VBA & Macro Problems

How to Dynamically Create Separate VBA Option Button Groups in Excel

Huda Qurayshi Oct 23, 2026 868 views

Question details

The user needs to dynamically generate new pairs of option buttons using VBA and assign them to unique, separate group names on each button click.

How to Dynamically Create Separate VBA Option Button Groups in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating interactive forms dynamically where multiple newly generated sets of option buttons require independent user selections.
Observed behavior: Dynamically created option buttons either default to the same group or require manual configuration. The goal is to automate unique group assignments using a persistent VBA counter across macro executions.

Before you start

Ensure that the Developer tab is enabled in your ribbon and that macros are allowed to run in your workbook security settings.

Solution 1Recommended

Use GetSetting and SaveSetting to Group Buttons Dynamically

Use VBA's built-in registry setting functions to maintain a persistent counter across macro executions to dynamically generate unique group names.

When generating option buttons dynamically, assigning a unique GroupName prevents independent pairs from acting as a single large group. The GetSetting and SaveSetting functions allow your macro to remember the last used group number between clicks without relying on worksheet cells.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your workbook.

Insert a New Module

Click on 'Insert' in the top menu bar and select 'Module' to create a new script area for your macro.

Write the Counter Logic

Declare your variables. Use `i = GetSetting("counter", "Options", "count")` to retrieve the previous count. Check if `i > 0`; if true, increment it by 1 (`i = i + 1`), otherwise set it to 1.

Save the New Counter

Store the new counter value for the next time the macro runs by adding the line: `SaveSetting "counter", "options", "count", i`.

Apply the Unique Group Name

Define the group name variable using `strGroup = "group" & i`. After inserting your new option button controls (e.g., s1 and s2), assign the group using `s1.OLEFormat.Object.GroupName = strGroup` and `s2.OLEFormat.Object.GroupName = strGroup`.

Use GetSetting and SaveSetting to Group Buttons Dynamically

Resetting the Counter: If you need to restart the testing or dynamic generation from group 1, you can use the DeleteSetting statement in VBA to clear the stored registry value.

Advanced VBA Support

Write and Execute VBA Macros Seamlessly in WPS Office

WPS Office provides excellent built-in support for VBA macros. You can effortlessly run complex scripts, design dynamic forms, and manage option buttons within WPS Spreadsheet.

1. Download and Install WPS Office: Get the latest version of WPS Office from the official website and open the WPS Spreadsheet application.
2. Enable Developer Tools: Navigate to the 'Options' menu, select 'Customize Ribbon', and check the 'Developer' tab to access VBA tools.
3. Run Your VBA Code: Click the 'Visual Basic' icon in the Developer tab, paste your dynamic option button script, and run it just as you would in Microsoft Excel.

Full compatibility with Microsoft Excel VBA scriptsLightweight and fast execution for heavy macro workbooksAdvanced developer tools included at no extra costSupports interactive form controls like Option Buttons and Checkboxes

microsoft office alternative - wps office

Frequently Asked Questions

Why do my dynamically created option buttons clear each other?

By default, option buttons placed in the same container belong to the same group, meaning only one can be selected at a time. You must explicitly assign different GroupName properties to each pair to allow independent selections.

Where are the values from SaveSetting stored in my computer?

The SaveSetting function stores data securely in the Windows Registry under 'HKEY_CURRENT_USER\Software\VB and VBA Program Settings'. It is a built-in method to store minor configuration data for macros.

Can I store the VBA counter in a worksheet cell instead of using SaveSetting?

Yes. You can read and write the counter value to a hidden worksheet cell (e.g., `Sheets("Hidden").Range("A1").Value`). While this avoids the registry, SaveSetting is often cleaner if you want to keep the worksheet strictly for user data.

Excel & Data Problems