[Excel VBA] How to Identify the Selected Option Button on a Worksheet

Introduction

When you want users to select only one item from multiple choices on an Excel sheet, “Option Buttons” (Form Controls) are the ideal tool. They are perfect for questionnaires or settings sheets where choices must be mutually exclusive.

Using VBA, you can easily determine which option button the user has selected from a group placed on the sheet. This article explains a basic and reliable method to check all option buttons on a sheet and retrieve the caption (display text) of the selected item.

VBA Sample Code to Identify the Selection

This macro loops through all option buttons placed on the active sheet and displays the name (caption) of the button that is currently selected in a message box.

' Get the selected item from Option Buttons on the sheet
Sub GetSelectedOptionButton()

    '== Define variables ==
    Dim optButton As OptionButton
    Dim selectedChoiceCaption As String
    
    ' Set default value to "No Selection"
    selectedChoiceCaption = "No Selection"
    
    '== Loop through all Option Buttons on the sheet using the OptionButtons collection ==
    For Each optButton In ActiveSheet.OptionButtons
        ' Check if .Value property is On (xlOn)
        If optButton.Value = xlOn Then
            ' Get the caption of the button that is On
            selectedChoiceCaption = optButton.Caption
            ' Exit loop since the selected item is found
            Exit For
        End If
    Next optButton
    
    '== Display result in a message box ==
    MsgBox "Selected Item: " & selectedChoiceCaption, vbInformation, "Selection Result"

End Sub

Explanation of the Code

For Each optButton In ActiveSheet.OptionButtons

This line is the basic structure for checking all option buttons on the sheet.

• ActiveSheet.OptionButtons: This is a collection containing all Form Control option buttons placed on the currently active sheet.
• The code uses a For Each loop to extract each option button object into the optButton variable one by one for processing.

If optButton.Value = xlOn Then

The state of an option button is determined by the .Value property.

• xlOn (Value: 1): Indicates the button is selected (On).
• xlOff (Value: -4146): Indicates the button is not selected (Off).

The If statement checks for the button where this value is xlOn.

selectedChoiceCaption = optButton.Caption

Once the button that is “On” is found, the code retrieves its .Caption property (the text displayed next to the button) and stores it in a variable.

Exit For

Since only one option button can be selected within a group, there is no need to continue the loop once the selected button is found. Using Exit For exits the loop immediately, making the process more efficient.

Summary

In this article, we explained how to determine the selection state of option buttons on a sheet using VBA.

• Option buttons on a sheet can be handled collectively using the ActiveSheet.OptionButtons collection.
• The button’s state is determined by the .Value property (xlOn / xlOff).
• The display text of the button can be retrieved using the .Caption property.

By using this method, you can make your sheets more interactive, such as performing calculations or changing displayed content based on the user’s selection.