Loop to condense code

[Chuck Moran]

Good morning,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Currently using: Excel 2007
<o> </o>
I use a spreadsheet with forms for selecting specific information. There are multiple frames and many OptionButtons on each the frames.
<o> </o>
The OptionButton.Captions are set by the following code and it works OK:
<o> </o>
<o> </o>
Sub SetupWireInspectorSheet()
If Range("LISTS!a2") = "" Then
UserForm2.OptionButton131.Visible = False
Else: UserForm2.OptionButton131.Caption = Range("LISTS!a2")
End If
If Range("LISTS!a3") = "" Then
UserForm2.OptionButton132.Visible = False
Else: UserForm2.OptionButton132.Caption = Range("LISTS!a3")
End If
…
Exit Sub
<o> </o>
There are over 100 OptionButtons and the code is very lengthy. I’d like to shorten the code.
<o> </o>
Can someone tell me how to create a LOOP that will:
1 – increment the # of the OptionButton portion of the above code
2 – increment the # of the Range portion of the above code – is it different since it is inside quotes
<o> </o>
Thank you,
Chuck
</SPAN></SPAN>

 

[kpark91]

Well. I haven't tried it but
something like

For i = 2 To 3
    If Range("LISTS!a" & i).Value = "" Then
        .....
Next i

should work as well as optionbuttons hopefully.

 

[Chuck Moran]

Thanks, I'll try it and let you know.

 

[Chuck Moran]

kpark91,

Your code worked well for the "Caption" portion.

I can't see how to make it work for the "Optionbutton" portion. I don't know how to separate the number from the UserForm2.OptionButtonxxx. I do realize I'll have to create another variable to increment the number.

Thanks again,
Chuck

 

[Chuck Moran]

Hi,
I'm having difficulty (as in I can't make it work after reading many other posts) in separating the number (132) portion of the Optionbutton from the line:

UserForm2.OptionButton132.Caption = Range("LISTS!a3")

Since I have many OptionButtons, my intent is to create a loop increment the number portion of the line to make all the changes.

An help is greatly appeciated.

Thank you!