How to iterate through controls in VBA/Excel?

February 22, 2007 4:34 PM

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...
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?

posted by apark to computers & internet (4 answers total)

The controls collection is available in VBA (at least, it was when I just tested it in Excel 2003). I'm assuming that your code is a paraphrase, not a copy-and-paste, but I notice that you don't have quotes around "optionbutton." In addition, you've hardcoded a 1 rather than referencing the variable i:

For i = 1 to 10
Controls("optionbutton" & i).Value= True
Next i

posted by Doofus Magoo at 5:36 PM on February 22, 2007

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:

Me.Controls("optionbutton" & i).Value = True

or

UserForm1.Controls("optionbutton" & i).Value = True

(or whatever your form name is)
posted by Doofus Magoo at 5:41 PM on February 22, 2007

You can also loop through the OLEObjects.
posted by unixrat at 6:05 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