How to check if an Option Button is selected

Question

I want to be able to check in code whether or not a particular Option Button is selected.

I made a couple of Form Control buttons in an Excel sheet and I tried something like:

if Activesheet.myButton = true then

     (do stuff)

endif

But I get an "Object doesn't support this property or method" error.

Would it be better to use an ActiveX Option Button?

Answer 1

By default, VBA hides easy access to the types needed to work with Form Controls placed on a Worksheet.

In the VBA editor, either press the 'F2' function key or from the View Menu->Object Browser. This will open the Object Browser window. If search the Excel library for 'OptionButton', you will not find anything of use for the given task.

However, if you right-click in the 'Library Window' and click on 'Show Hidden Members', you will be rewarded with a complete list of Classes that you can use. Once you have enabled 'Hidden Members', VBA's Intellisense will also show these classes to you.

The Form Controls are added to the Worksheet as a Shape, but the Shape object itself does not have any property that allows you see if the Option Button is checked or not. You can access the OptionButton through the 'OLEFormat.Object' property, but at this point it is just an 'Object' type. However you can cast the Object to an Excel.OptionButton as shown in the following code. You will notice that I preface 'OptionButton' with the 'Excel' namespace (Library) name. By doing this, Intellisense will kick-in as soon as you type the '.' and provide you with a list of classes to choose from. The OptionButton class exposes a 'Value' property that allows you to check if it is checked (Value = 1) or unchecked (Value = 0).

Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Worksheets.Item("Sheet2")

Dim btn As Shape
Set btn = ws.Shapes.Item("Option Button 1")
Dim optBtn1 As Excel.OptionButton
Set optBtn1 = Sheet2.Shapes.Item("Option Button 1").OLEFormat.Object

If optBtn1.Value = 1 Then ' it is checked
   Debug.Print "Option Button 1 is checked"
End If

Now this is all works, but now that you have acces to Hidden Members, you also have access to the OptionButtons collection that exists on the WorkSheet object and you can skip accessing it as a Shape Object.

Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Worksheets.Item("Sheet2")

Dim optBtn2 As Excel.OptionButton
Set optBtn2 = ws.OptionButtons.Item("Option Button 2")
If optBtn2.Value = 1 Then ' it is checked
   Debug.Print "Option Button 2 is checked"
End If