Clever Geek Handbook

Detect if control form select button is selected in VBA

I have some code that works fine using ActiveX option buttons. However, I want the macro to work on Mac, so I am trying to replace the ActiveX controls with form controls. In ActiveX, all I had to do was to check if one of my two select buttons was selected:

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

I'm trying to find the equivalent for Google form controls, but every time I get:

Required object error

Thanks a lot for your replies @ L42 and @Sai Nishank! Now, what if I want to check OptionButton_Click if an option button from another group is true? I tried this syntax, but I get an error: "Compilation 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'm not sure if BTUButton is the correct button name, but I don't know where to check, form controls don't have that convenient "Right Click"> "Properties" like ActiveX

+4


source to share


2 answers


You should remove the value .Value

from all parameter buttons, since the parameter buttons do not contain the resulting value, but the parameter group control. If you omit .Value

then the default interface will report the state of the option button as you expect. You have to write all the relevant code in the commandbutton_click events because whenever the command button is clicked, the select button action is triggered.

If you want to run action code when an option button is clicked, don't write an if loop to do so.



EXAMPLE:

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

Sub OptionButton1_Click   
    (action code...)
End sub
+2


source


If you are using Form Control

, you can get the same property as ActiveX

using the property OLEFormat.Object

for Shape Object

. Better yet, assign it in a variable declared as OptionButton to get hit with Intellisense.

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 link to it Macro

or a subroutine that is executed when it is selected. Therefore, you just need to set up a subroutine that identifies which button is pressed and then take the appropriate action on 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 the above code, you only have one subroutine assigned to both select buttons.
Then you check what is called a subroutine by checking Application.Caller

.
Thus, there is no need to check if the option button value is true or false.

+8


source






More articles:


All Articles