Working with Macros and Forms in Microsoft Excel 2010

8/12/2011

Contents

4.1 Create and Manipulate Macros
4.2 Insert and Manipulate Form Controls

4.2 Insert and Manipulate Form Controls

One way in which you can collect and present data in Excel is through the use of form controls you add to a worksheet. Controls such as list boxes, check boxes, and command buttons help structure a worksheet and manage its data, and they allow you and other users to work with data and objects on a worksheet in specific ways. The sections that follow describe how to add controls, define their behavior and values, and set properties for the controls.

Inserting Form Controls

Excel displays two groups of controls when you click Insert in the Controls group on the Developer tab—form controls and ActiveX controls. You can set up a form control, such as a list box, by using data on a worksheet. ActiveX controls are often handled programmatically by using Visual Basic for Applications, although you can set properties for ActiveX controls and use them without writing any code. The focus in this section is on form controls. You’ll learn more about ActiveX controls in the section “Making Use of ActiveX Controls” later in this chapter.

Excel provides nine types of form controls:

Command button Use a command button to run a macro when a user clicks the button.
Combo box A combo box creates a drop-down list that lets users select an item from a set of items you define or enter an item in the list.
Check box Use a check box to let users make selections from a series of options. A user selects the check box to indicate yes (or true). A user clears a check box to indicate no (or false). You can place a set of check boxes in a group box to present them as a single element. Users can select more than one check box in a group to indicate a range of choices. (For example, on an order form, users could select each of the product categories they want to review.)
Spin button Add a spin button to increase or decrease the value (within a minimum and maximum value that you define) in a specific cell. When you click the up or down arrow on the spin button, the value in the cell that the button is linked to changes, and you can see how these changes affect the results of formulas, for example.
List box Use a list box to display a set of items from which users can choose (a set of nine colors, for example). You can define a list box so that users can select only a single item, multiple items that are adjacent, or multiple items that are not adjacent.
Option button An option button marks one in a set of exclusive options. For example, Update Manually or Update Automatically. As you do with check boxes, you often present option buttons within a group box.
Group box A group box organizes a set of controls into a single element. You usually use a group box with check boxes or option buttons.
Label Use a label to identify or describe the function of a cell, cell range, or another control. You can use a label as you would a caption.
Scroll bar The scroll bar control is similar to the spin button. As you change the position of the scroll bar, you change a value in the cell the scroll bar is linked to.

To add any of these controls to a worksheet, click Insert in the Controls group, click the icon for the type of control you want to add (ScreenTips identify each control), and then click the cell on the worksheet where you want to place the control. Your next step is usually to right-click the control and choose Format Control. You use the Format Control dialog box to specify a cell range for a list, for example, or to specify whether a check box is selected by default, or to set up the maximum and minimum values for a spin button or a scroll bar. In the sections that follow, you’ll learn more details about setting up each type of control.

Inserting a Command Button

Form control command buttons are designed to run macros. When you add a command button to a worksheet, Excel displays the Assign Macro dialog box and assigns a default name to the macro it will associate with the button. To assign a macro that you already recorded or developed in VBA, use the Macros In list to specify the list of macros you want to choose from (All Open Workbooks or Personal.xslb, for example). Then select the macro and click OK. (If you want to make changes to a macro after you select it, click Edit.)

To work with a new macro, click New to write the macro in the Visual Basic editor or click Record to open the Record Macro dialog box, where you can name and describe the macro and then record it.

To change the label on a command button, click Design Mode in the Controls group, right-click the control, and choose Edit Text. The cursor is positioned at the start of the default text (something like Button 1). Select that text, delete it, and then type a label that describes the button’s operation.

Setting Up a Combo Box or a List Box

You define the items that appear in a combo box or list box control by entering them in a cell range. You can list the items on the same worksheet or on a different worksheet in the workbook. (The source of the items can come from a cell range in a separate workbook, but that workbook must be open to populate the list in the control.)

Click the combo box icon in the group of form controls, and then drag across the worksheet to position the combo box and set its initial size. You can resize the control by dragging one of the handles on its borders and at the corners. To reposition the control, point to the border (you’ll see the cursor appear as a four-way arrow) and then drag it to a new location.

