Microsoft Office Tutorials and References
In Depth Information
14.7.1 Referring to a Control on a Worksheet
ActiveSheet.OLEObjects("MyButton")
14.7.1 Referring to a Control on a Worksheet
Fortunately, Excel lets us refer to an ActiveX control on a worksheet by using its name, without
reference to the OLEObjects collection. For instance, if we place a command button on a
worksheet, Excel will give it the default name CommandButton1. Both of the following lines set
the height of this command button to 20 points:
ActiveSheet.OLEObjects("CommandButton1").Height = 20
ActiveSheet.CommandButton1.Height = 20
Unfortunately, however, the properties and methods that we access in this manner are the
properties and methods of the OLEObject, not the control itself. These properties are shown in
Table 14-4 .
Table 14-4. M embers of the OLEObject object
AltHTML
Enabled
PrintObject
Activate
Height
ProgId
Application
Index
Select
AutoLoad
Interior
SendToBack
AutoUpdate
Left
Shadow
Border
LinkedCell
ShapeRange
BottomRightCell
ListFillRange
SourceName
BringToFront
Locked
Top
Copy
Name
TopLeftCell
CopyPicture
Object
Update
Creator
OLEType
Verb
Cut
OnAction
Visible
Delete
Parent
Width
Duplicate
Placement
ZOrder
Thus, for instance, while we can set the Height property of the command button, we cannot set its
Caption property in this way. That is, the code:
ActiveSheet.OLEObjects("CommandButton1").Caption = "ClickMe"
will generate an error.
The way to reach the members of the control itself is to use the Object property of an OLEObject
object, which returns the underlying control, and makes its properties and methods accessible.
Thus, the following two lines each set the button's caption:
ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "ClickMe"
ActiveSheet.CommandButton1.Object.Caption = "ClickMe"
In addition to the standard properties available for ActiveX controls, the following properties can
be used with ActiveX controls embedded in sheets in Microsoft Excel:
BottomRightCell
 
Search JabSto ::




Custom Search