Excel 2019 Bible

Chapter 45: Using UserForm Controls in a Worksheet

45

Property Description

Width and Height Values that determine the control’s size.

Visible If False, the control is hidden.

Name The name of the control. When you add a control, Excel assigns it a

name based on the control type. You can change the name to any valid

name. However, each control’s name must be unique on the worksheet.

Picture Enables you to specify a graphics image to display.

Linking controls to cells

Often, you can use ActiveX controls in a worksheet without using macros. Many controls

have a LinkedCell property, which specifies a worksheet cell that is linked to the control.

For example, you may add a SpinButton control and specify cell B1 as its LinkedCell

property. After doing so, cell B1 contains the value of the SpinButton, and clicking the

SpinButton changes the value in cell B1. You can, of course, use the value contained in

the linked cell in your formulas.

When specifying the LinkedCell property in the Properties window, you can’t “point” to the linked cell in the
worksheet. You must type the cell address or its name (if it has one).

Creating macros for controls

To create a macro for a control, you must use the Visual Basic Editor (VBE). The macros

are stored in the code module for the sheet that contains the control. For example, if you

place an ActiveX control on Sheet2, the VBA code for that control is stored in the Sheet2

code module. Each control can have a macro to handle any of its events. For example, a

CommandButton control can have a macro for its Click event, its DblClick event, and

various other events.

The easiest way to access the code module for a control is to double-click the control while in Design mode. Excel
displays the VBE and creates an empty procedure for the control’s default event. For example, the default event
for a CheckBox control is the Click event. Figure 45.4 shows the autogenerated code for a control named
CheckBox1, located on Sheet1.