Right-click the control and choose Format Control to open the dialog box in which you specify properties for the control. On the Control tab of the Format Control dialog box, click in the Input Range box and then specify the cell range for the list. You can type the range or drag through it on the worksheet where the list appears.

In the Cell Link box, specify a cell (or a cell range) in which you want to capture the value that reflects which item a user selects in the list. The items are numbered sequentially. You might use this value in a formula, for example, or to control other aspects of the form.

You follow the same steps for defining an input range and a linked cell for a list box. On the Controls tab for a list box, you also need to specify an option in the Selection Type area. The options in this area are Single, Multi, and Extended. Use Single if you want a user to select only one item from the list. The Multi option enables users to select multiple adjacent items, and Extended lets users select items that are not adjacent.

Inserting Check Boxes and Option Buttons

After you insert a check box or an option button control (or a set of these controls), right-click the control and choose Format Control. On the Controls tab for these controls, use the Value area to specify whether the control should be selected or deselected as its default state. A Mixed value applies to multiple selections. Also specify the linked cell. When a check box is selected, the linked cell displays the value True. You can use this condition to affect the results of formulas. For example, you could refer to the linked cell in an IF statement that inserts a value in the cell with the IF formula depending on the state of the check box.

When you add a set of options buttons to a group control, right-click one of the option buttons and then specify the linked cell in the Cell Link box on the Control tab. You need to set the linked cell only once. Each option button enclosed in the group control uses the same linked cell.

To change the label for a check box, option button, or group control, right-click the control (be sure you have Design Mode enabled) and choose Edit Text. The cursor appears at the start of the default text. Select that text, delete it, and then type a label that identifies the control.

Using Spin Buttons and Scroll Bars

Add a spin button or a scroll bar to a worksheet when you need a control with which you can increase or decrease the value (within a range that you define) in a specific cell. On the Control tab for a spin button, you specify the current value (which you can leave blank or at the default value 0), the minimum and maximum value, and the interval for incremental changes. You might specify a range of 1,000 to 10,000 with incremental changes of 500, for example. You also need to specify the cell (in the Cell Link box) where the value set by the control appears. Here again, you can use this value in calculations as you need to. For example, if the value in the linked cell equals or exceeds a certain amount, you can use an IF statement to set the value in another cell accordingly.

A scroll bar control also adjusts the value in a cell as you move the scroll bar up or down. You can use a scroll bar to continuously change the value of the linked cell as you scroll. On the Controls tab, set the values for the current value, the minimum and maximum values, and the value for incremental change. The Page Change field affects the speed at which the scroll bar changes values. Scroll up to move toward the minimum value; scroll down to move incrementally toward the maximum value for the control.

Adding a Label

Use a label control to identify controls (or other items on a worksheet) that don’t have labels of their own. You probably won’t add a label for a command button or a check box, for example, but you might use a label for a combo box or a list box. To enter the text for a label, right-click the control and choose Edit Text.

CREATING A DATA FORM

Excel can create a data form from the column headings in a table or a cell range. A data form is especially useful for entering and updating information in a worksheet in which you frequently scroll to enter data in many columns. A data form presents the columns in a single dialog box that also includes command buttons that let you delete a row or add a new row.

The first step in creating a data form is to add the Forms button to the Quick Access Toolbar. Click the arrow at the right end of the Quick Access Toolbar, click More Commands, and then choose All Commands from the Choose Commands From list. Scroll down to the Forms button, select it, click Add, and then click OK. Open the workbook in which you want to create a data form (which should be organized with column headings, of course), and then click the Forms button. Excel creates the dialog box, which you can then use to work with the data in the workbook.

To insert a command button
1. On the Developer tab, click Insert in the Controls group.
2. In the Form Controls group, click the command button icon, and then click on the worksheet where you want to place the button.
  
  Excel displays the Assign Macro dialog box.
