How to Set the 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.

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

VBA Code Breakdown

VBA Code for Option Buttons:
- We have defined separate procedures for each option button (e.g., sBen_Click, sNatasha_Click, etc.).
- These procedures call the Student_Info function, passing the corresponding option button as an argument.
User-Defined Function: Student_Info
- This function takes one argument, which is the selected option button (e.g., sBen).
- We declare a variable cRange of type Range.
- The function searches for the student name (captured in the option button’s caption) within column B of the dataset (presumably on Sheet1).
- If a match is found (cRange is not Nothing), it retrieves the associated values:
  - MarkBox.Value is set to the value in the column one cell to the right of the found student name.
  - GradeBox.Value is set to the value two cells to the right.
  - PositionBox.Value is set to the value three cells to the right.

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.