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, Custom Forms 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.

Using Worksheet Controls

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.
A worksheet control is basically a graphical object that enables the user to enter information, perform an action or select a value.

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 recalculate 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.
4) There are some known problems especially around the resizing of buttons.

Data Validation and Filtering

Do not get confused with the drop-down list boxes that appear as part of the Data Validation or Tables Filtering Tables features.
These are not examples of worksheet controls.

Using Worksheet Controls

If a macro is worksheet specific and will only be used on that particular worksheet then it is appropriate to use a button that has been embedded on that worksheet.
If you want to be able to run a macro from any location in any worksheet or workbook then it would be more appropriate to attach this to a button on a toolbar or ribbon tab.
The most common types of worksheet controls are command buttons, option buttons and check boxes.

You can add Form controls and/or ActiveX controls.

Form Controls or ActiveX Controls ?

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

The Form controls is really only available for backwards compatibility and technically should not be used for any new worksheets.
However having said that though, they are still preferred by a lot of users as they provide certain functionality that is not possible using the ActiveX controls.
One big advantage is you can hook up a recorded macro to a button without actually writing any code or using the Visual Basic Editor.
It is also possible to link these controls directly with cells on the worksheet.
They are also frequently used when you want to display a simple drop-down list.

These controls are more sophisticated and do require you to use the Visual Basic Editor.
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.
The ActiveX controls also contains a few types of controls that are not available as Form controls, such as toggle buttons and image controls.
You must exit Design View before you can use any of these controls.

Formatting Worksheet Controls

There are various ways to change the properties and formatting of worksheet controls.
1) Using the Properties window (only available for the Control Toolbox Controls).
2) Using the (Format > Control) dialog box.
The (Format > Control) dialog box is applicable to any object that is placed on a worksheet in the drawing layer.
This included graphics, autoshapes and wordart.
The most useful tab is the "Properties" tab as this determines if the object is moved and resized when column widths and row heights are adjusted.

You cannot use the same method as you would use in the Visual Basic Editor.
You have to use the Align and Distribute buttons on the drawing toolbar since all worksheet controls as considered as graphical objects.
You can make several controls the same height or width by using the Ctrl and Shift buttons and then changing the height or width properties in the Properties window.

Size tab

Height - Enter the height you want for the selected object. The Height and Width settings are always the dimensions of an un-rotated object. Alternatively, you can adjust the height as a percentage of the original size by entering a percentage in the Height box under Scale. If the Lock aspect ratio check box is selected, the Height and Width settings change in relation to one another.
Width - Enter the width you want for the selected object. The Height and Width settings are always the dimensions of an un-rotated object. Alternatively, you can adjust the width as a percentage of the original size by entering a percentage in the Width box under Scale. If the Lock aspect ratio check box is selected, the Height and Width settings change in relation to one another.
Lock aspect ratio - If checked, the Height and Width settings change in relation to one another.
Relative to original picture size - Calculates the height and width percentages under Scale, based on the original size of the picture. This option is available only when the selected object is a picture. In PowerPoint, if you select this check box, click the resolution of your slide show monitor in the Resolution box.

Protection tab

Worksheet controls are very badly protected against accidental changes.
Locking any controls will not have any effect unless the worksheet is protected.
It is always worth clearing this box for all the controls and then protecting the whole worksheet when you have finished.

Properties tab

These are very important as they will determine how your control behaves when changes to the worksheet are made, i.e. the widths of rows and columns are adjusted.
Move and size with cells - For a graphic object on a chart, ensures that the object will move with and resize with the chart. For a graphic object on a worksheet, ensures that the object will move with and resize with its underlying cells. Use this option if you want to sort graphic objects with the underlying cells. The objects can be no taller than the row or no wider than the column you want to sort.
Move but don't size with cells - Ensures that a graphic object will move, but not resize, with its underlying cells.
Don't move or size with cells - Maintains the position and size of a graphic object on your worksheet.
Print object - Prints selected graphic objects when you print a worksheet. All objects will be printed by default.

Additional Controls

Select from the list and then drag to the sheet ?

CommandButton - TakeFocusOnClick Property

Controls by default always maintain the input focus when they are clicked or selected.
This is fine when you are using a userform or dialog box but this can cause a lot problems when a control is placed on a worksheet.
Quite often causing perfectly good VBA code to generate a run-time error.
When you add a control to a worksheet (from the Control Toolbox toolbar) make sure you change the property to False.

When this property is set to False, the focus remains exactly where it was before the button got pressed.
If you are using any other type of control then you may need to activate the worksheet (or range manually) as soon as the control is activated.

Control Toolbox - Unable to View Properties

When your workbook is password protected you are unable to view the control properties.
Selecting a worksheet control and selecting Properties from the shortcut menu will display a blank properties dialog box.

The VBA Project associated with this workbook has to be unlocked with the password before these properties will be displayed.

Chart Controls

At the moment the only types of controls that can be placed on Charts are Form Controls.

Forms Toolbar - Formatting Text

Is it possible to change the font of a control placed from the Forms toolbar.
The control will always use the default font from the (Tools > Options)(General tab).
You can change the font here, close and re-open Excel, add the control to the worksheet and then change the font back to Arial.
Remembering to close and reopen Excel for the font to be permanent.

Important

If you are populating a list box on a worksheet, then you must have screenupdating switched on otherwise the list box will appear empty.
Try to avoid linking a large number of controls to your worksheet as this will slow down your workbook.
Every time a control value changes and a cell value changes, the worksheet / workbook is recalculated (manual or automatic).
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.
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 more frequently. For this reason I would try and avoid using them if possible.
The Forms toolbar is only for backwards compatibility and should not really be used. You should use the Control Toolbox toolbar instead.
To select multiple controls you can use the Shift and Ctrl keys.
You can un-select a control by pressing the ESC key.
When a worksheet control is selected you can press the shortcut key (Ctrl + 1) to display the (Format > Control) dialog box.
You can remove a control at any time by selecting it with the Right mouse button and pressing the DEL key.
You must be in design mode if you want to add, resize or change any of the worksheet controls.
Can you add tool tip texts to your worksheet controls ??
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 ??
All worksheet controls can be moved and sized independently of row and column restrictions.
You can link one cell to several controls but a control can only be linked to a single cell.
When a control changes the value in a cell any formulas referring to that cell will be automatically recalculated (manual or automatic ??).