The Option button or radio button is an important feature in the UserForm. We can set the option button value and use VBA macro to execute various commands. In this article, we are going to show you 4 quick steps to set the option button value in Excel VBA.
Excel VBA Set Option Button Value: Step-by-Step Procedures
There will be 4 steps to achieving our goal for this article. Firstly, we will set up a dataset from which we will return values when we click an option button. Secondly, we will insert a user form and add elements to it. Thirdly, we will apply VBA code to return values when an option button is clicked. Lastly, we will insert a Button (Form Controls) to show the user form, and depending on the option button, it will return relevant results.
Step 1: Setting Up Dataset
In this first step, we will create a dataset from which we will return the data. There are four columns in this dataset: “Student”, “Mark”, “Grade”, and “Position”. This dataset represents the score in a particular subject for six students in a class.
- The grade distribution is as follows:
- Above 90: A.
- 80 to 89: B.
- 70 to 79: C.
- 60 to 69: D.
- These grade data are entered manually; you can use the IF, IFS, or Nested IF functions to do so.
- After that, we entered the position of the students manually. You can automate this using the RANK function.
- We will select the student names in the Option button, and it will display the remaining three values in the user form.
Read More: How to Add Option Button in Excel
Step 2: Inserting UserForm
In the second step, we will insert the UserForm. Then, we will use the Label, TextBox, and OptionButton to finish creating the UserForm. Moreover, we will change the properties of these using the Properties pane, which we will bring up using the F4 key.
- To begin with, press ALT+F11 to bring up the VBA window.
- Next, from Insert, select UserForm.
- So, this will bring up the UserForm1 window.
- Moreover, we can see the Toolbox alongside it.
- Firstly, we will create a TextBox inside the UserForm.
- So, select TextBox from the Toolbox and drag it inside the UserForm1 window.
- Therefore, it will create a text box with the tag “Label1”. Just click on it and type “Student” to change it.
- Next, select OptionButton from the Toolbox and drag inside UserForm1.
- Then, it will show “OptionButton1” as the label.
- Next, we will change the values. To do so, press F4 to bring up the Properties pane.
- Then, type “sBen” in the Name field.
- After that, type “Ben” in the Caption field.
- Similarly, add five more option buttons.
- Everyone’s Name and Caption will be in the same pattern. For example, for Maria, the Name box value is sMaria and the Caption is “Maria”. You can type other things too, but you will need to change accordingly when applying VBA codes.
- After that, add three labels called “Mark”, “Grade”, and “Position”.
- Then, insert three text boxes by clicking on the TextBox button and dragging inside UserForm1.
- Then, we will change the Name field to “labelBox” format. For example, beside the Mark label, we name the text box as “MarkBox”. The other two names are “GradeBox” and “PositionBox”.
- Then, select “True” inside the “Locked” field.
Step 3: Applying VBA Code
In the third step, we will apply the VBA codes to return values from our selections from the option buttons. This means if we select “Ben”, then the code will display the “Mark”, “Grade”, and “Position” of “Ben” in that particular course.
- Firstly, double-click on “Ben” inside the user form.
- Then, a Module window will appear.
- After that, type the following code.
Option Explicit
Private Sub sBen_Click()
Call Student_Info(sBen)
End Sub
Private Sub sNatasha_Click()
Call Student_Info(sNatasha)
End Sub
Private Sub sDaniel_Click()
Call Student_Info(sDaniel)
End Sub
Private Sub sLeonardo_Click()
Call Student_Info(sLeonardo)
End Sub
Private Sub sMaria_Click()
Call Student_Info(sMaria)
End Sub
Private Sub sRoss_Click()
Call Student_Info(sRoss)
End Sub
Private Sub Student_Info(StudentName As MSForms.OptionButton)
Dim cRange As Range
Set cRange = Sheet1.Columns("B").Find(StudentName.Caption)
If Not cRange Is Nothing Then
MarkBox.Value = cRange.Offset(0, 1).Value
GradeBox.Value = cRange.Offset(0, 2).Value
PositionBox.Value = cRange.Offset(0, 3).Value
End If
End Sub
VBA Code Breakdown
- Firstly, we are setting it mandatory to declare the variable types.
Option Explicit- Secondly, we are calling the Student_Info function for each of the option buttons. There is an argument inside that function, which is the Name field value for the buttons.
Private Sub sBen_Click()
Call Student_Info(sBen)
End Sub
Private Sub sNatasha_Click()
Call Student_Info(sNatasha)
End Sub
Private Sub sDaniel_Click()
Call Student_Info(sDaniel)
End Sub
Private Sub sLeonardo_Click()
Call Student_Info(sLeonardo)
End Sub
Private Sub sMaria_Click()
Call Student_Info(sMaria)
End Sub
Private Sub sRoss_Click()
Call Student_Info(sRoss)
End Sub- Thirdly, we are creating a user defined function named Student_Info which takes one argument.
Private Sub Student_Info(StudentName As MSForms.OptionButton)- Then, we specify the variable type.
Dim cRange As Range- Afterward, we set the variable to look for the student name from column B.
Set cRange = Sheet1.Columns("B").Find(StudentName.Caption)- Lastly, we set the values to return. Here, we are moving to the right side of the “Name” column. For example, the “Grade” column is two columns right of column B. That is why we used the Offset(0,2) property here.
If Not cRange Is Nothing Then
MarkBox.Value = cRange.Offset(0, 1).Value
GradeBox.Value = cRange.Offset(0, 2).Value
PositionBox.Value = cRange.Offset(0, 3).Value
End If
End SubRead More: Radio Button in Excel Without Macro (Insert, Copy, Group & Delete)
Step 4: Adding Button to Show UserForm
In the last step, we will add a Button (Form Controls) from the Developer tab inside the Excel file to show the UserForm. We will need to type a small code to do so. After doing that, we will be able to bring up the UserForm and it will return value upon clicking on the option buttons.
- To begin with, from the Developer tab → Insert → select Button (Form Controls).
- Then, the cursor will change and drag that in row 11 to create the button.
- After that, right-click on the button and select “Assign Macro…”.
- After that, type the following code.
Option Explicit
Sub Displaying_Userform()
UserForm1.Show
End Sub
- Our user form is called UserForm1, and using the Show method, it will display it whenever we click on the button.
- Alternatively, you can type the code beforehand and, upon releasing the mouse while creating the button, it will ask us to assign a pre-existing macro to use.
- Finally, after doing all these steps, we can click on the button, and it will show the user form. Moreover, when we click on the student names on the option buttons, it will show the mark details in the text box. Thus, we can set the option button value in Excel VBA.
Download Practice Workbook
Conclusion
We have shown you four quick steps to set the option button value in Excel VBA. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible.
Thanks for reading, keep excelling!
Related Articles
- How to Group Radio Buttons in Excel
- How to Add Radio Buttons in Excel
- How to Use Option Button in Excel
- How to Create & Apply Option Button Click Event in Excel VBA
- How to Insert Excel VBA Radio Button Input Box
