In this post, we will learn how to create and use Multiple Option Buttons and in two or more different groups.
Let’s understand the basic functionalities of Option Button and how it works.
Option Buttons are very useful control available in VBA when user want to select one option from a small number of choices. Option Buttons are always used in group of two or more items. When we select an Option Button from a group then other Option Buttons gets deselected in that group and this is the actual behavior of Option Button.
If a UserFom or Data Entry form contains more than one set of OptionButtons, the Option Buttons in each set must have a unique GroupName property value in VBA. If we don’t assign the unique GroupName property to OptionButtons then all OptionButtons become part of the same set. To create a group, we can also use Frame Control and enclose the OptionButtons in that. Frame will automatically groups the OptionButtons contained in the frame.
In this tutorial, we will use two different group of Option Buttons. First will be ‘Gender’ and second would be ‘Marital Status’. So, considering the above rule for Option Button, we will design our form and transfer the data from that form to Excel Table or Sheet.
So, let us start developing the form from scratch.
Step 1 – Open the MS Excel Application. Create a New Workbook and Save the file with the name ‘UserForm With Multiple Option Buttons.xlsm’. Make sure to select ‘.XLSM’ extension before saving the file.
Step 2 – Rename the ‘Sheet1’ to ‘Data’. Create Table with required Headers e.g. Name, Gender and Marital Status. Form the table with back color, border color and remove the gridline from the sheet.
Step 3 – Insert a command button on the right side of Table. To Insert a Command Button, go to Developer Tab then Click on Insert and then select Command Button Control in ActiveX Controls.
Step 4 – Now draw the Command Button on Sheet and change the Properties of Command Button. To change the Properties, right click on Button and Select Properties. In Properties window, update the Name ‘cmdShow’ and Caption ‘Show Form’. Please see the below images.
Step 5 – Let us move to Visual Basic Application window. To do that, just click on Developer Tab then click on Visual Basic button available in Code group. Alternatively, you can also press ALT + F11 to open the VBA window.
Step 6 – In VBA widow, let us insert a UserForm. To do that, just click on Insert Menu then click on ‘Form’.
Step 7 – Let us design the Data Entry form with required Controls for the fields Name, Gender and Marital Status with a command button to submit the data. Please see the below image and follow the same properties to design the form.
Step 8 – Now, we have done with designing the form. Let us double click on Submit Button and write the below code on Click event of Submit Button.
Private Sub cmdSubmit_Click()
Dim iRow As Long
iRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & iRow).Value = Me.txtName.Value
If Me.optFemale.Value Then .Range("B" & iRow).Value = "Female"
If Me.optMale.Value Then .Range("B" & iRow).Value = "Male"
If Me.optUnknown.Value Then .Range("B" & iRow).Value = "Unknown"
If Me.optSingle.Value Then .Range("C" & iRow).Value = "Single"
If Me.optMarried.Value Then .Range("C" & iRow).Value = "Married"
If Me.optOther.Value Then .Range("C" & iRow).Value = "Other"
'Reset the controls after submitting
Me.txtName.Value = ""
Me.optFemale.Value = False
Me.optMale.Value = False
Me.optUnknown.Value = False
Me.optSingle.Value = False
Me.optMarried.Value = False
Me.optOther.Value = False
MsgBox "Data submitted Successfully!"
Step 9 – Let us move to Excel Worksheet and double click on Show Form button and write the below code on it’s click event.
Private Sub cmdShow_Click()
Now, the designing and coding part is done. You can run this form and transfer the data to the table.
Please watch our YouTube tutorial for this.
Click on below button to download the Excel file used in this tutorial.
Thanks for visiting our website! You can download several free tools and codes from our site. Please visit to other pages which are useful for you.