In an Excel VBA UserForm, put related OptionButton controls inside the same Frame to make them a mutually exclusive group: selecting one turns the others off. You can also use each button’s GroupName property to define a named group; in VBA, check the selected button’s Value property to find the user’s choice.
What an option-button group means
An option button, also called a radio button, is intended for a choice where the user selects one answer from a set, such as a delivery method or report format. In a group, only one button can be selected at a time. The selection is represented by the button’s Value: the selected OptionButton has a value of True, and the other buttons in that group have a value of False.
A group is not just a visual arrangement. Two buttons can appear next to each other but behave as separate choices if they belong to different containers or groups. Conversely, if unrelated choices are in the same group, choosing one will clear the other. Decide which choices belong together before arranging the form.
For example, a form might ask for a contact method (Email, Phone, or Mail) and a preferred time (Morning or Afternoon). Those are two separate questions, so they need two separate option-button groups. The user should be able to select Email and Morning at the same time.
Create a group with a Frame
A Frame is usually the clearest way to build a UserForm group. It provides a visible border and caption, and OptionButtons placed inside it are grouped by their shared container. The following steps are for desktop Excel for Microsoft 365 on Windows:
- Open the Visual Basic Editor with Alt+F11, then open the UserForm in its designer. If you need a form, use Insert > UserForm.
- In the Toolbox, add a Frame and set its Caption property to a prompt such as “Contact method.”
- With the Frame selected, add OptionButton controls inside it. Set each button’s Caption to a choice, for example “Email,” “Phone,” and “Mail.”
- Set each control’s (Name) property to a clear code-friendly name, such as optEmail, optPhone, and optMail. (Name) is different from Caption: code uses the name, while the user sees the caption.
- Add another Frame for a separate question, then add that question’s OptionButtons inside the new Frame.
Creating the buttons inside the Frame matters. If a button is merely positioned over a Frame but is actually a child of the UserForm, it may not participate in the Frame’s group. The reliable approach is to select the Frame before adding the controls, or to cut and paste a control into the Frame if the designer supports the move as intended, then verify its behavior by testing the form.
Use GroupName for named groups
OptionButton controls have a GroupName property. Assign the same nonblank GroupName to buttons that should act as one group, and a different name to a separate group. This can be useful when you want logical groups without relying only on visible Frames. For a form that will be maintained by other people, Frames are often easier to understand because the group is apparent in the design; GroupName is a useful alternative when the layout does not call for a visible border.
If GroupName is left blank, the controls are grouped according to their container. In practice, this means that OptionButtons in the same Frame form a group, while buttons in different Frames can represent independent questions. Avoid mixing an unclear layout with inconsistent GroupName values: use one deliberate grouping approach, give controls descriptive names, and test that selecting a button clears only the intended alternatives.
Do not confuse GroupName with the control’s (Name) or Caption. The (Name) property identifies a particular control to VBA; Caption is the text displayed to the user; GroupName identifies the mutually exclusive set. Each serves a different purpose.
Read the selected choice in VBA
Once the controls are named, code can inspect their Value properties. In this example, the UserForm has a Frame containing optEmail, optPhone, and optMail, plus a command button named cmdOK. Add the event procedures to the UserForm’s code module. The first procedure selects a default when the form opens; the second responds when the user clicks OK.
Private Sub UserForm_Initialize()
' Set a sensible default for the contact method.
Me.optEmail.Value = True
End Sub
Private Sub cmdOK_Click()
Dim contactMethod As String
If Me.optEmail.Value Then
contactMethod = "Email"
ElseIf Me.optPhone.Value Then
contactMethod = "Phone"
ElseIf Me.optMail.Value Then
contactMethod = "Mail"
Else
MsgBox "Choose a contact method before continuing."
Exit Sub
End If
MsgBox "Selected: " & contactMethod
End Sub
The example handles the possibility that no button is selected, even though it sets Email as the initial choice. That check is useful if other code later clears the selection or if the form is changed. Replace the message box with the action your form needs, such as assigning the choice to a worksheet cell or passing it to another procedure.
To save the selection for later code, assign it to a form-level variable or expose a property on the form; avoid reading a control after the form has been unloaded. Another simple pattern is to write the selected text to a cell before hiding or unloading the form. The right pattern depends on how the form is opened and how the workbook uses the result.
Set defaults and handle required choices
A UserForm can open with no OptionButton selected unless the form or code sets a default. A default is helpful when one choice is common and safe to assume, but it can also lead users to accept an unintended answer. If the choice is important, leave the group unselected and validate it when the user submits.
For a required selection, check each relevant Value before continuing, as in the example’s Else branch. For multiple independent questions, validate each group separately. For instance, check whether one of optEmail, optPhone, or optMail is selected, and then separately check whether optMorning or optAfternoon is selected. A check for one group does not establish that a choice has been made in another.
Set defaults in UserForm_Initialize when they should be applied each time a new form instance is initialized. If the form is hidden and shown again rather than unloaded and recreated, its existing selections may remain. Decide whether reopening the form should preserve the previous answers or reset them, and place reset logic accordingly.
Common problems and how to fix them
- Selecting one button clears an unrelated choice: Check whether the buttons share a Frame or the same GroupName. Separate independent questions into different Frames or assign distinct group names.
- Buttons that look grouped behave independently: They may not actually be inside the same Frame. Add them from the Toolbox while the Frame is selected, then test the form.
- VBA reports that a control is not found: Check the control’s (Name) property, not its displayed Caption. Also confirm that the code is in the correct UserForm module and that the spelling matches exactly.
- The wrong answer is saved: Verify that the code checks the right controls and that the button captions still correspond to the values being stored. If choices can be rearranged or relabeled, keeping a clear mapping between names and meanings helps prevent errors.
- The form submits without a choice: Do not assume a group must always have a selection. Add explicit validation or set a deliberate default, depending on the task.
Testing should include more than the first choice. Click each option in turn and confirm that it clears the other choices in its own group, while leaving selections in other groups alone. Then test submission with no selection if that state is possible, and test the form after any code that initializes, resets, hides, or unloads it.
Option buttons compared with checkboxes
Use OptionButtons when the user should choose one item from a set. Use CheckBoxes when choices are independent and the user may select none, one, or several. For example, “Email, Phone, or Mail” is usually an option-button question; “Include charts” and “Include notes” are often separate checkboxes because both can be selected.
A common misconception is that a group of OptionButtons automatically means one answer is mandatory. Mutual exclusion means the user cannot select two options in that group at once; it does not replace validation. If a choice is required, enforce that requirement in the submit procedure. If users need to select several answers, use checkboxes rather than trying to make option buttons behave like them.
Platform and macro considerations
This guidance targets VBA UserForms in desktop Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros or display a VBA UserForm as an interactive form, so users need desktop Excel to use this interface. Excel for Mac has a VBA environment, but the editor and control support can differ from Windows; test the workbook on the actual Mac environment if Mac use is required.
Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA, and follow your organization’s macro-security policy. Before running or distributing code that changes workbook data, save a separate copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the practical way to recover from an unwanted change.
Frequently Asked Questions
How do I make two groups of option buttons on one UserForm?
Put each set in its own Frame, or give the controls in each set a distinct GroupName. Separate Frames are generally the easiest arrangement to see and maintain.
Can an option-button group start with nothing selected?
Yes. Do not set any button’s Value to True during initialization. Check the group when the user submits if a selection is required.
How do I find which OptionButton the user selected?
Check the Value property of each button in the group. The selected one is True. You can use If and ElseIf statements to map that control to a text value or other action.
Why are my option buttons not mutually exclusive?
They may be in different containers, or their GroupName values may not match as intended. Confirm the actual container and group settings, then test by selecting every button.
Can users run a VBA UserForm in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for a VBA UserForm, or consider a web-compatible alternative if users must work in a browser.
Related
- Data Validation Multiple Selection in Excel: What Works
- Data Validation Interview Questions and Strong Answers
- Excel Data Validation Guidelines for Reliable Spreadsheets
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA If Then Examples for ExcelVBA’s If...Then statement runs code when a condition is true, and can choose an alternative with Else or…
- Excel VBA Dictionary Keys: Add, Find, and Manage ItemsIn Excel VBA, a dictionary key is a unique identifier used to store and retrieve a corresponding value.…
- Excel VBA Error 91: Why an Object Variable Is Not SetExcel VBA Run-time error 91 means your code tried to use an object variable that does not currently…
- PowerPoint VBA: How to Loop Through SlidesTo loop through every slide in the open PowerPoint presentation, use a VBA For Each loop over ActivePresentation.Slides.…
- VBA Pivot Tables: Create, Refresh, and Set Fields EasilyYes. In Excel for Microsoft 365 on Windows, VBA can create a PivotTable, assign its row, column, filter,…
- VBA Loop Only Through Visible Rows?Yes. In Excel VBA, loop through the rows in your data range and check whether each row is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.