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.
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.
Drawing a Control
When you draw or add a control you are automatically placed in Design Mode.
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.
Selecting a Control
You can easily select a group of controls by using the select objects command on the Drawing toolbar.
Use a left mouse click
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.
Positioning a Control
If you hold down the Shift key as well then the control maintains the same horizontal position as the original control.
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.
Changing the Font
Courier New Bold - 14
Fonts - Century Schoolbook, Eras Medium ITC, Franklin Gothic Book Gill Sand MT, Goudy Old Style, Lucida Bright.
Adding the VBA Code
Any VBA code has to be added to the corresponding worksheet module.
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:
|Control||Worksheet Property||Userform Property|