3. In the dialog box, type a name for the button’s macro (or use the default name Excel provides.)
4. Select the macro you want the button to run, or click New to write a macro in the Visual Basic editor, or click Record to record a new macro.
To insert a combo box or list box control
1. On the Developer tab, click Insert in the Controls group.
2. In the Form Controls group, click the combo box or list box icon, and then click on the worksheet where you want to place the control.
3. Right-click the control and choose Format Control.
4. On the Control tab of the Format Control dialog box, specify the input range and the linked cell for the control.
5. For a list box, choose the selection type (Single, Multi, or Extended).
To insert a check box or option button control
1. On the Developer tab, click Insert in the Controls group.
2. In the Form Controls group, click the check box or option button icon, and then click on the worksheet where you want to place the control.
3. Right-click the control and choose Format Control.
4. On the Control tab of the Format Control dialog box, use the Value area to specify whether the control should be selected or deselected by default.
5. In the Cell link box, specify the cell that you want to link to the control.
6. To create a group of check boxes or option buttons, click Insert in the Controls group, click the group box icon, and then click on the worksheet where you want to add the group.
7. Drag the check box or option button controls into the group control.
To insert a spin button or scroll bar control
1. On the Developer tab, click Insert in the Controls group.
2. In the Form Controls group, click the spin button or scroll bar icon, and then click on the worksheet where you want to place the control.
3. Right-click the control and choose Format Control.
4. On the Control tab of the Format Control dialog box, enter values in the Current value, Minimum value, Maximum value, and Incremental change boxes. For a scroll bar, also enter a value for the Page change box.
5. In the Cell link box, specify the cell that you want to link to the control.
To insert a label control
1. On the Developer tab, click Insert in the Controls group.
2. In the Form Controls group, click the label icon, and then click on the worksheet where you want to place the control.
3. Right-click the control and choose Edit Text.
4. Delete the default text, and then type the text you want to use.
To change the default label for a command button, option button, or check box
1. Right-click the control and choose Edit Text.
2. Delete the default text, and then type the text you want to use.

Defining Form Control Properties

The Format Controls dialog box contains several other tabs that you work with to manage the size, display, and behavior of form controls:

Size tab On the Size tab, you can adjust the height and width of controls (and the proportional scale). Use the Lock Aspect Ratio option to keep the height and width of the control proportionate.
Protection tab The Protection tab lets you lock a control (and the text contained in some controls), but as information on the Protection tab explains, the options you set on this tab “have no effect unless the sheet is protected.” To protect the worksheet, click Protect Sheet on the Review tab, or click Format on the Home tab and choose Protect Sheet.

SEE ALSO

For more information about applying protection to a worksheet, see “Protecting Worksheets and Workbooks” in Chapter 1 in this section of the book.
Properties tab Options on the Properties tab affect the positioning of a control and whether the control appears when you print the worksheet. The positioning options are Move And Size With Cells, Move But Don’t Size With Cells, and Don’t Move Or Size With Cells. These settings affect the behavior of the control when you resize or move cells that appear behind them.

The default settings for the options on the Properties tab depend on the type of control. For example, the Print Object option is not selected for a command button, but this option is set by default for a check box. The Move And Size With Cells option is enabled for a command button, but this option is not available for many other types of controls.
Alt Text tab The Alt Text tab lets you provide text that is displayed while a form is loading in a web browser. Alt text is also used in search engines and can provide people with disabilities with information about a control.

Some types of controls include other tabs. For example, the Format Control dialog box for check boxes and option buttons includes the Colors And Lines tab. Use this tab to specify a fill color and degree of transparency, as well as a line color, style, and weight. (The options for arrow styles are not available.)

You can apply additional formatting to a command button control by using the Font, Alignment, and Margins tabs in the Format Control dialog box. The settings on the Margins tab affect the positioning of the button’s label.

To set properties for form controls
1. Right-click the control and choose Format Control.
2. Use the Size, Protection, Properties, and Alt Text tabs in the Format Control dialog box to specify options for the control’s behavior.
3. For a command button control, use the Font, Alignment, and Margins tabs to apply additional formatting to the button and its label.
4. For check boxes and option button controls, use the Colors and Lines tab to apply line styles and fill colors.

Making Use of ActiveX Controls

The types of basic ActiveX controls you can insert on a worksheet form are similar to the types of form controls. You can insert a command button, a combo box, a list box, a check box—all the same types of controls except for a group box. You can also insert an ActiveX text box control, an image control, and a toggle button control.

