Consulting

Results 1 to 12 of 12

Thread: How to click programmatically on a shape

  1. #1
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    210
    Location

    How to click programmatically on a shape

    Hi,

    Is it possible to click (not to select) programmatically on a shape in order to start a procedure (sub)?

    The code below selects the grey shape, but i need code that "click's" on it (an artificial click fired by code).

    Insert my test code below in a module and run Draw_Shapes. After clicking the red shape i want the message "My color is grey" to appear, via the grey shape.

    [vba]Sub Draw_Shapes()
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
    sh.Delete
    Next
    ActiveSheet.Shapes.AddShape(1, Left:=ThisWorkbook.Application.Range("A1").Left + 10, Top:=ThisWorkbook.Application.Range("A1").Top + 2, Width:=50, Height:=50).Select
    Selection.Name = "GreyShape"
    Selection.OnAction = " My_Color_Is_Grey"
    With Selection.ShapeRange.Line
    .Visible = msoTrue
    .Weight = 1
    End With
    With Selection.ShapeRange.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(150, 150, 150)
    .Transparency = 0
    .Solid
    End With
    ActiveSheet.Shapes.AddShape(1, Left:=ThisWorkbook.Application.Range("E1").Left + 10, Top:=ThisWorkbook.Application.Range("E1").Top + 2, Width:=100, Height:=50).Select
    Selection.ShapeRange.TextFrame.Characters.Text = "Click programmatically on left left"
    Selection.OnAction = "Activate_MyColorIsGrey"
    With Selection.ShapeRange.Line
    .Visible = msoTrue
    .Weight = 1
    End With
    With Selection.ShapeRange.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0
    .Solid
    End With
    End Sub


    Sub My_Color_Is_Grey()
    MsgBox "My color is Grey"
    End Sub


    Sub Activate_MyColorIsGrey()
    ActiveSheet.Shapes("GreyShape").Select
    End Sub
    [/vba] Greatings,
    Stranno
    Last edited by Aussiebear; 12-07-2012 at 03:09 AM. Reason: Added tags to code

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Greetings,

    Unless I am mis-reading (it happens ), why not simply have the code call 'Activate_MyColorIsGrey'?

    Mark

  3. #3
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    How about (untested):
    [vba]set tmp = ActiveSheet.Shapes.AddShape(1, Left:=ThisWorkbook.Application.Range("E1").Left + 10, Top:=ThisWorkbook.Application.Range("E1").Top + 2, Width:=100, Height:=50)
    with tmp
    .name = "GreyShape"
    .OnAction = "Book1!My_Color_Is_Grey" 'Book1 being workbook name
    ...
    end with
    [/vba]
    _________________________________________________________________________
    "In theory there is no difference between theory and practice. In practice there is." - Chuck Reid

    Any day you learn something new is a day not wasted.

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    Perhaps
    [vba]Sub Activate_MyColorIsGrey()
    Application.Run ActiveSheet.Shapes("GreyShape").OnAction
    End Sub[/vba]
    Be as you wish to seem

  5. #5
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    210
    Location
    Hi there,

    Thanx for your commants.

    Meanwhile I solved my problem with a workaround, but the initial problem is not solved yet.

    I was looking for something like:
    ActiveSheet.Shapes (Application.Caller) in general and adapted to my case someting like: ActiveSheet.Shapes (ActiveSheet.Shapes("GreyShape")(Application.Caller)). But That doesn 't work.

    Thanx again

    greetings, stranno

  6. #6
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    210
    Location
    Yes Alfatoon! That's the solution.
    Thanx a lot!

    greetings, stranno

  7. #7
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Quote Originally Posted by Aflatoon
    Perhaps
    [vba]Sub Activate_MyColorIsGrey()
    Application.Run ActiveSheet.Shapes("GreyShape").OnAction
    End Sub[/vba]
    No obstinence intended, and I would not have thought of that "in a million years."

    Mostly just cat-killin' curiousity I suppose: I remain not understanding why we cannot just call the procedure that is called by the button?

    Mark

  8. #8
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    210
    Location
    Hi mark, I like your curiosity. In fact I am just like you. I try to explain. In my real program there is no extra button. I needed this button just to to show what i wanted to achieve, namely to fire the macro which is linked to a shape (of which the name is known) while nobody is clicking on it.

    Kind regards,
    Stranno

  9. #9
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Thank you Stranno :-)

  10. #10
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    nonetheless: it seems very unlikely you do not know which macro has been 'attached' to that shape (e.g. 'M_stranno_001')

    so you can 'call' that macro simply using it's name

    [vba]
    sub M_snb()
    M_stranno_001
    end sub
    [/vba]

  11. #11
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    210
    Location
    hi snb,

    Hi snb,

    A user clicks on a shape. Based on a specific set of properties (color, relative position with respect to other shapes, width etc) a macro is being fired. Calculations are being made, and based on the outcome, the size of the shape changes. After that the user can select additional criteria (this is optional) and again
    calculation are being made. But in order to start the right macro (there are a few of them), the user had to click on the same shape again. And this i found not very elegant. I wanted to automate this by using the name of the shape which was stored somewhere. Aflatoon 's proposal was the solution in my case. Ofcourse there are more roads leading to Rome (Dutch saying). But this worked for me.

  12. #12
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    Maar evenmin elegant...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •