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.
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)
Using the OLEObject Collection
The OLEObject is a container for the controls and therefore corresponds to the control's relationship with the worksheet.
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.
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.AutoUpdate = False
obObject.PrintObject = False
obObject.Object.BackColor = RGB(10,10,10)
obObject.Object.Caption = ""
obObject.Object.TakeFocusOnClick = False
Using the Specific Object Name
This method can only be used to refer to controls that have been added from the Control Toolbox toolbar.
CommandButton1.BackColour = RGB(20,20,20)
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", _
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
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext