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



© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext