ActiveX Controls

Accessing the Controls

There are several ways to access the objects in the Drawing layer:
1) Use the Shapes collection.
2) Use the OLE Objects collection.
3) Refer to the specific name of the object.

Using the Shapes Collection

This method can be used to refer to controls that have been added from the Control Toolbox toolbar.
If you do not specify the type of shape you are referring to you are limited to only the properties and methods for a general Shape object.

ActiveSheet.Shapes("CommandButton1").Select 

If you want to refer to the properties and methods of the control you must use the following:

ActiveSheet.Shapes("CommandButton1").OLEFormat.Object.Object.BackColor = RGB(20,20,20) 

ActiveSheet.Shapes.AddOLEObject 

Using the OLEObject Collection

The OLEObject is a container for the controls and therefore corresponds to the control's relationship with the worksheet.

Worksheets("Sheet1").OLEObjects.Count 
Worksheets("Sheet1").OLEObjects.Delete 

If you do not specify that you are referring to the object you are limited to only the properties and methods for a general OLEObject.

Worksheets("Sheet1").OLEObjects("CommandButton1").Select 

If you want to refer to the properties and methods of the control without declaraing an OLEObject datatype then you must use an additional Object property.

Worksheets("Sheet1").OLEObjects("CommandButton1").Object.Caption = "Run" 

In addition to the standard properties and methods for ActiveX controls the following are available for ActiveX control embedded on worksheets.

Dim obObject As OLEObject 
For Each obObject in ActiveSheet.OLEObjects 
   obObject.OLEType = xlOLELink 
   obObject.Name 
   obObject.AutoUpdate = False 
   obObject.BottomRightCell 
   obObject.LinkedCell 
   obObject.ListFillRange 
   obObject.Placement 
   obObject.PrintObject = False 
   obObject.TopLeftCell 
   obObject.Zorder 

   obObject.Object.BackColor = RGB(10,10,10) 
   obObject.Object.Caption = "" 
   obObject.Object.TakeFocusOnClick = False 
Next obObject 

Using the Specific Object Name

This method can only be used to refer to controls that have been added from the Control Toolbox toolbar.

Worksheets("Sheet1").CommandButton1.Select 
CommandButton1.BackColour = RGB(20,20,20) 

Adding Controls

The ClassType is the so-called "pragrammatic identifier" or ProgID for the control
The ClassType and the size and position are the only parameters that are relevant. All the othes can be ignored.

ActiveSheet.OLEObjects.Add (ClassType:="Forms.Textbox.1", _ 
                            FileName:= 
                            Link:= 
                            DisplayAsIcon:= 
                            IconFileName:= 
                            IconIndex:= 
                            IconLabel:= 
                            Left:=10, _ 
                            Top:=10, _ 
                            Width:=10, _ 
                            Height:=10) 

Handling Events

Control Toolbox controls must be placed in the class module behind the worksheet in which they are embedded.
The procedure name must be the same as the name of the control and the name of the event.
For example - SS