Loop through OptionButtons

  • Thread starter Thread starter PPL
  • Start date Start date
P

PPL

Excel 2003:

Worksheet contains a series of Option Buttons named
OptionButton1A to OptionButton1E

I'm trying to cycle through the sets of buttons to establish their status
with the following

Sub CheckStatus()
Dim X As Integer
For X = 65 To 69 '(i.e. when used with Chr will yield character A to E which
will be appended to the OptionButton1 name in the next command)
If OptionButton1 & Chr(X).Value = True Then '<--- Problem here gives Run
time error 424: Object Required
MsgBox "Do Stuff"
End If
Next X
End Sub

I'm doing something quite wrong here but no clue as to what
Can anyone help please?

TIA

Phil
 
First, there are two types of optionbuttons you could have used. One type is
from the Forms Toolbar and the other type is from the Control toolbox toolbar.

I'm guessing that you used the optionbuttons from the control toolbox toolbar:

Option Explicit
Sub CheckStatus()
Dim lCtr As Long

With Worksheets("Sheet1")
For lCtr = Asc("A") To Asc("E")
If .OLEObjects("Optionbutton1" & Chr(lCtr)).Object.Value = True Then
MsgBox "optionbutton1" & Chr(lCtr) & " is checked"
End If
Next lCtr
End With
End Sub

=====
If you used the optionbuttons from the Forms toolbar:

Option Explicit
Sub CheckStatus()
Dim lCtr As Long

With Worksheets("Sheet1")
For lCtr = Asc("A") To Asc("E")
If .OptionButtons("Optionbutton1" & Chr(lCtr)).Value = xlOn Then
MsgBox "optionbutton1" & Chr(lCtr) & " is checked"
End If
Next lCtr
End With
End Sub
 
Thanks Dave. You were right in your guess. It was the Control Toolbox Option
Button that I used.
Your code works perfectly thank you. I guess the it was the OLE bit that I
was missing
I'm not sure why you've used the Asc versus Chr solution, but you can't
argue with success

Thanks again

Phil
 
I find that this:
For lCtr = Asc("A") To Asc("E")
is much easier to understand than:
For X = 65 To 69

But we both used chr() to convert those numbers to the associated letter.

(I changed the variable name to lCtr (LetterCounter), too. I don't really like
one letter variable names.)
 
Back
Top