Leading the way in Microsoft Office Development

Step 1 - Display the Control Toolbox toolbar

Select (View > Toolbars > Control Toolbox) or alternatively press the "Control Toolbox" icon on the Visual Basic toolbar.

This toolbar like all other toolbars can be docked to any side of the main window.

You can add different controls to your worksheets to make it easier to capture data.

The only controls that can not be used directly on a worksheet are the Multipage, Tab and the RefEdit control.

You can quickly copy a control by holding down the Ctrl key while you drag with the mouse. If you hold down the Shift key as well then the control maintains the same horizontal position as the original control.

For example if you add a command button to your worksheet you must write a corresponding "_click" event.

If you add a list box to your worksheet then you must populate the list box from VBA code.

Step 2 - Drawing a Control

When an ActiveX control is added to a worksheet it is actually embedded and becomes an Object of the worksheet.

Because controls are treated as graphical objects they can be placed anywhere on a worksheet and can be placed over part of a cell or parts of a range of cells.

Click on the control that you want to add to the worksheet.

Adding controls will automatically take you into Design Mode.

Step 3 - Using the Shortcut Menu

You can right mouse click a worksheet control and select "Format Control".

You can use the shortcut menu (Object > Edit) to change the text displayed on the control.

Step 4 - Adding the VBA Code

Any VBA code has to be added to the corresponding worksheet module.

Step 5 - Changing the Properties

All the properties for the controls can be changed using the Visual Basic Editor "Properties" window.

A lot of the properties are changed using the Properties window although some of the formatting options are the same as those that apply to shapes.

Right click on the control and select "View Properties". This is exactly the same window that appears in the Visual Basic Editor.

You will find that the majority of the properties available for these controls are the same, although some are not available and a few even have different names.

These differences are summarised below:

Step 6 - Selecting Controls

You can easily select a group of controls by using the select objects command on the Drawing toolbar.

Changing the size and the alignment of your worksheet controls can be a bit tricky.

It is possible to use the Align and Distribute commands on the Drawing toolbar.

The quickest way to change the size of the contents is to use the Height and Width properties in the Properties window.

Step 7 - Things to Remember

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 ??