Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
displaying objects | Excel Discussion (Misc queries) | |||
Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
OLE Objects | Excel Discussion (Misc queries) | |||
VBA Objects | Excel Discussion (Misc queries) | |||
Changing objects on other Worksheets with Variables | Excel Programming |