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.
|Command Button||Perform a specific action|
|Combo Box||Select an item from a drop-down list|
|Check Box||Either select or deselect an option|
|List Box||Select from a list of possible choices|
|Text Box||Allow the user to enter information|
|Scroll Bar||Drag a button in order to select a value|
|Spin Button||Select a value by increasing and decreasing|
|Option Button||Select from a list of possible choices|
|Label||Display text or information|
|Image||Display a graphic or picture|
|Toggle Button||Switch between two states|
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.
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|
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext