[Excel VBA] How to Manage Option Buttons with Group Boxes and Determine Selected Items

Introduction

When using Option Buttons (Radio Buttons) on an Excel sheet for multiple questions—such as “Q1. Select a Plan” and “Q2. Select Payment Method”—you encounter a common problem: by default, all option buttons on a sheet belong to the same group. Selecting an answer for Q2 might deselect the answer for Q1.

The solution is the “Group Box” (Form Control). By placing Option Buttons inside a Group Box, you create an independent group. This allows users to make one selection per box.

In this article, I will explain how to manage Option Buttons using Group Boxes and how to use VBA to determine which item was selected in each specific group.

Preparation Before Running the Code

1. Insert Group Boxes: From the Developer tab > Insert > Form Controls, add two Group Boxes to your sheet.
2. Rename Group Boxes: Select the first box and change its name to “PlanGroupBox” in the Name Box (the box to the left of the formula bar). Name the second one “PaymentGroupBox”.
3. Add Option Buttons: Place multiple Option Buttons inside each Group Box (e.g., “Plan A”, “Plan B” in the first; “Credit Card”, “Bank Transfer” in the second).

VBA Sample Code

This macro checks the two specific Group Boxes (PlanGroupBox and PaymentGroupBox) and retrieves the caption of the selected button within each.

' Retrieve the selected Option Button values from specific Group Boxes
Sub GetGroupedOptionValues()

    '== Define Variables ==
    Dim grpBox As GroupBox
    Dim optButton As OptionButton
    Dim selectedPlan As String, selectedPayment As String
    
    ' Set default values to "Not Selected"
    selectedPlan = "Not Selected"
    selectedPayment = "Not Selected"
    
    '== 1. Check options in the Plan Selection Group (PlanGroupBox) ==
    ' Get the specific Group Box by its name
    Set grpBox = ActiveSheet.GroupBoxes("PlanGroupBox")
    
    ' Loop through ONLY the Option Buttons inside this Group Box
    For Each optButton In grpBox.OptionButtons
        If optButton.Value = xlOn Then
            selectedPlan = optButton.Caption
            Exit For ' Exit loop once the selected button is found
        End If
    Next optButton
    
    '== 2. Check options in the Payment Method Group (PaymentGroupBox) ==
    Set grpBox = ActiveSheet.GroupBoxes("PaymentGroupBox")
    
    ' Loop through Option Buttons in this specific box
    For Each optButton In grpBox.OptionButtons
        If optButton.Value = xlOn Then
            selectedPayment = optButton.Caption
            Exit For
        End If
    Next optButton
    
    '== Display Results ==
    MsgBox "Plan: " & selectedPlan & vbCrLf & _
           "Payment: " & selectedPayment, vbInformation, "Selection Confirmation"

End Sub

Explanation of the Code

1. Grouping Logic

Instead of placing Option Buttons directly on the sheet, placing them inside a Group Box allows VBA to treat them as a subset of controls.

2. The .OptionButtons Collection

Set grpBox = ActiveSheet.GroupBoxes("PlanGroupBox")
For Each optButton In grpBox.OptionButtons

This is the core of the solution.

• ActiveSheet.GroupBoxes("Name"): Retrieves the specific Group Box object.
• .OptionButtons: This property returns a collection of only the Option Buttons contained within that specific Group Box. This isolates the logic so you don’t have to loop through every button on the sheet.

3. Checking the State

If optButton.Value = xlOn Then ...

Inside the loop, we check the .Value of each button.

• xlOn: Indicates the button is selected (checked).
• xlOff: Indicates the button is not selected.

Once xlOn is found, we store the .Caption (the text label) and use Exit For to stop the loop, improving efficiency.

Summary

In this article, I explained how to group Option Buttons using Group Box controls and retrieve the selected values using VBA.

• To create multiple independent questions, enclose Option Buttons in Group Boxes.
• Use ActiveSheet.GroupBoxes("Name") to target a specific group.
• Use the GroupBox.OptionButtons collection to loop through and check only the relevant buttons.

By using this technique, you can create structured, easy-to-use forms like questionnaires or settings panels directly on your Excel sheets.