Create Option Buttons in UserForm with Excel VBA

Objective:

We will create a UserForm with a few Option Buttons. When an Option Button is selected, it triggers a specific action or change in another part of the UserForm (like displaying a message or modifying a value).

Step-by-Step Guide:

1. Open the Visual Basic for Applications (VBA) Editor:
   • Press Alt + F11 to open the VBA editor in Excel.
   • In the editor, go to Insert > UserForm to create a new UserForm.
2. Adding Option Buttons to the UserForm:
   • Once the UserForm is open, you’ll see a toolbox. If it’s not visible, go to View > Toolbox to enable it.
   • In the Toolbox, locate the « OptionButton » control (it looks like a small circle).
   • Click the OptionButton control, then click on the UserForm where you want to place it.
   • Repeat the above steps to create multiple Option Buttons (e.g., for different options like Option1, Option2, Option3).
3. Adjusting Properties:
   • You can change the name, caption, and other properties of the Option Buttons. Right-click on an Option Button and select Properties to view and edit properties.
   • Change the Name property to something like OptionButton1, OptionButton2, etc., and change the Caption property to something like « Option 1 », « Option 2 », etc.
4. Writing the VBA Code: Now, let’s write VBA code that will handle the events when the user selects an Option Button.

Private Sub UserForm_Initialize()
    ' Setting initial state of the Option Buttons
    OptionButton1.Caption = "Option 1"
    OptionButton2.Caption = "Option 2"
    OptionButton3.Caption = "Option 3"   
    ' Setting the default selected Option Button
    OptionButton1.Value = True ' Default to Option 1
End Sub

Private Sub OptionButton1_Click()
    ' Code to execute when Option 1 is selected
    MsgBox "You selected Option 1"
End Sub

Private Sub OptionButton2_Click()
    ' Code to execute when Option 2 is selected
    MsgBox "You selected Option 2"
End Sub

Private Sub OptionButton3_Click()
    ' Code to execute when Option 3 is selected
    MsgBox "You selected Option 3"
End Sub

Explanation of the Code:

1. UserForm_Initialize:
   • This event is triggered when the UserForm is loaded.
   • The code sets the captions of the Option Buttons (OptionButton1.Caption, OptionButton2.Caption, etc.).
   • The default Option Button is set by setting the Value property of OptionButton1 to True.
2. OptionButton1_Click, OptionButton2_Click, OptionButton3_Click:
   • These events are triggered when the user clicks an Option Button.
   • Each event shows a message box (you can replace this with any action you want to perform when an option is selected).

How the Code Works:

• The UserForm_Initialize subroutine initializes the UserForm with default settings and captions for the Option Buttons.
• When the user selects an Option Button, the respective Click event is fired, triggering a message box to inform the user of the selection. This can be customized to perform any other action, like updating a worksheet, changing the UserForm interface, etc.

Testing the Form:

1. Close the VBA editor and go back to Excel.
2. Press Alt + F8, select your UserForm from the list, and click Run.
3. The UserForm will pop up with the Option Buttons.
4. Select an Option Button to see the respective message.

Additional Customizations:

• Grouping Option Buttons: By default, Option Buttons within the same group behave mutually exclusive (i.e., when one is selected, the others are deselected). You can create multiple groups by placing Option Buttons in different Frame controls.
• Using the Value Property: The Value property of Option Buttons can be used programmatically to check if an Option Button is selected (True) or not (False).

Example for using Value Property:

If OptionButton1.Value = True Then
    MsgBox "Option 1 is selected."
ElseIf OptionButton2.Value = True Then
    MsgBox "Option 2 is selected."
Else
    MsgBox "No option selected."
End If

This checks the value of each Option Button and executes the appropriate code based on which one is selected.

Conclusion:

By using Option Buttons in a UserForm, you can create interactive and dynamic interfaces in Excel. The above code allows for handling multiple options and triggering corresponding actions in VBA, providing flexibility to your Excel applications.