ActiveX Controls

You can add ActiveX 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.

microsoft excel docs
Command ButtonPerform a specific action
Combo BoxSelect an item from a drop-down list
Check BoxEither select or deselect an option
List BoxSelect from a list of possible choices
Text BoxAllow the user to enter information
Scroll BarDrag a button in order to select a value
Spin ButtonSelect a value by increasing and decreasing
Option ButtonSelect from a list of possible choices
LabelDisplay text or information
ImageDisplay a graphic or picture
Toggle ButtonSwitch between two states
More ControlsLets you register and add additional controls

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

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.

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.

microsoft excel docs

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:

ControlWorksheet PropertyUserform Property

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext