Excel VBA option buttons are the same as checkboxes
except that option buttons are dependent on each other while checkboxes
are not. This means that when you check one option button the other
option button will automatically uncheck.
Excel 2003 users. Click on Option Button from the Control ToolBox.
1. Draw two option buttons on your worksheet.
2. You can change the captions of the option buttons by right clicking on the option button and then clicking on Properties and Caption (make sure Design Mode is selected).
3. You can change the name of the option button by right clicking on the option button and then clicking on Properties and Name. For now we will leave OptionButton1 and OptionButton2 as the names of the option buttons.
1. Right click on the first option button. Click on View Code. Add the following code line:
2. Right click on the second option button. Click on View Code. Add the following code line:
3. Exit the Visual Basic Editor and click on the option buttons.
Result:
You will see that when you check the 'Female' option button the 'Male' option button will automatically uncheck and vice versa.
Draw an Option Button
Excel 2010 and Excel 2007 users. Click on Insert from the Developer tab and then click on Option Button in the ActiveX Controls section.Excel 2003 users. Click on Option Button from the Control ToolBox.
1. Draw two option buttons on your worksheet.
2. You can change the captions of the option buttons by right clicking on the option button and then clicking on Properties and Caption (make sure Design Mode is selected).
3. You can change the name of the option button by right clicking on the option button and then clicking on Properties and Name. For now we will leave OptionButton1 and OptionButton2 as the names of the option buttons.
Refer to Option Button in your Code
To refer to a option button in your Excel VBA code, execute the following steps:1. Right click on the first option button. Click on View Code. Add the following code line:
Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then Range("D3").Value = 30
End Sub
If OptionButton1.Value = True Then Range("D3").Value = 30
End Sub
2. Right click on the second option button. Click on View Code. Add the following code line:
Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then Range("D3").Value = 50
End Sub
If OptionButton2.Value = True Then Range("D3").Value = 50
End Sub
3. Exit the Visual Basic Editor and click on the option buttons.
Result:
You will see that when you check the 'Female' option button the 'Male' option button will automatically uncheck and vice versa.
