Excel Tip of the Week #101 - Form controls

Hello and welcome back to the Excel Tip of the Week.  This week, we have a Developer post in which we looking at how to add dynamic controls into a spreadsheet, such as buttons, checkboxes, and dropdown menus.

Developer menu

These options are on the Developer tab of the Ribbon.  This tab is hidden by default in Excel, as it also contains some of the more advanced macro-related features.  To view this tab in Excel 2007, check 'Show Developer Tab in Ribbon' from the Excel options menu.  For Excel 2010+, follow this procedure:
 - File
 - Options
 - Customize Ribbon
 - From the right-hand pane, check the box next to ‘Developer’
 - OK

What are form controls for?

Form controls are simple, visually appealing options for controlling inputs.  You can create checkboxes, dropdowns, buttons, and other such familiar and convenient objects, and then have those objects control cells within the workbook.

Insert menu

The Insert menu, shown above, is the one needed to add a Form Control.

After selecting the option you want from the menu, you can then add the object to your sheet.  It will appear by you either clicking or dragging to draw a shape.

There are several options on display here, but we’ll focus on the most popular:
 - Button (top row, 1st item) – Draw a button.  You can then assign a macro to the button, which will run each time the button is pressed.  See TOTW #81 and #82 for more on macros.
 - Combo box (top row, 2nd item) – Draw a dropdown menu.  You can then right-click and use Format Control to set the INPUT RANGE (the range of cells with the list of options) and the CELL LINK (a cell which will show 1,2,3 etc. depending on which option has been chosen).
 - Check box (top row, 3rd item) – Insert a checkbox.  You can right-click and Format Control to create a cell link, which will show TRUE or FALSE depending on the status of the checkbox.
 - Spin button (top row, 4th item) – Insert an up/down arrow.  Format Control lets you link these buttons to another cell, which is then incremented by pressing the buttons.

In the attached file, you will see examples of each of these four.  Try creating your own Form controls, and perhaps experiment with some of the other options on display here.

In general, if you want to operate a form control, left click on it.  If you want to edit (move, alter, rename) a form control, right click on it.  You can also use this to review other’s form controls and find out how they work / where they’re linked to.

Previous post - Working with arrays 
TOTW index
Next post - Go To & Go To Special

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.