Microsoft Office Tutorials and References
In Depth Information
14.7.1 Referring to a Control on a Worksheet
Returns a Range object that represents the cell that lies under the lower-right corner of the
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.
Returns or sets the worksheet range that is used to fill a list box control.
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
Prints the control when the worksheet is printed if this property is set to True .
Returns a Range object that represents the cell that lies under the top-left corner of the
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:
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
upperleft corner of the window:
Dim rng As Range
Set rng = ActiveSheet.CommandButton1.TopLeftCell
.ScrollRow = rng.Row
.ScrollColumn = rng.Column
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: