If you add a form control to a worksheet and right click on it, the menu that appears will often have an Assign Macro option, (the label control and the group box control do not have a macro option available on the menu).

 

As an example, look at the worksheet shown below. A button control has been added to the worksheet, and right clicking on the button control displays the menu shown.

 

 

 

The text on the button has already been edited to say Highlight list. You can change the text that appears on a button control by choosing Edit Text from the menu. If you select the format control option from the menu, you can change the font type and size of the button’s text.

 

By selecting the Assign Macro option, you will invoke 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 on the Assign Macro dialogue box. Clicking record will display the record macro dialogue discussed in lesson 3.1.

 

 

 

In the record macro dialogue, you can give your macro a name, a short cut key, specify where it will be stored, and supply a description for it.

 

When you click OK, the macro will begin recording. For this example, the cell range (E6:E15) containing the animal names is selected from the worksheet, and the background color for the selected range is shaded yellow. Choosing Tools->Macro->Stop Recording from the menu bar, will stop the macro from recording.

 

The new macro will be activated when the Highlight list button control is clicked.

 

 

Here is the list before clicking the Highlight list button control.

 

 

 

 

 

 

 

Here is the list after clicking the Highlight list 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.