Objects and Variables
 

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)

Objects and Variables
 

Geoff,

Perhaps this may help:
Controls("TextBox1").Value = "hello"

Rob

"Geoff H" wrote in message
om...
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)

Objects and Variables
 

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)

"Geoff H" wrote in message
om...
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)

Objects and Variables
 

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","txtLe ft")

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

--
Regards,
Tom Ogilvy


Geoff H wrote in message
om...
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)

Objects and Variables
 

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


"Geoff H" wrote in message
om...
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)