You can use form controls to design a workbook that encourages users to make certain choices and selections. Operations can then be performed based on the selections made. Form controls can also be used to provide data options or choices for the user.
Essentially, form controls let you manage what kind of data a user can enter or manipulate and what happens in your worksheet after a user has performed specific actions. You can even combine form controls to create a simple user interface or Form for interacting with a workbook.
The following is a list of the main form controls available in Excel 2016.
Adding a Control to a Worksheet
In Excel 2016, it is fairly easy to add a form control to a worksheet.
- The first step is to display the Developer Ribbon and then display the options under the Insert button.
- Under the heading Form Controls, you will see a panel of control icons like the ones described above. If you single click on one of these icons, your mouse pointer will turn into a cross as you move the mouse over the grid area of the worksheet.
- To add a control, choose it by clicking on it, and then drag the mouse pointer to draw out the control on the worksheet.
- To add a list box, for example, you would perform a single left click on the list box button under the Form controls heading, and then move your mouse pointer to the location of your choice. When you find the right spot, click on it, and a list box control will be placed at your chosen location.
- You can resize the list box by mouse dragging the small circles positioned around its border. If you click on an empty cell, the circles will disappear and the list box will be embedded in the worksheet.
- When you right click on a form control, it can be removed (cut), copied, grouped, resized, relocated, or have its Format Controls modified.
- If you right click on the list box and then click the Format Control option on the pop-up menu, a Format Control dialogue box will appear.
- Under the Size tab, you will find options to modify the size of the control. Under the Protection tab, you can choose to lock or unlock the control (used with protected worksheets).
- Under the Properties tab, you can modify how the control object is positioned when cells are moved or resized. You can also specify whether or not you want the control to be printed with the worksheet.
- The Alt Text tab has options for displaying an alternative text for the control.
- Finally, under the Control tab, you will find options for specifying the input range, (a specific cell range containing the data that will appear in the list), and the cell link, which is the cell that will receive an index value corresponding to the item chosen from the list. When you make a selection from a list box, Excel will place the index value into the specified cell link.
To select an input range, put your cursor in the input range data field and use your mouse to select the range of data to be displayed in the list box.
- To select the cell link, use the Tab key or click your mouse to place your cursor in the Cell Link field.
- Next, use your mouse to select the cell that the list box output will be placed in. You can also type the cell references for the input range and the cell link directly if you wish. When you are finished setting up the list box, click OK to implement your modifications.
- In this image, you can see that the Input range D3: D9 has been entered as well as the cell link, B8.
- Here you can see that the list box contains the names of the people in cells D3: D9.
- The list of names can be navigated by the arrow buttons on the right side of the list box. You can also see the index number of a selected item from the list in cell B8. Put another way, Julie has been selected from the list box, and the number corresponding to Julie’s order in the original cell range (3) is visible in the linked cell (B8).
- You can just as easily have the source list or linked cell on a separate worksheet from the form control if you wish.
The process described here is basically the same for any of the form controls. The only differences are the control property modifications, and data source and link options which vary from control to control.
Remember, the best way to get familiar with the different form controls is to experiment with them.