Option Button Excel VBA
Written by
Reviewed by
In this Article
In VBA, you can create an Option button to enable a user to choose from several options. An Option button is often used in UserForms, but can also be used in a Worksheet. In this tutorial, you will learn how to create an Option button, get a user choice in VBA and use it in code.
If you want to learn how to create a Checkbox, click here: VBA Checkbox
In order to insert an Option button in the Worksheet, you need to go to the Developer tab, click Insert and under ActiveX Controls choose Option button:
Image 1. Insert an Option button in the Worksheet
When you select the Option button which you inserted, you can click on Properties under the Developer tab:
Image 2. Change the Option button Properties
Here you can set different properties of the Option button. For the beginning, we changed the attribute Name to optOptionButton1. Now, we can use the Option button with this name in VBA code.
Also, we changed the text which appears next to the Option button to Male. To do this, you need to set the attribute Caption.
As we want to allow a user to choose gender, we will repeat the whole process for another Option button. The second one has name optOptionButton2, while the caption is Female. You can also create a new Option button by copying the existing one and changing properties. Now our Worksheet has two option buttons:
Image 3. Insert the second Option button
The purpose of an Option button is to get a users decision. We will show in the example how to write Male in the cell C3 if the Option button 1 is chosen or Female if the second one is chosen. Every Option button has its event which triggers when its chosen – Click.
Therefore, for the first Option button we need to put the following code in the event Click of the object optOptionButon1:
Private Sub optOptionButton1_Click()
If Sheet1.optOptionButton1.Value = True Then
Sheet1.Range("C3") = "Male"
End If
End SubSimilarly, we will put this code in the event Click of the object optOptionButton2:
Private Sub optOptionButton2_Click()
If Sheet1.optOptionButton2.Value = True Then
Sheet1.Range("C3") = "Female"
End If
End SubThe value of the Option button is in the Value attribute of the object optOptionButton1 or optOptionButton2. The value of the Option button can be true if it is chosen or false if it’s not chosen.
Image 3. Return Male if the first Option button is chosen
As you can see in Image 3, we clicked on the first Option button. Therefore, the value of Sheet1.optOptionButton1.Value is true, so the result in C3 is Male.
Image 4. Return Female if the second Option button is chosen
Similarly to the previous example, the value of C3 is changed to Female, as we have chosen the second Option button. In this case, the procedure optOptionButton2_Click() is triggered.
As we mentioned, Option button is most often used in UserForms. To explain how you can do it, we will first insert an UserForm. In VBA Editor, right-click on Module name, click on Insert and choose UserForm:
Image 5. Insert a Userform
To display controls for inserting, you need to enable the Toolbox. To do this, click on the Toolbox icon in the toolbar. After that, you will get the windows with all the controls available. You can click on the Option button to create it in the Userform:
Image 6. Insert an Option button in the Userform
In the properties window, we change the name of the Option button to optOptionButton1 and caption to Male. Similarly, we created another Option button for Female. When we run the Userform, we get two Option buttons:
Image 7. Two Option buttons in the Userform
If you want to get a selected Option button, you need to use the same logic as for a Worksheet, which is explained earlier in the article.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
