UserForm and Multiple Option Buttons in VBA

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.

Save the File with Macro Enabled Extension (.xlsm)

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.

Create Table to Transfer the Data from Form to Excel 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.

Insert a Command Button from 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.

Draw a command button on sheet
Change the properties on CommandButton1

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.

Open VBA Window

Step 6 – In VBA widow, let us insert a UserForm. To do that, just click on Insert Menu then click on ‘Form’.

Insert a UserForm

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.

Form designing and setting the properties e.g. Name, Caption and GroupName for OptionButton

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.

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()
frmForm.Show
End Sub

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.

Download
Click to download

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here