Activating shape objects

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Since most Shape objects (with the exception of OLEObjects) do not have any associated event procedures, you can use the OnAction property of the Shape object to simulate a Click() event. After the following code is executed, a Shape object named MyRectangle will activate a VBA procedure called LoopThruShapes() when clicked. Technically, this is not the action of a Click() event procedure, but practically it serves the same purpose.

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 100, 100, 50, 50).Select Selection.Name = "MyRectangle"

ActiveSheet.Shapes("MyRectangle").OnAction = "LoopThruShapes"

The OnAction property of the Shape object must be executed before a user's click will activate the specified procedure (LoopThruShapes()). This can be done anywhere in the program, but including it in the procedure that adds the shape used to simulate the Click() event is a good place for the code. Once the OnAction property has assigned a procedure to the Shape object, the connection between the shape and the macro is saved with the workbook and can be viewed from the application by showing the Assign Macro dialog box (right-click on the shape and select Assign Macro) as shown in Figure 10.7.

The LoopThruShapes() sub procedure is listed earlier in this chapter. The result of the LoopThruShapes() sub procedure after application to the Misc Shapes worksheet is shown in Figure 10.8.

Assign Macro

The Assign Macro dialog box.

Assign Macro

The Assign Macro dialog box.

How Assign Macro Button

TheMisc Shapes worksheet after execution of the LoopThruShapes() sub procedure.

TheMisc Shapes worksheet after execution of the LoopThruShapes() sub procedure.

Activesheet Oleobjects Add


The OLEObjects collection object represents all of the ActiveX controls on a document or worksheet and can be accessed from the Worksheet object or the Shapes collection object. Existing ActiveX controls can be accessed and new controls added to a worksheet. For example, a Command Button can be added to a worksheet with either the Add() method of the OLEObjects() collection object, or the AddOLEObject() method of the Shapes collection object.

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1").Select Or


Other ActiveX controls are added using very similar code to that which adds a Command Button by including the type of control in the assignment of the ClassType argument (for example, Forms.Label.1 and Forms.TextBox.1).

Properties of the newly added OLEObject object are manipulated in one of two ways. First, if the property is listed in the Object Browser under the class OLEObject, then it can be assigned a new value in the usual way by returning the OLEObject from the OLEObjects collection object. If the property is not listed under the OLEObject class in the Object Browser, then you must return the actual control object by using the Object property before setting the new value of the control's property.

The sub procedure AddCommandButton() adds a Command Button control to the active worksheet using the AddOLEObject() method of the Shapes collection object. Returning the object from the OLEObjects collection object sets the Name, Left, and Top properties of the OLEObject; however, to set the Caption property, you must first return the control using the Object property of the OLEObject object.

Public Sub AddCommandButton()

ActiveSheet.Shapes.AddOLEObject( _

ClassType:="Forms.CommandButton.1").Name = "cmdTest" With ActiveSheet.OLEObjects("cmdTest") .Left = Range("C1").Left .Top = Range("C4").Top End With

ActiveSheet.OLEObjects("cmdTest").Object.Caption = "Click Me" End Sub

Event procedures for an OLEObject object can be written prior to their addition to a worksheet. You must name the event procedure as VBA would name it when adding the control at Design time. For example, if you intend to add a Command Button control at Run time using the AddCommandButton() sub procedure and you need its Click() event procedure, then you must name the procedure cmdTest_Click(). Furthermore, the event procedure must be added to the object module of the worksheet to which the Command Button control will be added. The Click() event procedure listed here will trigger when the user clicks on the Command Button control cmdTest (previously created by running the AddCommandButton() sub procedure) provided the Click() event procedure is added to the object module of the same worksheet to which the Command Button was added.

Private Sub cmdTest_Click()

MsgBox ("Hello") End Sub

To execute this code, select the worksheet named OLEObjects in the ShapeDemos.xls workbook and click on the button labeled Add Command Button. A Command Button control will immediately appear on the worksheet with the caption Click Me. With a click on the newly added Command Button control a message box appears with the message Hello. The final product of this sequence of events is shown in Figure 10.9.

Adding an OLEObject object to a worksheet and activating its pre-defined Click() event.

Adding an OLEObject object to a worksheet and activating its pre-defined Click() event.

Point Attachment

It is sometimes desirable to create programs that are completely independent of a worksheet or even a workbook. For example, you may want to store programs in your personal macro workbook so they can be executed from the Excel application without having to load a specific workbook file. This is a relatively simple task when your program does not require ActiveX controls, because all the worksheet formatting can be handled with code.

Considering the sub procedures listed previously, it may seem tempting to try and create programs that add ActiveX controls to a worksheet at Run time in order to avoid the requirements of a specific worksheet. Unfortunately, this task cannot be completed because the event procedures of the control added at Run time must still be added to the object module of a specific worksheet; therefore, adding ActiveX controls from a VBA program has limited utility and might just as well be added at Design time when the event procedures are written.

Was this article helpful?

+1 -2
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


  • teodros
    Can I create a worksheet as an object class VBA?
    8 years ago
  • Hallie
    Do shapes in excel have events vba?
    2 years ago
  • negisti
    How tro activate shapes in an excell spread sheet?
    2 years ago
  • Frank
    How to activate shapes in excel?
    1 year ago
    How to change command button shapes in vba?
    1 year ago
  • anthony
    Do Events with drawing a shape and VBA?
    1 year ago
  • kaarlo
    How to activate shape in excel sheet and do some operations?
    8 months ago
  • Roope
    Have worksheet shape click up and down vba?
    7 months ago
  • armando
    How to activate a shape excel macro?
    6 months ago
  • Senait
    How to creat white shape in VBA excel programming?
    6 months ago
  • stephan friedman
    Do event moving shapes to right and left vba?
    6 months ago
  • Denese
    How to simulate a clickof a shape vba?
    3 months ago
  • Karolin
    How to select myshape in vba excel?
    2 months ago
  • Eugene
    How to link shape to a click event?
    10 days ago
  • Venanzio
    How to program different shapes using VBA?
    9 days ago

Post a comment