Leading the way in Microsoft Office Development

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.

Selecting Controls

You can select multiple controls by holding down the Shift key or by dragging the mouse over the 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.

Things to Remember

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).