How to iterate through controls in VBA/Excel?
February 22, 2007 4:34 PM Subscribe
How do I loop through a bunch of controls using VBA in Excel 2003?
I have a sheet w/ a bunch of option buttons on it. I would like to be able to use a loop and set their values.
So,
I have a bunch of OBs named OptionButton1, OptionButton2, etc...
I want to do something like...
Unfortunately the collection "CONTROLS" seems to be available only in VB and not in VBA... Can someone help?
I have a sheet w/ a bunch of option buttons on it. I would like to be able to use a loop and set their values.
So,
I have a bunch of OBs named OptionButton1, OptionButton2, etc...
I want to do something like...
For i = 1 to 10
controls(OptionButton & 1).value = True
Next i
Unfortunately the collection "CONTROLS" seems to be available only in VB and not in VBA... Can someone help?
Also, depending on where you have the code (behind the form or in a separate module) you may need to qualify the use of the Controls collection, e.g:
posted by Doofus Magoo at 5:41 PM on February 22, 2007
Me.Controls("optionbutton" & i).Value = Trueor
UserForm1.Controls("optionbutton" & i).Value = True(or whatever your form name is)
posted by Doofus Magoo at 5:41 PM on February 22, 2007
It also depends on whether they're option buttons added from the Forms toolbar or from the Control Toolbox.
Controls from both are considered shapes, whereas only controls from the Control Toolbox are OLEObjects.
Add one from each and try the below:
Sub tt()
Dim sh As Worksheet
Dim s As Shape
Dim o As OLEObject
Set sh = ActiveSheet
For Each s In sh.Shapes
MsgBox s.Name
Next s
For Each o In sh.OLEObjects
MsgBox o.Name
Next o
Set sh = Nothing
End Sub
And also check out ozgrid or perhaps j-walk for some good info.
posted by NailsTheCat at 10:04 PM on February 22, 2007
Controls from both are considered shapes, whereas only controls from the Control Toolbox are OLEObjects.
Add one from each and try the below:
Sub tt()
Dim sh As Worksheet
Dim s As Shape
Dim o As OLEObject
Set sh = ActiveSheet
For Each s In sh.Shapes
MsgBox s.Name
Next s
For Each o In sh.OLEObjects
MsgBox o.Name
Next o
Set sh = Nothing
End Sub
And also check out ozgrid or perhaps j-walk for some good info.
posted by NailsTheCat at 10:04 PM on February 22, 2007
This thread is closed to new comments.
posted by Doofus Magoo at 5:36 PM on February 22, 2007