How to Set Option Button Value in Excel VBA (4 Easy Steps)

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.

Similar Readings

• How to Use Option Button in Excel
• How to Insert Excel VBA Radio Button Input Box
• How to Make a Calculate Button in Excel
• How to Use VBA Code for Submit Button in Excel
• How to Clear Cells in Excel with Button
• How to Add Radio Buttons in Excel
• Radio Button in Excel Without Macro
• How to Create & Apply Option Button Click Event in Excel VBA

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

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

Read More: How to Create Button to Link to Another Sheet in Excel

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.

Read More: How to Edit a Macro Button in Excel

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 Create a Macro Button in Excel
• How to Create Button Without Macro in Excel
• How to Add Up and Down Buttons in Excel
• Change Cell Value Using Toggle Button in Excel
• How to Group Radio Buttons in Excel
• How to Change Color of Toggle Button When Pressed in Excel
• How to Add Command Button Programmatically with Excel VBA
• How to Remove a Form Control in Excel
• How to Create Chart Slider in Excel
• How to Make Games in Excel