Detect Option Group Value in VBA

[Steve]

I have a set of three option buttons (from the control toolbox) called
"MyGroup" that I would like to detect which of the three is true in VBA and I
cannot figure out how to refer to the group in VBA. Is there a way to detect
the value of the group or do I have to cycle through each option button
individually using if statements or select case statements?

Thanks for your help.

Steve

 

[Harald Staff]

Hi Steve

The optionbuttons, not the group, contains the value. So loop, like:

If optionbutton1.value = True then
'xxx
elseif optionbutton2.value = true then
'yyy
elseif 'and so on


I think it's better is to assign the result at the time a button is clicked,
like:

Dim GroupValue1 As Long

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then GroupValue1 = 1
End Sub

Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then GroupValue1 = 2
End Sub

HTH. Best wishes Harald

 

[Chip Pearson]

Try a function like the following:

Function SelectedButtonFromGroup(WS As Worksheet, _
GroupName As String) As MSForms.OptionButton

Dim OleObj As OLEObject
Dim OPT As MSForms.OptionButton

For Each OleObj In WS.OLEObjects
If TypeOf OleObj.Object Is MSForms.OptionButton Then
Set OPT = OleObj.Object
If StrComp(OPT.GroupName, GroupName, vbTextCompare) = 0 Then
If OPT.Value <> 0 Then
Set SelectedButtonFromGroup = OPT
Exit Function
End If
End If
End If
Next OleObj
End Function


You pass in the Worksheet and GroupName and it will return the
OptionButton object that is selected in the specified group. You can
then call this function from your code with something like

Dim SelOpt As MSForms.OptionButton
Set SelOpt = SelectedButtonFromGroup( _
Worksheets("Sheet1"), "Group1")
If SelOpt Is Nothing Then
Debug.Print "none checked or GroupName is invalid."
Else
Debug.Print "Opt Button '" & SelOpt.Caption & "' is checked"
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)