Part VI: Programming Excel with VBA
864
TABLE 42.2Properties Shared by Multiple Controls
Property Description
AutoSize If True, the control resizes itself automatically, based on the text in its caption.
BackColor The background color of the control.
BackStyle The style of the background (either transparent or opaque).
Caption The text that appears on the control.
LinkedCell A worksheet cell that contains the current value of a control.
ListFillRange A worksheet range that contains items displayed in a ListBox or ComboBox control.
Value The control’s value.
Left and Top Values that determine the control’s position.
Width and Height Values that determine the control’s width and height.
Visible If False, the control is hidden.
Name The name of the control. By default, a control’s name is 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 graphic image to display.Linking controls to cells
Often, you can use ActiveX controls in a worksheet without using any 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 prop-
erty. 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.Note
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). n
Creating macros for controls
To create a macro for a control, you must use the Visual Basic Editor (VB Editor). The macros are
stored in the code module for the sheet that contains the control. For example, if you place an