Excel Form Controls
Excel form controls helps you turn your Excel workbook book into something more. They add interactivity to your workbook. Here is some explanation about form controls…
Form controls can be found under developer tab/insert button on your ribbon. If you don’t see any developer tab in your ribbon, that means it is not enabled. To learn how to enable it see Enabling Developer Tab in Excel .
Here is how does it looks like:
Every form control has different attributes and options. You can use these to add interactivity to your workbooks and impress everyone around. Now lets take a look at the ones which we will use frequently.
To make it easier to grasp, I will go over an example. Lets say we have a small stall where we sell fruits. We want to make an excel list where we can track our sales with fancy reports and stuff. But we want to do it style. For that, we need to know how to use form controls.
Combo Box : Combo box is for having users select values from a controlled list of your choice. To use it, click on combo box icon from above menu than click on where you want it to be placed on your workbook(lets put it on G7). To get it working, right click on it and select “Format Control” from menu. From here, add your list of values that you want it yo show as “input range”, for this example it is the fruits that we sell (C6:C11). Than add destination cell where you want change to happen as “cell link”, for this example we will use “M7”. And set how many rows you’d like your combo box to show (lets make it 6 rows).
Once finished, you will have a combo box that expands to 6 rows showing our list of fruits when clicked. See how value of cell M7 changes with each selection. When combined with IF or CHOOSE formulas, it makes things interactive. We will use this with formulas and charts to make slick interactive report in upcoming posts.
Check Box : Check box is for determining whether some value is true or false. In other words it lets user to say this is ok/done or n.ok/not done. So when we’d like to make multiple selections like checklists, etc., check-boxes are our best friends.
Similar in any form control, after placing check-boxes on your sheet, you need to access Format Control menu and select a target cell for your control. For this example address y our six check box for fruits to cells between M13 to M18, all to a corresponding single cell. When you click on any of these check box’ you will see target cell get a new vaule (TRUE for checked, FALSE for unchecked). Check boxes comes with a default label attached to them. On the example workbook I removed text from check boxes.) We will use this with formulas and charts to make slick interactive reports in upcoming posts.
Spin Button & Scroll Bar : These two controls are basically same in terms of settings and behavior. After placing either one of these controls in your sheet, you will need to assign; current value(for this example 1 for start), minimum value(for this example 1), maximum value(for this example 6) and incremental change value (1 for 1 by 1 change). Now you will see that whenever you click on any arrow of your spin button or scroll bar, you will see value of target cell change by one in the direction that arrow points. We will use this with formulas and charts to make slick interactive reports in upcoming posts.
List Box : List box is almost same as combo box in terms of usage and function. Main difference between them is while combo box is a collapsible list, list box is a statics list that is expanded to include all values within related range by default. After placing your list box into your worksheet, assign data range to be shown inside of list box and target cell.
Option Button : Option buttons looks similar to check boxes. But instead of doing true/false check, they are used for selecting from a list of options. So the trick here is to assign same cell for all your option buttons of a certain list (assign target to first one, then any other option buttons you created will automatically target same cell). By this time you should feel familiar with form controls concept. So just place some option buttons to your sheet and observe how do they behave.
Button : We will not use button untill we start working with VBA. Button is mainly used for triggering macros in your workbook.
Download example workbook to see form controls in action!
