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.
Introduction to Excel VBA Option Button
The 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. We can add option buttons in Excel using the Form Controls or ActiveX Controls.
Option Button Click Event in Excel VBA: Step-by-Step Process to Create & Apply
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.
- 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 Caption ‘Pass’ 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.
- Finally, we can see the second option button in the screenshot below.
Read More: How to Use Option Button in Excel
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:
- 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.
- 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
- Next, click on the Save button to save the VBA code (see screenshot).
- 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.
- 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).
Read More: How to Set Option Button Value in Excel VBA
Download Practice Workbook
Download the practice workbook from here.
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.