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.CommandButtonl.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.
|
AltHTML |
Enabled |
PrintObject |
|
Activate |
Height |
ProgId |
|
Application |
Index |
Select |
|
AutoLoad |
Interior |
SendToBack |
|
AutoUpdate |
Left |
Shadow |
|
Border |
LinkedCell | |
|
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
Returns a Range object that represents the cell that lies under the lower-right corner of the object.
LinkedCell
Returns or sets the worksheet range that is linked to the value of the control. Thus, if we place a value in the linked cell, the control will assume this value, and vice-versa.
ListFillRange
Returns or sets the worksheet range that is used to fill a list box control.
Placement
Returns or sets the way that the control is attached to the cells below it. The possible values are the XlPlacement constants: xlMoveAndSize, xlMove, and xlFreeFloating.
PrintObject
Prints the control when the worksheet is printed if this property is set to True. TopLeftCell
Returns a Range object that represents the cell that lies under the top-left corner of the object.
ZOrder
Returns the ZOrder position of the control.
Note also that Table 14-4 has some properties that are not properties of controls themselves. They relate to the OLEObject, which is the container for the control, and thus to the control's relationship with the worksheet. For instance, the code:
ActiveSheet.CommandButtonl.TopLeftCell.Address returns the address of the top-left cell of the worksheet that lies under the control (or rather, the control's container: the OLEObject).
As another example, the following code will locate the top-left cell under the command button and then scroll the active window so that this cell (and therefore the command button) is at the upper-left corner of the window:
Dim rng As Range
Set rng = ActiveSheet.CommandButtonl.TopLeftCell With ActiveWindow
.ScrollRow = rng.Row .ScrollColumn = rng.Column End With
It is important to note that some properties and methods of some Excel objects are disabled when an ActiveX control has the focus. For example, the Sort method of the Range object cannot be used when a control is active. Since a control on a worksheet remains active after it is clicked, the following code will fail:
Private Sub CommandButtonl Click
Range("A:A").Sort Key1:=Range("A:A") End Sub
(We will discuss the sort method in Chapter 19. Don't worry about that now.) This is one disadvantage of placing controls directly on worksheets.
Of course, one way to avoid this problem is to activate another object before calling the sort method. For instance, we can amend the previous code as follows:
Private Sub CommandButtonl Click Range("A:A").Activate Range("A:A").Sort Key1:=Range("A:A") CommandButtonl.Activate ' Optional
End Sub
It is also worth mentioning that if you save an Excel 97 or Excel 2000 workbook in Excel 5.0/95 Workbook file format, all ActiveX control information will be lost.
Was this article helpful?
Black Jack Tactics Conquering The Game In Casinos
So, you want to learn how to play Blackjack. Youre in good hands. There is no better ship to chart those learning waters than the one youre riding on right now.
Related Posts
Responses
-
michael9 years ago
- Reply
-
Taziana3 years ago
- Reply
-
Cottar2 years ago
- Reply
-
miranda2 years ago
- Reply
-
lukas1 year ago
- Reply
-
Phillipp1 year ago
- Reply
-
tanja1 year ago
- Reply
-
Frank1 year ago
- Reply
-
annibale1 year ago
- Reply
-
Fulgenzio1 year ago
- Reply
-
zak1 year ago
- Reply
-
reijo1 year ago
- Reply
-
senay1 year ago
- Reply
-
quinto1 year ago
- Reply
-
EDWARD1 year ago
- Reply
-
mantissa1 year ago
- Reply
-
Ponto Zaragamba10 months ago
- Reply
-
marmaduke10 months ago
- Reply
-
itala lorenzo9 months ago
- Reply
-
ovidio9 months ago
- Reply
-
gruffo9 months ago
- Reply
-
pandora9 months ago
- Reply
-
MEBRAHTU8 months ago
- Reply
-
riitta7 months ago
- Reply
-
Ajay6 months ago
- Reply
-
MELBA5 months ago
- Reply
-
Stefania Lucciano5 months ago
- Reply
-
Mehret5 months ago
- Reply
-
christin5 months ago
- Reply
-
Destiny Jones5 months ago
- Reply
-
judy5 months ago
- Reply
-
ABBY4 months ago
- Reply
-
Mafalda3 months ago
- Reply
-
Girmay3 months ago
- Reply
-
selina2 months ago
- Reply
-
DAVID2 months ago
- Reply
-
giuliano2 months ago
- Reply
-
Aloisia2 months ago
- Reply
-
yrj2 months ago
- Reply
-
max2 months ago
- Reply
-
cornelia1 month ago
- Reply
-
rezene1 month ago
- Reply
-
abel elias18 days ago
- Reply
-
niamh13 days ago
- Reply