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

User · Accepted Answer

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

User · Answer

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

[vba] Detect if a Form Control option button is selected in VBA

Examples related to vba

Examples related to excel

Examples related to form-control

Examples related to radio-button