In a text box, you can type, edit, or display data that is linked to a cell.
Use a toggle button control to signify Yes/No or On/Off. A toggle button changes its appearance when it is enabled and when it is not.
Use an image control to embed an image on a form.

When you click More Controls in the ActiveX Controls group, Excel displays a dialog box that lists numerous ActiveX controls that are installed with Excel. These are generally advanced controls that require programming.

To insert an ActiveX control, click the icon for the control in the ActiveX Controls group (click Insert in the Controls group to display the menu of controls), and then click the spot where you want to place the control on the worksheet. The steps you take at this point depend on the type of control you are inserting.

When you add an ActiveX command button control, for example, you won’t see the Assign Macro dialog box. To provide the code you want the button to execute, right-click the button and choose View Code to open the Visual Basic editor. In the Code window, you will see the opening and closing lines of a subprocedure for the button’s Click event. If you aren’t familiar with VBA, you can enter the name of a macro you recorded here or copy and paste the code for a macro you recorded.

You can set properties for a command button (such as BackColor, Locked, Height, Width, and others) and for other controls by clicking Design Mode, right-clicking the button, and then choosing Properties. (You can also click Properties in the Controls group.) The Properties pane that’s displayed lets you view a control’s properties alphabetically or by category.

Some properties are the same as for form controls. For example, ActiveX controls have a LinkedCell property that serves the same purpose as the Linked Cell box on the Format Control dialog box you use with form controls. The Placement property is comparable to the options that control moving and sizing a control that you can select on the Properties tab of the Format Controls dialog box.

You can use the Font property for an ActiveX list box control, for example, to change the font and font formatting applied to the list items. Other properties you can set for a list box include BorderColor, BorderStyle, TextAlign, and Visible (which lets you hide the control if you want to). Other types of controls have the same or similar properties.

Two important properties to keep in mind are the Name property and the Caption property. The Name property identifies the control (such as CommandButton1). The Caption property, which does not apply to all control types, sets the text displayed in a control. For a check box or a command button, for example, type the text you want the control to display in the Caption property.

Here are some of the other important properties you work with in setting up different types of ActiveX controls:

Check box Set the Value property to True to select the check box; set this property to False if you want the check box to be cleared. Among the formatting properties for a check box are BackColor, Shadow, SpecialEffect, and TextAlign.
Combo box Use the ListFillRange property to specify the cell range that includes the items you want the combo box to display. (You need to type the cell range here; you can’t drag through the worksheet as you can for a form control.) To set up a two-column combo box, specify 2 for the ColumnCount property, set the ColumnHeads property to True, and set the ColumnWidths property so that each column is displayed at the size you want it. The BoundColumn property controls which column (in a multicolumn combo box) provides a value. The ListRows property sets the maximum number of rows to display.
List box As you do for a combo box, use the ListFillRange property to specify the input range. The MultiSelect property controls whether users can select one or more items in the list box. Use fmMultiSelectSingle for selecting a single item. Use fmMultiSelectMulti to allow selection of multiple adjacent items. The fmMultiSelectExtended option is the value to choose if you want users to be able to select multiple nonadjacent items.
Option button Set the Value property to True or False to specify whether the option button is selected by default.
Scroll bar Use the Max and Min properties to set the range of values controlled by the scroll bar. The SmallChange property specifies the increment in which the value changes when you click the up or down arrow. The LargeChange property specifies the increment of change when you click the area of the scroll bar between the arrows.
Spin button Use the Max, Min, and SmallChange properties to specify the maximum value, minimum value, and increment of change. Here, as with other controls, the LinkedCell property specifies the cell in which the value associated with the spin button appears.

Security Settings for ActiveX Controls

As it does for macros, the Trust Center provides a page in which you can review and change security settings for ActiveX controls. With the default setting, Excel notifies you that a workbook contains active content. You need to click Enable Content in the message bar to let ActiveX controls run. You can also choose an option to disable all ActiveX controls or to enable all controls without restrictions (this option is not recommended because some ActiveX controls could run code that harms your computer). Or you can choose an option to be prompted to enable controls that are marked by a developer as Safe for Initialization (SFI). If a control is not marked, it is not considered safe (making it a UFI—Unsafe for Initialization—control) and is not enabled.

