ActiveX Controls on Worksheets

As you may know, ActiveX controls (and standard Excel controls) can be placed directly on a worksheet. Care must be taken, however, not to clutter up a worksheet with controls that would be better placed on a UserForm. When only a small number of controls are required, placing these controls directly on a worksheet may be appropriate.

There are some special considerations when controls are placed directly on a worksheet. In particular, each ActiveX control on a worksheet (not on a UserForm) is represented by an OLEObject in the Excel object model. However, it is important to note that OLEObject objects can also represent embedded OLE objects. Thus, for instance, if we insert a bitmap on a worksheet (select Object from Excel's Insert menu), this bitmap object will be represented by an OLEObject.

The Worksheet object has a property called OLEObjects that returns the OLEObjects collection consisting of all OLEObject objects on the worksheet. Thus, the OLEObjects collection for the active worksheet is:

ActiveSheet.OLEObjects

Because OLEObjects also represent embedded OLE objects (such as bitmaps), we cannot be certain that, say:

ActiveSheet.OLEObjects(1)

is a control. Thus, it is wise when adding a control or embedded OLE object to a worksheet to immediately assign the control or object a name and then refer to it by this name rather than by index, as in:

ActiveSheet.OLEObjects("MyButton")

Referring to a Control on a Worksheet

Fortunately, Excel lets us refer to an ActiveX control on a worksheet by using its name, without reference to the OLEObjects collection. For instance, if we place a command button on a worksheet, Excel will give it the default name CommandButton1. Both of the following lines set the height of this command button to 20 points:

ActiveSheet.OLEObjects("CommandButton1").Height = 20
ActiveSheet.CommandButton1.Height = 20

Unfortunately, however, the properties and methods that we access in this manner are the properties and methods of the OLEObject, not the control itself. These properties are shown in Table 14-4.

Table 14-4. Members of the OLEObject object

AltHTML	Enabled	PrintObject
Activate	Height	ProgId
Application	Index	Select
AutoLoad	Interior	SendToBack
AutoUpdate	Left	Shadow
Border	LinkedCell	ShapeRange
BottomRightCell	ListFillRange	SourceName
BringToFront	Locked	Top
Copy	Name	TopLeftCell
CopyPicture	Object	Update
Creator	OLEType	Verb
Cut	OnAction	Visible
Delete	Parent	Width
Duplicate	Placement	ZOrder

Thus, for instance, while we can set the Height property of the command button, we cannot set its Caption property in this way. That is, the code:

ActiveSheet.OLEObjects("CommandButton1").Caption = "ClickMe"

will generate an error.

The way to reach the members of the control itself is to use the Object property of an OLEObject object, which returns the underlying control, and makes its properties and methods accessible. Thus, the following two lines each set the button's caption:

ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "ClickMe"
ActiveSheet.CommandButton1.Object.Caption = "ClickMe"

In addition to the standard properties available for ActiveX controls, the following properties can be used with ActiveX controls embedded in sheets in Microsoft Excel:

BottomRightCell: Returns a Range object that represents the cell that lies under the lower-right corner of the object.
LinkedCell: Returns or sets the worksheet range that is linked to the value of the control. Thus, if we place a value in the linked cell, the control will assume this value, and vice-versa.
ListFillRange: Returns or sets the worksheet range that is used to fill a list box control.
Placement: Returns or sets the way that the control is attached to the cells below it. The possible values are the XlPlacement constants: xlMoveAndSize, xlMove, and xlFreeFloating.
PrintObject: Prints the control when the worksheet is printed if this property is set to True.
TopLeftCell: Returns a Range object that represents the cell that lies under the top-left corner of the object.
ZOrder: Returns the ZOrder position of the control.

Note also that Table 14-4 has some properties that are not properties of controls themselves. They relate to the OLEObject, which is the container for the control, and thus to the control's relationship with the worksheet. For instance, the code:

ActiveSheet.CommandButton1.TopLeftCell.Address

returns the address of the top-left cell of the worksheet that lies under the control (or rather, the control's container: the OLEObject).

As another example, the following code will locate the top-left cell under the command button and then scroll the active window so that this cell (and therefore the command button) is at the upper-left corner of the window:

Dim rng As Range
Set rng = ActiveSheet.CommandButton1.TopLeftCell
With ActiveWindow
  .ScrollRow = rng.Row
  .ScrollColumn = rng.Column
End With

It is important to note that some properties and methods of some Excel objects are disabled when an ActiveX control has the focus. For example, the Sort method of the Range object cannot be used when a control is active. Since a control on a worksheet remains active after it is clicked, the following code will fail:

Private Sub CommandButton1_Click
    Range("A:A").Sort Key1:=Range("A:A")
End Sub

(We will discuss the sort method in Chapter 19. Don't worry about that now.) This is one disadvantage of placing controls directly on worksheets.

Of course, one way to avoid this problem is to activate another object before calling the sort method. For instance, we can amend the previous code as follows:

Private Sub CommandButton1_Click
    Range("A:A").Activate
    Range("A:A").Sort Key1:=Range("A:A")
    CommandButton1.Activate        ' Optional
End Sub

It is also worth mentioning that if you save an Excel 97 or Excel 2000 workbook in Excel 5.0/95 Workbook file format, all ActiveX control information will be lost.

Adding a Control to a Worksheet Programmatically

To programmatically add an ActiveX control to a worksheet, we use the Add method of the OLEObjects collection. The syntax is:

                  OLEObjectCollection.Add(ClassType, FileName, Link, DisplayAsIcon, _
  IconFileName, IconIndex, IconLabel, Left, Top, Width, Height)

The ClassType parameter is the so-called programmatic identifier (or ProgID) for the control. Table 14-5 shows the ProgIDs for various controls.

Table 14-5. ProgIDs for ActiveX Controls

Control	ProgID
CheckBox	Forms.CheckBox.1
ComboBox	Forms.ComboBox.1
CommandButton	Forms.CommandButton.1
Frame	Forms.Frame.1
Image	Forms.Image.1
Label	Forms.Label.1
ListBox	Forms.ListBox.1
MultiPage	Forms.MultiPage.1
OptionButton	Forms.OptionButton.1
ScrollBar	Forms.ScrollBar.1
SpinButton	Forms.SpinButton.1
TabStrip	Forms.TabStrip.1
TextBox	Forms.TextBox.1
ToggleButton	Forms.ToggleButton.1

The only other parameters that are relevant to adding ActiveX controls (this method is used for other types of OLE objects as well) are the Left, Top, Width, and Height parameters, which specify in points the location (with respect to the upper-left corner of cell A1) and size of the control. All other parameters should be omitted. (This is a good place for named arguments!)

For instance, the code:

ActiveSheet.OLEObjects.Add ClassType:="Forms.Textbox.1", _
  Left:=72, Top:=72, Height:=20, Width:=100

places a new text box approximately one inch from the top and left edges of the active worksheet. (The dimensions do not seem to be terribly accurate.)