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

Fo rms. Frame. 1

Image

Forms.Im age. 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.)

0 0

Post a comment