Objects and Variables

  • Thread starter Thread starter Geoff H
  • Start date Start date
G

Geoff H

Hi
Can anyone help me with a little problem. I wish to access a number of
objects (eg textboxes or check boxes) using a "for Next "loop with a
variable instead of how it is now

cbPart1.Value = ""
txtDesc1.Value = ""
txtLoc1.Value = ""
txtUsed1.Value = ""
txtLeft1.Value = ""

cbPart2.Value = ""
txtDesc2.Value = ""
txtLoc2.Value = ""
txtUsed2.Value = ""
txtLeft2.Value = ""

cbPart3.Value = ""
txtDesc3.Value = ""
txtLoc3.Value = ""
txtUsed3.Value = ""
txtLeft3.Value = ""

I have tried using the following and minor variations

For iCount = 1 to 10
oTemp1 = "cbPart" & (iCount)
oTemp2 = "txtDesc" & (iCount)
oTemp3 = "txtLoc" & (iCount)
oTemp4 = "txtUsed" & (iCount)
oTemp5 = "txtLeft1" & (iCount)

oTemp1.Value = ""
oTemp2.Value = ""
oTemp3.Value = ""
oTemp4.Value = ""
oTemp5.Value = ""

next

May not be the best illustration (I have one instance where I need to
test the status of 83 check boxes)but what I am after is the method or
ability to convert variables to object references as VBA doesnt seem
to support Control Arrays.

Am I flogging a dead horse?

Any help would be well helpfull (Holy Grail)
 
Geoff,

Access these controls through the controls collection. For example

For iCount = 1 to 10
oTemp1 = Controls("cbPart" & (iCount))
oTemp2 = Controls("txtDesc" & (iCount))
oTemp3 = Controls("txtLoc" & (iCount))
oTemp4 = Controls("txtUsed" & (iCount))
oTemp5 = Controls("txtLeft1" & (iCount))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Answers provided so far assume these are on a Userform. If they are on a
worksheet and from the control toolbox toolbar, you can use a similar
approach

Dim OleObj as OleObject
varr = Array("cbPart","txtDesc","txtLoc","txtUsed","txtLeft")

for i = 1 to 83
for j = lbound(varr) to ubound(varr)
set OleObj = ActiveSheet.OleObjects(varr(j) & i)
if typeof OleObj.Object is MSForms.Textbox then
OleObj.Object.Value = ""
elseif typeof OleObj.Object is MsForms.Checkbox then
OleObj.Object.Value = False
end if
next j
next i
 
Geoff,

You want something like

For nCounter = 0 to 10
msgbox frmMyForm.Controls("txt" & cstr(nCounter)).Text
msgbox frmMyForm.Controls("chk" & cstr(nCounter)).Value
Next nCounter

where your text boxes are obviously txt1, txt2, checkboxes chk1, chk2 etc

Robin Hammond
www.enhanceddatasystems.com
 
Back
Top