Other Form Controls in Excel Worksheets

In our previous post, we looked at connecting VBA code to command buttons on the Worksheet, the Quick Access Toolbar and the Ribbon.  What about the other Form Controls: The Combo Box, List Box, or Check Box?  How about Option Buttons, the Scroll Bar, the Spin button, the Label, etc.?  That’s coming up in this post.

But first, the distinction between Form Controls and ActiveX Controls:

1.        Form Controls vs. ActiveX Controls

You can add two types of controls to a worksheet: Form Controls and ActiveX Controls.

1.1      The Form Controls

The Form controls are compatible with earlier versions of Excel, from Excel version 5.0.

Use a Form control when you want to record or use existing macros but do not want to write or change VBA macro code. You can also use Form controls on chart sheets.

These controls are easier to use and are ideal if you want to feed a value through to the worksheet.

1.2      The ActiveX Controls for Macros and Web Scripts

When you need to add a control, use an ActiveX Control for most situations.  Use it especially when you need to control different events that occur when you use the control. For example, you can add a list box control to a worksheet.  Then you can write a macro to take a different action depending on which choice a user selects from the list.

ActiveX controls are the controls in programming languages like stand-alone Visual Basic.  They are the same controls you can add to custom forms you create by using the Visual Basic Editor (see a later post).

Use ActiveX controls when you want users to interact with Excel data that you publish to a Web page. You cannot use ActiveX controls on chart sheets.

These controls are more versatile than the Forms controls.  You can pick up more information from them and use more events.

They also make your worksheets a bit bigger. They use a little more memory than the equivalent Form controls.

2.        Other Form Controls

These are on the Developer Ribbon, in the Controls group.  Pull down the Insert dropdown menu, and look on the Form Controls list.  Most controls have properties that you can change – see below.

• Button                       A Command button to run a macro.
• Combo Box               A drop-down list box.
• Check Box                 A tick box to turn an option on or off.
• Spinner                     A button with up and down arrows to change the value in a cell.
• List Box                     A box that contains a list of items.
• Option Button          A button to select one of the options in a group box.
• Group Box                 A box to group related controls, e.g. option buttons.
• Scroll Bar                   A control that scrolls through a range of values.
• Label                         Static Text to provide information.
• Edit Combo List                       Edit a Combo Box with its attached open List Box.
• Edit Combo Dropdown            Edit a Combo Box that drops down a List Box.

To create a control, click on the button indicated above, then drag to draw the control on the worksheet.

To change a control’s properties, right-click it and choose Format Control.  Then choose the Control tab. Properties you may wish to change include:

• Cell link: A cell that gets the value returned by the control.  For a check box, the value is TRUE, FALSE, or #N/A.  For a group of option buttons, the value is the number of the selected option button in the group. (Use the pop-up menu to group the selected option buttons.)  For a list or combo box, it is the number of the selected item in the list.
• Input range: A Range on a worksheet. It provides the values for a list or combo box.
• Other controls may have other properties, such as the following for a Scroll Bar:

Page Change is the jump you get when you click on the bar itself rather than an arrow button.

3.        Attaching VB Code to Form Controls

Assigning a macro to any Forms Control works the same way as for a Command Button.  Right-click on the control and, from the pop-up menu, choose Assign Macro.

There, you can choose an existing macro and click OK.  Otherwise, you can click New to create one, and then write the code.  Or you can click Record, and record your new macro.  In both cases, the macro will go into a new Module.

So much for Form Controls.  Our next post will deal with ActiveX Controls.