Detect if a Form Control option button is selected in VBA

Question

I have a code that work just fine using ActiveX option buttons. However, I want the macro to run on a Mac as well so I am trying to replace my ActiveX controls with form controls. With ActiveX, all I had to do in order to check if one of my two option buttons was selected is:

    Sub OptionButton1_Click
    If OptionButton1.Value = true then
        (action...)
    End if
    End sub

I have been trying to find an equivalent for Form Controls on Google but each time I get an:

Object required error

Thank you very much for your answers @L42 and @Sai Nishank! Now what if I want to check in an OptionButton_Click if an option button from an other group is true ? I tried this syntax but I get an error message : "Compile error Method or Data not found"

    Sub USDButton_Click()
    MsgBox "USD"
    If Sheet1.BTUButton = True Then
    (action1)
    End If
     If Sheet1.kWhButton = True Then
    (action2)
     End If

I am not sure if BTUButton is the correct name of the button, but I don't where to check, form controls don't have that handy "Right Click > Properties" like ActiveX

Infralapsarian

Wow I should have seen that. Sorry...Now how do I refer do this button (I let's say I named it "My Option Button 1" ? This doesn't work: 'If Sheet1."My Option Button 1"= True Then (action1) End If' – Infralapsarian

can you tell me whether ur creating everything in a userform or ur using form controls in excel sheet – Abstraction

I am using form controls in excel sheet. Both your answers and L42's helped me figure it out, thanks ! – Infralapsarian

Answer 1

You should remove .Value from all option buttons because option buttons don't hold the resultant value, the option group control does. If you omit .Value then the default interface will report the option button status, as you are expecting. You should write all relevant code under commandbutton_click events because whenever the commandbutton is clicked the option button action will run.

If you want to run action code when the optionbutton is clicked then don't write an if loop for that.

EXAMPLE:

Sub CommandButton1_Click
    If OptionButton1 = true then
        (action code...)
    End if
End sub

Sub OptionButton1_Click   
    (action code...)
End sub

Abstraction

Answer 2

If you are using a Form Control, you can get the same property as ActiveX by using OLEFormat.Object property of the Shape Object. Better yet assign it in a variable declared as OptionButton to get the Intellisense kick in.

Dim opt As OptionButton

With Sheets("Sheet1") ' Try to be always explicit
    Set opt = .Shapes("Option Button 1").OLEFormat.Object ' Form Control
    Debug.Pring opt.Value ' returns 1 (true) or -4146 (false)
End With

But then again, you really don't need to know the value.
If you use Form Control, you associate a Macro or sub routine with it which is executed when it is selected. So you just need to set up a sub routine that identifies which button is clicked and then execute a corresponding action for it.

For example you have 2 Form Control Option Buttons.

Sub CheckOptions()
    Select Case Application.Caller
    Case "Option Button 1"
    ' Action for option button 1
    Case "Option Button 2"
    ' Action for option button 2
    End Select
End Sub

In above code, you have only one sub routine assigned to both option buttons.
Then you test which called the sub routine by checking Application.Caller.
This way, no need to check whether the option button value is true or false.

Neologize

Thank you very much for your answers @Neologize and @Sai Nishank! Now what if I want to check in an OptionButton_Click if an option button from an other group is true ? I tried this syntax but I get an error message : Sub USDButton_Click() MsgBox "USD" If Sheet1.BTUButton = True Then (action1) End If If Sheet1.kWhButton = True Then (action2) End If – Infralapsarian

What kind of error message you are getting, when you click the command button. – Abstraction

"Compile error Method or Data not found", I actually edited my original question with more details if you want to take a look. Thanks @Sai Nishank!! – Infralapsarian

Using If ActiveSheet.Shapes("Option4").OLEFormat.Object.Value = 1 Then 'Range("B39").Value = "what I want to set this cell to" End If will work also in pre-2010 Excel version 2007 VBA macro scripts - older versions do not support the shortcut Option4.Value = True that is mentioned everywhere. Took me hours to find this post, thanks @L42. – Clite

In case useful for anyone: in the first block of code, Option Button 1 is not the text on the Option Button in the sheet. To see what needs to be inputted there I had to right-click the Option Button, and then on the top left of the UI the name of the button will appear (you can also edit it there - mine reads OptionButton131). – Griz