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.

Table 14-4. Members of the OLEObject object











































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:


Returns a Range object that represents the cell that lies under the lower-right corner of the object.


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 xlFreeFloating.


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.


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?

+20 -7
Black Jack Tactics Conquering The Game In Casinos

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.

Get My Free Ebook


  • Taziana
    How to refer to all activex textboxes on a sheet?
    3 years ago
  • Cottar
    How to reference a control on a worksheet in vba?
    2 years ago
  • miranda
    How to refer to a activex control in vba code?
    2 years ago
  • lukas
    How to refer to form controls excel vba?
    1 year ago
  • Phillipp
    How refer to form control when use in excel sheet vba?
    1 year ago
  • tanja
    How to find the cell that a activex button is in?
    1 year ago
  • Frank
    How to refer to a form control button in excel?
    1 year ago
  • annibale
    How to refer to activex button in vba?
    1 year ago
  • Fulgenzio
    How to reference a form control radial button in vba?
    1 year ago
  • zak
    How to refer to a button on spreadsheet?
    1 year ago
  • reijo
    How do you use vba to change the properties of an OLEObject?
    1 year ago
  • senay
    How to reference buttons in excel vba?
    1 year ago
  • quinto
    How to refer to command button in excel vba?
    1 year ago
    How to call a activex control box on a sheet in vba?
    1 year ago
  • mantissa
    How to get control of excel workbook vba?
    1 year ago
  • Ponto Zaragamba
    How to reference activex control vba?
    10 months ago
  • marmaduke
    How to access an activeX in a different worksheet?
    10 months ago
  • itala lorenzo
    Which reference has oleobjects?
    9 months ago
  • ovidio
    How to controll all the sheets name vba excel?
    9 months ago
  • gruffo
    How to reference button in vba code?
    9 months ago
  • pandora
    How to access control in workbook from vba?
    9 months ago
    How to reference in sheet activex in vba?
    8 months ago
  • riitta
    How to reference a button control in an Excel Worksheet with VBA?
    7 months ago
  • Ajay
    How to access a control in excel from another excel?
    6 months ago
    How to use VBA with control sheets?
    5 months ago
  • Stefania Lucciano
    How to reference command button in vba target, range?
    5 months ago
  • Mehret
    How to refer control buttons Excel VBA?
    5 months ago
  • christin
    How to grab a reference to a form control in excel vba?
    5 months ago
  • Destiny Jones
    How to reference an activex control in excel?
    5 months ago
  • judy
    How to find a form control on worksheet?
    5 months ago
  • ABBY
    How to reference activex button on sheet, vba?
    4 months ago
  • Mafalda
    How to access a sheet using command controls in excel?
    3 months ago
  • Girmay
    What is a controller of an object in vba?
    3 months ago
  • selina
    How to reference an ActiveX option button vba?
    2 months ago
    How do i refer to a macro button excel from vba?
    2 months ago
  • giuliano
    How to reference a drop down control in excel worksheet vba?
    2 months ago
  • Aloisia
    How to reference command button object in excel vba?
    2 months ago
  • yrj
    How to make a control to on another sheet?
    2 months ago
  • max
    How to find a reference to a button in excel vba?
    2 months ago
  • cornelia
    How to get name of a msforms command button?
    1 month ago
  • rezene
    How to refer to a textbox in another worksheet vba?
    1 month ago
  • abel elias
    How to refer to another sheet in activex property?
    18 days ago
  • niamh
    How to refer to a sheet in active x?
    13 days ago

Post a comment