VBA & Macro Problems

Loop Through Excel Form Control Option Buttons by Group Box with VBA

Phi Hung VoPhi Hung Vo Nov 13, 2026 869 views

Question details

The user needs to loop through only the Form Control option buttons associated with a particular Excel group box and disable that group after a selection.

Loop Through Excel Form Control Option Buttons by Group Box with VBA
Product
Microsoft Excel VBA and worksheet Form Controls
Device & OS
not provided
Scenario
A worksheet contains many option buttons organized into group boxes. After a user selects an option, the buttons in that group must be disabled so the selection cannot be changed.
Observed behavior
Excel does not expose a direct child-option-button collection for a Form Control group box. The existing code scans every option button on the worksheet, which becomes slow as the number of controls increases.
Before you start

Confirm that the worksheet uses Form Controls, not ActiveX controls, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Record the worksheet name and each option button's object name; these names are different from the visible button captions.

Solution 1Recommended

Cache group membership in a hidden Setup sheet and loop only through the selected group

Use an explicit group-to-button mapping when scanning all worksheet controls on every click is too slow.

A Form Control group box does not expose a collection containing just its option buttons. Instead, store each button's group membership once, create a named range for each group's list, and use Application.Caller to locate the clicked button.

The example below uses workbook-level named ranges whose names start with ob_. Initialization scans the mapping table, not the worksheet's control collection. Each subsequent click looks up the caller and processes only the buttons listed for that group.

1
Create the mapping table

Insert a worksheet named Setup. Enter GroupKey in A1 and ButtonName in B1. For every option button, add one row containing its group key and exact object name, such as Choices1 and Option Button 1. Give all buttons in the same group box the same key. Use simple keys containing letters, digits, and underscores, beginning with a letter, and list each button only once. Select a button and check the Name Box to the left of the formula bar for its object name.

2
Add the initialization and click-handler code

Press Alt+F11 to open the VBA editor, choose Insert > Module, and paste the following code. Replace Sheet1 with the name of the worksheet containing the controls. Option Explicit Private Const CONTROL_SHEET As String = "Sheet1" Public Sub InitializeOptionGroups() Dim ws As Worksheet Dim groups As Object Dim r As Long, lastRow As Long Dim key As Variant Dim list As Range Set ws = ThisWorkbook.Worksheets("Setup") Set groups = CreateObject("Scripting.Dictionary") lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row For r = 2 To lastRow key = Trim$(CStr(ws.Cells(r, "A").Value2)) If Len(key) > 0 And Len(ws.Cells(r, "B").Value2) > 0 Then If groups.Exists(key) Then Set list = Application.Union(groups.Item(key), ws.Cells(r, "B")) Set groups.Item(key) = list Else groups.Add key, ws.Cells(r, "B") End If End If Next r For Each key In groups.Keys Set list = groups.Item(key) On Error Resume Next ThisWorkbook.Names("ob_" & CStr(key)).Delete On Error GoTo 0 ThisWorkbook.Names.Add _ Name:="ob_" & CStr(key), _ RefersTo:="=" & list.Address(External:=True) Next key End Sub Public Sub LockSelectedOptionGroup() Dim caller As Variant Dim setup As Worksheet, controls As Worksheet Dim matchCell As Range, list As Range Dim area As Range, cell As Range Dim groupKey As String caller = Application.Caller If VarType(caller) <> vbString Then Exit Sub Set setup = ThisWorkbook.Worksheets("Setup") Set controls = ThisWorkbook.Worksheets(CONTROL_SHEET) Set matchCell = setup.Columns("B").Find( _ What:=CStr(caller), After:=setup.Range("B1"), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If matchCell Is Nothing Then Exit Sub groupKey = Trim$(CStr(setup.Cells(matchCell.Row, "A").Value2)) Set list = ThisWorkbook.Names("ob_" & groupKey).RefersToRange For Each area In list.Areas For Each cell In area.Cells controls.OptionButtons(CStr(cell.Value2)).Enabled = False Next cell Next area End Sub

3
Build the named ranges and assign the macro

In Excel, open Developer > Macros, select InitializeOptionGroups, and click Run. Then right-click each Form Control option button, choose Assign Macro, select LockSelectedOptionGroup, and click OK. Application.Caller identifies the clicked control when this assigned macro runs; do not test the click handler directly from the Macros dialog.

4
Test the group-specific behavior

Click an option button in one group. Confirm that the selection remains visible, every mapped button in that group becomes disabled, and buttons in other groups remain enabled. If a button name is not found, compare its Name Box value with the corresponding entry in Setup column B.

5
Hide and maintain the Setup sheet

Right-click the Setup sheet tab and choose Hide after testing. When you add, remove, rename, or regroup controls, use Home > Format > Hide & Unhide > Unhide Sheet to edit the mapping, then run InitializeOptionGroups again. You can also call InitializeOptionGroups from the ThisWorkbook module's Workbook_Open event to rebuild the named ranges when the workbook opens.

Cache group membership in a hidden Setup sheet and loop only through the selected group
Scope and compatibility: This example targets one control worksheet and uses Scripting.Dictionary, whose availability can vary by platform. It avoids enumerating all worksheet option buttons on each click, but the caller lookup still searches the mapping table. Disabling controls prevents normal UI changes; it is not a security boundary.
Free Microsoft Office alternative

Use WPS Spreadsheet to review Excel data and control-mapping tables

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility, making migration straightforward for everyday spreadsheet work. Use WPS Spreadsheet to review the supporting data and group-mapping tables; it does not add a group-box child collection to Excel, and this VBA control workflow requires compatibility testing in your WPS edition.

Open and edit Microsoft Excel .xlsx and .xls spreadsheets.Review group keys and option-button mapping tables in a familiar grid interface.Use a lightweight, easy-to-use spreadsheet application for everyday workbook tasks.Move routine spreadsheet work to WPS while retaining Excel for workflows that require its VBA Form Control behavior.
microsoft office alternative - wps office

Frequently Asked Questions

Can I loop directly through the option buttons inside an Excel Form Control group box?

No direct child-option-button collection is exposed for a Form Control group box. You can enumerate the worksheet's OptionButtons collection and determine membership, or maintain a mapping table so the click handler processes only the relevant controls.

How do I get the name of the option button that was clicked?

Right-click the Form Control option button, choose Assign Macro, and assign your click handler. Inside that handler, Application.Caller returns the clicked control's name. Running the handler directly from Developer > Macros does not provide that same control-name context.

Does disabling the group clear the selected option?

Setting each mapped option button's Enabled property to False disables interaction without resetting its Value. To allow another selection later, run a separate reset macro that sets Enabled to True for the buttons in that group's named range.

Do the named ranges contain the option buttons themselves?

No. Open Formulas > Name Manager to inspect them: each ob_ named range refers to Setup cells containing button names. The handler reads those names and accesses the corresponding controls through the worksheet's OptionButtons collection.

Will this VBA solution work unchanged in WPS Spreadsheet?

Do not assume it will. VBA availability and support for Excel Form Control objects vary by WPS edition and platform. WPS Spreadsheet can handle the supporting spreadsheet data, but test Application.Caller, assigned macros, named ranges, and OptionButtons behavior before moving this workflow out of Excel.