Welcome to my latest hub on Form and ActiveX Controls in Excel 2007 / Excel 2010. Today, we will investigate the Option Button (also known as the Radio Button). Option Buttons are perfect if you want users of your spreadsheet to select only one option from a list. The option that your user selects can then be stored for future use.
Form Control and ActiveX Control Option buttons are very similar; the ActiveX button allows you to change colours and fonts should you wish to. In addition, ActiveX buttons allow you to create groups of buttons, whereas Form Control buttons will be automatically added to any nearby groups and cannot be reconfigured. The below figure shows this difference, the right hand buttons are ActiveX (and have been assigned to different groups) and the user can select from two of the five options available, rather than one from the five options on the left (Forms Control buttons).
In today’s hub, we will create and configure two buttons, one Form and one ActiveX button. Alongside creating the buttons, we will also convert the results of the user’s selection into something more meaningful (the default result of Form Control buttons is a number and from ActiveX it is true / false).
Adding the Developer tab to Excel 2007 / Excel 2010
Before starting, we need to ensure that the Developer tab in available in Excel. If it is not, for Excel 2007:
- Navigate to the Excel button
- Click Excel Options
- On the Popular tab, ensure that Show Developer tab in the Ribbon is selected
If you are using Excel 2010:
- Click on the File menu
- Select Options
- Choose the Customize Ribbon tab
- Tick Developer under Main Tabs as shown below
Creating a Form Controls and ActiveX Option (Radio) Button in Excel 2007 and Excel 2010
To create a button:
- First navigate to the Developer tab and select the Insert button in the Controls Group and select Option Button in the Form Controls section.
- The cursor will change to a +
- Define the outline of your Option / Radio Button
Either right click and select Copy and then Paste further buttons, or repeat the three above steps to create additional buttons
The process of creating ActiveX buttons is identical, except you choose the Option Button from the ActiveX Controls section via the Insert button in the Controls Group instead.
Configuring a Form Controls Option (Radio) button in Excel 2007 and Excel 2010
Configuring a Form Controls button is quite straightforward.
- To change the button’s caption right click the button itself and select Edit
All other configurable options are available via Format Control. To access these options:
- Right click the button and select Format Control
- The Colors and Lines tab allows you to change the Fill colour and the Line colour (creates a box around the button)
- The Size tab allows you to precisely change the size of the button
- The Control tab is where you configure the Cell link which is the cell in which Excel places the result of the user’s selection
Configuring a ActiveX Controls Option (Radio) button in Excel 2007 and Excel 2010
ActiveX buttons have many more options available than Forms buttons. This additional complexity allows you to configure them far more than Forms buttons.
Note: in order to configure an ActiveX button, you must be in Design mode. To enter Design mode, click the Design Mode button in the Controls group on the Developer tab.
While in Design Mode, right click on your button and select Properties
As you can see from the above figure, there are a large number of items you can configure. I will discuss those options which you either must change or should change if it fits your design for your own buttons:
- BackColor – this allows you to change the colour of your button
- Caption – used to change the text on your button
- Font – select a different font here
- ForeColor – font colour can be adjusted here
- GroupName – if you want to create a separate group of buttons then you would choose a new name for the second group here
- Linked Cell – this cell will display whether this button has been selected
Note: Unlike Form Control buttons, EACH ActiveX button in a group MUST each have their own linked cell.
Note: to change the size of the button, I recommend doing this via the Size tab by right clicking and selecting the Format Controls tab as this tab uses centimetres.
Working with the output from your Option (Radio) Buttons in Excel 2007 and Excel 2010
Now that we have created and configured our buttons, we need to translate Excel’s output into something that we can use or display. First, let’s look at exactly what Excel produces. In the figure below:
N2 = the output from the Form Control button (the light pink one)
N3 – N7 = the output from each of the ActiveX buttons (the yellow one) in order
To translate this data into something we can use, we need to use IF statements.
=IF(N2=1,"Compact Disc", IF(N2=2,"Vinyl", IF(N2=3,"Mini Disc", IF(N2=4,"Cassette", IF(N2=5,"DVD")))))
Now the cell with this formula in it displays what the user selected rather than the number of the Option Button they selected.
=IF(N3=TRUE,"Compact Disc", IF(N4=TRUE,"Vinyl", IF(N5=TRUE,"Mini Disc", IF(N6=TRUE,"Cassette", IF(N7=TRUE,"DVD")))))
Now we have two cells that contain the actual user’s selection that we can record or use as a reference later on.
Option (or Radio) buttons in Excel 2007 and Excel 2010 allow you to create panels with a list of items from which the user can select one item. The user’s selection is then recorded in a cell which can be stored or manipulated further.
In this hub, we investigated creating and configuring Form and ActiveX Control buttons. We also looked at creating groups of ActiveX buttons which allow the creation of discrete groups of buttons as well as other differences between these and the simpler Form Control buttons. Finally, we looked at using IF statements to translate the output from our buttons into more useful outputs.
In addition to this hub on Option / Radio buttons, I have a number of other hubs on the Controls available in Excel 2007 and Excel 2010. These include:
List Boxes: are ideal for short lists such as a list of postage options or signs of the zodiac. They allow users to select from items that are listed and selectable by the user. The list itself is fully configurable and the user can select one item if it is a Form Control box or multiple items if it is an ActiveX Control box. Users can also use Shift and Control to select more than one item at a time. My hubs on ActiveX and Form Control List Boxes are located here:
Toggle Buttons: ActiveX Toggle bittons through the addition of Visual Basic code allow you to create a button that is ideal for turning things on and off in Excel. For example, you can turn grid lines or split screens on and off with the click of a single button. Click here to learn more about Toggle Buttons:
Spin Buttons and Scroll Bars allow users of your spreadsheets to quickly select values from a configured range. Spin buttons are excellent for selecting things such as dates of birth. Scroll bars are perfect for selecting numbers from ranges such as selecting an interest rate for a loan. Both are used heavily on the Internet so users now instinctively how to use both.
My hub on Spin Buttons can be found here:
My hub on Scroll Bars can also be found here:
Many thanks for reading and I hope that you found this hub interesting and informative. Please feel free to leave any comments you may have below.
Robbie C Wilson (author) on July 24, 2013:
Many thanks for your kind comment, I am glad that you found the article so useful. Option Buttons when created are put into a default group based on the sheet they are in. To resolve the issue you had with the groups, create ActiveX buttons and go into the properties of the Option Buttons and put them into separate groups. Hope this helps and thanks again for reading!
Jason Buda on July 24, 2013:
I like the versatility of the ActiveX Option Button, but when you copy the sheet the option Button takes on the same group as the prior one so a customized copy sheet macro would have to be used to get the option button group unique or else the previous sheet will lose the settings of the option button when you change the new sheet! I guess there is always a catch, huh? I appreciate your article. I learned something about the ActiveX buttons being more customizable, but I have a lot of applications where I need to duplicate the sheet. Thanks!