Before you assign a macro, you should make sure that the purpose of the button is clear. You can change the text that appears on a button control by choosing Edit Text from the menu. For this example, the button text is changed to Highlight.
If you select the Format Control option from the pop-up menu, you can change the font type and size of the button’s text.
If you select the Assign Macro option, you will display the Assign Macro dialogue box.
You can choose a macro to assign to the button control by selecting one from the list of available macros and clicking OK. The macro you choose will be activated when you click the button it is assigned to.
You can also record a new macro for the button control by clicking the Record button in the Assign Macro dialogue box. (This button will appear when you type a new macro name.) Clicking Record will display the Record Macro dialogue, discussed in Section 2.
When you click OK, the macro will begin recording. For this example, the cell range D3: D9 containing people’s names is selected from the worksheet, and the background color for the selected range is shaded yellow. Clicking the stop (white square) button in the lower left of the Excel screen will stop the macro from recording. The new macro will be activated when the Highlight button control is clicked.
Here is the original list.
Here is the list after clicking the Highlight button. The button has activated the assigned macro and the list is highlighted in yellow.
You can change the macro assigned to a given form control by right-clicking the control and choosing the Assign Macro option from the menu. When the Assign Macro dialogue box is displayed, select the macro of your choice from the list and click OK. The macro you choose will now be the one that is assigned to the form control.
You can assign macros to other form controls, like lists boxes or combo boxes, but these macros will often require Visual Basic programming to handle and manipulate the values captured by the controls.
Using Form Control
There are many ways to apply form controls in Excel. They can be used to make selections and trigger simple calculations or to provide a basic interface that allows a user to interact with the workbook data.
When using form controls in your workbook, keep in mind that one type of form control may be better suited for a task than another. Here are a few examples to give you a better idea of what you can do with form controls.
This is a group box control, containing two radio button (option button) controls. Radio buttons are used when you want to make a mutually exclusive selection; if you select one radio button, any others will be deselected.
The group box control allows you to have different groups of radio buttons for different purposes. If radio buttons are in a group box, selecting one will only deselect the other radio buttons in the same group box, rather than all other radio buttons.
The image above will give you a better idea of how option buttons work. With the 5-year plan selected, the price is 1000. You can see the value returned from the radio button control in cell F8. Now, look at the formula for the active cell in the formula bar. If the value in F8 = 1 (meaning if the first option button in the grouping been selected), 1000 will be displayed. If the value in F11 is not 1, 10000 will be displayed.
The following example demonstrates the use of checkboxes. In this example, there are three checkbox controls. Checkboxes return a true or false value indicating if they have been checked or not. You can see that the product1 and product3 checkboxes have been selected, and the total is 4000 dollars.
Here, you can see how the checkboxes work. The true and false values from the checkboxes are entered in H1: H3 (the linked cells).
Cells I1: I3 contain IF functions that display the values from column E corresponding to the checkboxes (you can see the IF function for I1 in the formula bar). If the corresponding value in column H is true, the value from column D will be shown in the cell with the IF function. If the value from column H is not true, zero will be shown. Cell E12 sums the values in I1: I3 for a total.
This is an example of a combo box control. This combo box allows you to select a given month from a drop list. When a selection is made, the profit for that month is shown.
This image shows how the combo box works. Notice the VLOOKUP function in the formula bar.
When a month is selected from the combo box, Excel puts the index value of the month in the combo box cell link G1. (The index is 1 because Jan is the first month.) Cells E5: G16 contains a lookup table with profit figures for each month. Cell C8 contains a VLOOKUP formula that looks up the index value from G1 in the lookup table. VLOOKUP then displays the value from column 3 of the look-up table corresponding to the index given from the combo box. In this example, the resulting value is 12000.