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

Step 1 – Prepare the Dataset

In this initial step, we’ll create a dataset containing four columns: Student, Mark, Grade, and Position. The dataset represents the scores of six students in a particular subject. The grade distribution is as follows:

• Above 90: A
• 80 to 89: B
• 70 to 79: C
• 60 to 69: D

You can manually enter these grade values, or use Excel functions like IF, IFS, or Nested IF to populate them.

Additionally, manually enter the student positions, or 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 – Insert the UserForm

• Open the VBA window by pressing ALT+F11.
• From the Insert menu, select UserForm. This will open the UserForm1 window.

• Alongside it, you’ll see the Toolbox.

• Create a TextBox inside the UserForm by selecting TextBox from the Toolbox and dragging it into the UserForm1 window.
• Change its default label (Label1) to Student.

• Drag an OptionButton into the UserForm1. It will be labeled OptionButton1.

• Modify the values:
  • In the Properties pane (F4), set the Name field to sBen and the Caption field to Ben.

  

• Add five more OptionButtons following the same pattern (e.g., sMaria with caption Maria).

• Add labels for Mark, Grade, and Position.
• Insert three text boxes (MarkBox, GradeBox, and PositionBox) inside the UserForm1.

• Set the Locked property to True for each text box.

Step 3 – Applying VBA Code

• Double-Click on Ben Inside the User Form:
  • When you double-click on the Ben option button within the user form, a Module window will appear.

• After that, insert 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

Read More: Radio Button in Excel Without Macro (Insert, Copy, Group & Delete)

Step 4 – Adding a Button to Show the UserForm

• Open your Excel file and go to the Developer tab.
• From the Insert section, select Button (under Form Controls).

• Your cursor will change, and you can drag the button to row 11 (or any desired location) to create it.
• Right-click on the newly created button.
• Choose Assign Macro…

• In the dialog box, insert the following code:

Option Explicit
Sub Displaying_Userform()
UserForm1.Show
End Sub

• The user form we created earlier is named UserForm1, and using the Show method, it will display when we click the button.

Alternative Approach (Assigning Macro During Button Creation):

• • Alternatively, you can insert the code before creating the button.
  • When you release the mouse after creating the button, Excel will prompt you to assign a pre-existing macro. Select the Displaying_Userform macro.

• Testing the UserForm:
  • After completing these steps, click the button.
  • The UserForm will appear, and when you click on student names using the option buttons, it will display the corresponding mark details in the text boxes.

This way, you can set the option button value in Excel VBA.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Group Radio Buttons in Excel
• How to Create & Apply Option Button Click Event in Excel VBA
• How to Insert Excel VBA Radio Button Input Box