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")

0 0

Post a comment