This article covers the basics of using Form Controls in an Excel worksheet, and originated from one of my blog posts, which can be found here.
While I don't tend to use Form Controls a great deal, they are certainly one of the Excel features I'm glad are in the toolbox. The main reasons I'll use one of the Form Controls are:
- They give an obvious visual style to your worksheet
- They can be used to force users to choose one of your provided options (Pro-active data validation)
ActiveX vs Forms controls?
Excel actually has two sets of controls available to put on a worksheet. ActiveX controls, which are available from the Control Toolbox, and Form Controls.
ActiveX controls are pretty robust, allowing you to change many of their properties, such as their backgrounds. They also have a variety of built in macro events that are linked specifically to the objects. They do have their drawbacks, however. If you don’t know VBA, you may have a much harder time working with them, as you need to write code to do things with them.
By contrast, Form Controls are much more simple. They have built in methods to easily place values in worksheet cells with no programming needed. Unfortunately, you can’t manipulate the display of them much at all. Having said that, they can be quite simple to use, and can help in forcing your users to choose amongst the options you give them.
The remainder of this article will focus on how to set up and use the Form Controls that I use most often; Group Box, Option Buttons, List Boxes, Check Boxes, Combo Boxes, Spin Buttons and Buttons.
Where do you find the Form controls?
If you are using Office 2007, both the ActiveX and Forms controls are located in the Controls group on the Developer tab. Click “Insert” there, and you’ll get the list shown below:
- Excel 2007: Go to the Office button, choose Excel Options, and check the "Show Developer Tab in the Ribbon" checkbox on the Popular tab.
- Excel 2010: File-->Options-->Customize Ribbon, and check the checkbox next to the Developer Tab on the right
One of the great things you can do with these controls is to force a user to choose a value from a list. We’ll illustrate this by creating one to begin with. Go to a blank sheet in your workbook and enter the following list of colours. (Or colors if you happen to be one of my US friends. )
If you don’t know how to do this easily, go to Insert|Name|Define (or Formulas|Defined Names|Define Name if you’re using Excel 2007 or higher.) Type “rngColours” in the Name box, and make sure that the cells that contain your list are in the “RefersTo” box.
We’re now ready to use one of the Form controls on our sheet.
Combo Box and List Box
Combo boxes and list boxes are virtually identical in use, but vary in their presentation. While the list box shows all items in the list, combo boxes keep only one line until you click the arrow to expand them. For this example, we’ll use a combo box. Personally, I find these to be one of the most appealing of the controls, as they have a small footprint but still contain the list of data. So move to a new sheet (let’s keep it separate from our data), and draw a Combo Box on your worksheet:
- Click the Combo Box on the Forms toolbar
- Starting at the upper left of cell A3, left click and drag your mouse to cover to the bottom right of B3
At any rate, let’s right click the control, and choose “Format Control”. The true power of this control is all on the “Control” tab, so let’s navigate there.
In the “Input Range” field, fill in “rngColours” (or whatever you called the range). You could also refer to the range using Excel’s standard A1 format if you like.
The “Cell Link” is the big key here. This is the cell that the selected value will drop in to when we select something. Let’s make this C3 for now, and then click OK.
Click somewhere on the sheet to get out of design mode (the little circles surrounding the control will go away), and then left click it. You should notice that it gives you a drop-down list with all the colours. Select one.
Now, in cell C3, you should see a number. This is the number of the item from the list. Great, you say, but that’s not what I chose… how do I get the actual colour?
This is actually part of the power of the forms controls. You get the index number of what was chosen, which allows you to look up things in tables. Let’s enter the following in cell D3: =Index(rngColours,C3)
Go and change the combobox values a few times. You should see that your colours keep updating.
Tip: Put the value of the combo box in cell A3, to hide it behind the control, or place it in a hidden range to keep it out of the users’ view.
Group Box and Option Buttons
The Group Box is a control that is specifically used to keep Option Buttons together and have them work as a group. To illustrate this, let’s go to a new sheet, and add a nice big Group Box:
- Click the Group Box on the Forms toolbar
- Starting at the upper left of cell A3, left click and drag your mouse to cover to the bottom right of D16
- Click Option Button and draw it within the Group Box, starting upper left of B4 to lower right of C4
- Do this 5 more times, on rows 6, 8, 10, 12 and 14
- Change the names, from top to bottom, to Red, Orange, Yellow, Green, Blue, Purple
Before we move on, let’s set up the formula to return the chosen colour. In F3, enter the formula =Index(rngColours,E3).
Now, try selecting on of the option buttons. You should see the colour you’ve chosen show up in F3. I should point out that this has nothing to do with the names you assigned to the buttons, and everything to do with the value you chose. This is why we made sure we named the buttons from top to bottom in the order our list was set.
- Only one Option Button within the frame can be selected at a time. If you add a button outside the frame, however, it could be selected as well as one from within the frame. Likewise if you have multiple frames… one in each.
- Only one cell link needed to be set up. This setting affects all option buttons within the frame.
The spin button is a bit different from the afore-mentioned controls. Create a new sheet again, and drop a spin button control on your worksheet. (You can choose where.)
Right click it and have a look at the following screen (captured from Excel 2007):
So what is all this?
- Current Value tells you what the value of the spin box is currently set to. Since we’ve just created it, it’s zero
- Minimum value is as low as the spin button can go. You cannot set a value below zero, but you can set it higher. Let’s set it to 1.
- Maximum value is whatever you choose. In this case, we’ll set it to 6.
- Incremental change is how much you want the spin button to change by each time you click the button. We’re going to leave this at 1.
- Cell link, as you might expect, is where you want the value outputted to. Choose B3.
If you’ve been following along with this post, you’ve probably guessed that the formula in C3 is =Index(rngColours,B3). Click up or down on the spin button, and you should see the values change:
The values I had in the spin button configuration were:
- Minimum: 0
- Maximum: 1000
- Incremental: 1
The checkbox is similar to an Option Button, except that you do not need to place it inside a Group Box. In addition, each checkbox can be selected individually from all others, meaning that you can have one, some, all or no checkboxes in your worksheet checked. The cell link, accessed the same was as the controls above, places only a TRUE/FALSE value in the cell.
The button control is the most simple of all. It pretty much just acts as a very simple macro launcher. As soon as you add it to a worksheet, it will ask you which macro you wish to run.
While I haven’t covered them all, hopefully this has given you enough information to use the most common of Excel’s built in Form controls. They can be a great addition to help you control your users actions in your worksheets and, combined with worksheet level security, are one way to encourage your users to make valid data selections. Properly set up, they can also add a certain flair or “bling” to your worksheets as well.