To change these settings, click Options on the File menu. In the Excel Options dialog box, click Trust Center. Click Trust Center Settings, and then click the ActiveX Settings page.

To insert and set properties for an ActiveX control
1. On the Developer tab, click Insert in the Controls group.
2. In the ActiveX Controls group, click the icon for the control you want to add, and then click on the worksheet where you want to place the control.
3. In the Controls group, click Design Mode.
4. Right-click the control, and then click Properties.
5. Use the properties pane to specify values for the control’s properties.

A Simple Order Form

Combining form controls and ActiveX controls with formulas and functions is what makes forms most useful. For example, here are some steps you could follow to create a simple order form that uses several combo boxes, matching spin buttons, a group of option buttons, and a command button (all form controls), together with the INDEX, IF, and SUM functions.

Add four combo boxes (or some appropriate number of them) to a worksheet. Stack the combo boxes in the same column. You can size them so that they fit in a single row. The input range for these combo boxes will be the same and refer to the cell range where you list your products. (In this example, the list is included in a cell range on a separate worksheet.) Set the Cell Link box in the Format Controls dialog box to point to the cell adjacent to the combo box in the next column (column C here, the product ID).
Add matching spin buttons with which users can specify a quantity. Set the minimum and maximum range and the incremental change to appropriate values (1 to 25 with an increment of 1). Set the Cell Link box for each spin button to the cell adjacent to the control (column E in this example).
In the sample order form, prices for each product are listed on a separate worksheet next to the corresponding product. Products are identified in the linked cell for the combo box by the number of their order in the list. By using the INDEX function and a reference to the linked cell, the product price is entered in column G. The full formula (for row 5) is =(INDEX(Sheet2!B1:B10,C5)). The first argument for the INDEX is the cell range with the prices. The second argument (C5) is the linked cell for the combo box. When a user selects a different product from the combo box, the value in cell C5 (or cells C7, C9, and C11) changes and the INDEX function returns the price for that product.
The value for each entry in the Total column is calculated by multiplying the quantity (column E) with the price in column G. The total in cell I13 is just the sum of the item totals.
The shipping amount is also determined by the INDEX function in conjunction with the group of option buttons that identify shipping options. The amount charged for each option is listed on a separate worksheet. The linked cell for the option button group is F14. When a user selects a shipping option, the value in the linked cell reflects that choice. The formula in cell I14 uses the INDEX function to look up shipping charges: =(INDEX(Sheet2!E1:E3,F14)).
An IF statement in cell I16 determines whether the order is large enough to receive a 10 percent discount. The formula is =IF(I13>=100,I13*-.1,0). If the value of the order (in cell I13) is greater than or equal to 100, a discount of 10 percent of that amount is entered in cell I16. If the order is less than 100, the formula enters 0 and the value is displayed as '-'.
To finish off the order form, the command button runs a macro that saves the form as a PDF file.

PRACTICE TASKS

The practice files for these tasks are located in the folder for Microsoft Excel 2010. You can save the results of these exercises in the same folder. Change the file name so that you don’t overwrite the sample files. When you are done, try performing the following tasks:

Open the file LoanCalculator.xlsx. Sheet1 identifies the cells whose input is required for the PMT function, which you can use to calculate loan payments based on interest rate, loan amount, and number of payments.
Insert form controls to automate the formula by using the named cells as the linked cells for various controls. Add a spin button control that changes the interest rate between 3 and 9 percent. (You need to divide the value by 100 to insert a percentage in the formula.) Add a scroll bar that changes the amount of a loan between $5,000 and $30,000 in $1,000 increments.
Create a list for the possible number of payments (12 through 60). In the adjoining column, number these options 1 through 5. Add a list box control to your form, and use the cell range that contains the list of payment options as the input range. Specify the Number of Payments cell as the linked cell.
Now use the LOOKUP function to equate the output from the list box with the number of payments. The syntax for the LOOKUP function is =LOOKUP (lookup_value,lookup_vector,[result_vector]). Here, you look up the value in the Number of Payments cell, compare it to the numbered list 1–5, and return the matching result from the list of payment options to the PMT formula.