How to Create & Apply Option Button Click Event in Excel VBA

In Excel VBA, we can form an option button that allows the users to choose a particular option. Usually, the option button is found in UserForms, but sometimes it is applied in Excel worksheets. In this tutorial, we will learn the option button click event in Excel VBA with a step by step guide. Let’s see the steps below.


Download Practice Workbook

Download the practice workbook from here.


Introduction to Excel VBA Option Button

Option button refers to a type of icon that allows the user to choose just one option. When there are multiple instances of this button on a document, the user may only choose one. Numerous Excel applications frequently use this button. The values of the option buttons are fixed and cannot be changed.


Step by Step Process to Create & Apply Option Button Click Event in Excel VBA

This tutorial will guide you with some quick steps to apply the Excel VBA option button click event easily. Here, we will create the option button to decide if a candidate is Selected or Rejected. So without further delay, let’s get started.


Step 1: Forming Option Button in Excel

We will first form the option button for its click event using Excel VBA. To do so, we need to follow some steps. The steps are below:

  • Firstly, open the Excel worksheet.
  • Then, go to the Developer tab.
  • Later, go to the Controls group.

Forming Option Button in Excel

  • Now, click on the Insert button.
  • Therefore, click on Option Button from the ActiveX Controls section.

  • Consequently, drag the first option button (OptionButton1) on the worksheet (see screenshot).

  • Afterward, keeping the selection on the option button, go to the Developer tab again.
  • Thereupon, click on Properties.

  • In turn, the Properties window will pop up.
  • At this time, go to the Alphabetic tab > (Name) > type Option1 > Alphabetic tab > type Pass > close the window.
  • See the screenshot below for a better understanding.

  • As a result, we can see the CaptionPass’ on the option button.
  • Besides, in the Name Box, we can see the name (Option1) that we typed.

  • Similarly, insert the second option button in the same worksheet.
  • But this time, type Option2 for (Name).
  • Go to Caption and type Fail.

Forming Option Button in Excel

  • Finally, we can see the second option button in the screenshot below.

Read More: How to Use Option Button in Excel (2 Easy Ways)


Similar Readings


Step 2: Assigning the Selected Option in Excel VBA

In this step, we will demonstrate the process for the option button click event using Excel VBA. Here, we will assign the selected option in Excel VBA. In this step, we need to be very careful at the time of typing the VBA code. We will apply the VBA code in cell D5 of our worksheet. That means we will get the output in this cell. Follow the steps below to execute the VBA code:

Assigning the Selected Option in Excel VBA

  • First of all, click on the first option button (Pass).
  • Secondly, go to the Developer tab.
  • Now, confirm that the Design Mode is active.

  • Then, right-click on the option button (Pass).
  • Consequently, select View Code.

Assigning the Selected Option in Excel VBA

  • As a result, the VBA code window will open.
  • Eventually, enter the following VBA code in the code window:
Private Sub Option1_Click()
If Option1.Value = True Then Range("D5").Value = "Selected"
End Sub

  • Subsequently, go to the Developer tab to deactivate the Design Mode option.

  • Therefore, click on the Pass option button.
  • Hence, you will see the output (Selected) in cell D5 as mentioned in the VBA code.

  • Similarly, for the second option button, select the option button (Fail) > go to the Developer tab > turn on Design mode.

Assigning the Selected Option in Excel VBA

  • Thereupon, click on View Code by right-clicking on the option button (Fail).

  • Eventually, insert the VBA code below in the code window:
Private Sub Option2_Click()
If Option2.Value = True Then Range("D5").Value = "Rejected"
End Sub

  • After inserting the code, click on the Save button (see screenshot).

  • Later, turn off the Design Mode option in the Developer tab.

  • Lastly, if you click on the Fail option button then the D5 cell will show ‘Rejected’ as the output (as mentioned in the code).

Assigning the Selected Option in Excel VBA

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


Conclusion

I hope this article will be helpful for you to understand the option button click event using Excel VBA. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.


Related Articles

Sagufta Tarannum

Sagufta Tarannum

Hi, I am Sagufta. I have completed my graduation in Civil Engineering from Bangladesh University of Engineering and Technology. I am very much interested about research and innovation in the field of Civil Engineering.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo