Leading the way in Microsoft Office Development

What are Worksheet Controls ?

Most applications use dialog boxes as a means of interacting with the user, whether it is displaying a message or requesting a password.

An alternative to using custom dialog boxes is to actually place the controls directly onto the worksheet.

When controls are placed directly onto a worksheet they are known as Worksheet Controls or Form Controls or ActiveX controls.

A worksheet control is basically a graphical object that enables the user to enter information, perform an action or select a value.

Worksheet controls can be used effectively with databases and pivot tables. It is even possible to embed a worksheet control inside a chart object.

Common uses for Worksheet Controls

1) Providing a button to perform a specific task, such as printing particular cell ranges.

2) Providing a checkbox to allow options to be easily switched on or off.

3) Providing a spinner button to easily change the parameters for a calculation.

Advantages of using Worksheet Controls

1) Controls can be placed anywhere on the worksheet giving you more flexibility.

2) The consequence of changing a worksheet control is immediately visible within the worksheet. The worksheet will automatically recalculated when a value changes.

3) All worksheet controls can be moved and sized independently of the row and column headings.

4) They can be used to help prevent errors by restricting choices to valid options.

Disadvantages of using Worksheet Controls

1) You must select the controls using the mouse, there is no keyboard equivalent.

2) Some control properties will be reset when the workbook is next opened (e.g. items selected in a list box).

3) They are not as robust as using userform controls and can frequently cause Excel to crash.

What are Custom Forms ?

The most common types of worksheet controls are command buttons, option buttons and check boxes.

You can add a wide variety of controls to a worksheet and when this is done they are often referred to as Custom Forms.

A common use might be to add a command button as a way to run a macro or maybe to add a spin button as a convenient way to adjust the value in a cell.

Do not get confused with the drop-down list boxes that appear as part of the AutoFilter or Pick from List features. These are not examples of worksheet controls.

It is possible to add controls from both the Forms toolbar and the Control Toolbox toolbar.

Forms toolbar or Control Toolbox toolbar ?

There are two types of controls that you can add to your worksheets and there are a lot of similarities between the two.

Forms Toolbar

The Forms toolbar is only available for backwards compatibility and should not really be used for any new worksheets.

These controls are however preferred by a lot of users as they do not require you to write any VBA code and all the functionality can be maintained from within Excel.

It is also possible to link these controls directly with cells on the worksheet.

They are commonly used when you want to run a single macro or for displaying a simple drop-down list.

Control Toolbox Toolbar

The Control Toolbox uses ActiveX controls that are not supported in Excel 5.

These controls are much more sophisticated and must be used in conjunction with VBA code. What makes these controls more flexible than the controls on the Forms toolbar is that we can trap their events.

The event could be a simple click, a double click, a change in selection or even just shifting the focus to another control.

You will need to write the corresponding VBA code in order to populate and retrieve the data from these controls.

These controls are much more suited for complex forms and web pages.

The Control Toolbox also contains a few types of controls that are not available on the Forms toolbar such as toggle buttons and image controls.

You must exit Design View before you can use any of these controls.

Things to Remember

Any worksheet controls can also be placed on charts and other objects. They are actually placed in the invisible drawing layer of the worksheet or chart.

These types of controls are not to be confused with drop-down lists that appear as part of Data Validation or Filtering.

Worksheet controls cannot be assigned shortcut keys.

Worksheets that contain sheets with controls on are not suitable for being converted to Add-ins as the fundamental purpose of an add-in is that the sheets are hidden from view.

If you add a control to a workbook which has its VBA project protected then you will not be able to see any of the Properties. This is an indication that you will not be able to add any code behind the button ??

One of the biggest problems with using worksheet controls is that they are not particularly stable and may cause your workbooks to crash a lot more frequently. For this reason I would not recommend using them.