Dynamically call a macro passing parameters from shape OnAction property

Question

Well, my problem is that I have created a VBA Sub that receives an excel Cell reference and 2 text values and a Variant as parameter.

Sub CreateButton(oCell, sLabel, sOnClickMacro, oParameters)

This Sub succeeds in creating a button over the oCell but I must send a parameter to the Macro what is the best way to achieve that ?

If have digged some ways that doesn't worked and also others dirty that dont make me fill confortable

With the help given I managed to resolve the problem, I'm putting down here a simpler working solution for that

Sub Button_Click(sText)
    MsgBox "Message: " & sText
End Sub

Sub Test_Initiallize()
    Dim oCell
    Dim oSheet
    Dim oShape

    Set oCell = Range("A1")
    Set oSheet = ThisWorkbook.Sheets(1)

    For Each oShape In oSheet.Shapes
        oShape.Delete
    Next

    Set oShape = oSheet.Shapes.AddShape(msoShapeRectangle, oCell.Left, oCell.Top,     oCell.Width, oCell.Height)

    oShape.TextFrame.Characters.Text = "Click Me"
    oShape.OnAction = "'Button_Click ""Hello World""'"
End Sub

Cor_Blimey · Accepted Answer · 2013-08-17T23:31:18.310

13

You can assign a string to OnAction that has the sub to call followed by its arguments (note the whole string wrapped in single quotes)

Like:

Shape.OnAction = "'SubToCallOnAction ""Hello World!""'"

Sub SubToCallOnAction(text As String)

    MsgBox text

End Sub

Numeric arguments don't need the quotes (though they will be passed in via Number -> default string conversion -> default number conversion)

So, I guess, what you are wanting to do is pass in the name of the button that was clicked:

Shape.OnAction = "'SubToCallOnAction """ & Shape.Name & """'"

More advanced and flexible usage could be something like:

'Set the button up like:
databaseTable = "tbl_ValidAreas"
databaseField = "Country"
Shape.OnAction = _
    "'SubToCallOnAction """ & _
    Shape.Name & """ """ & _
    databaseTable & """ """ & 
    databaseField & """'"

...
Sub SubToCallOnAction(buttonID As String, ParamArray args)

    Select Case buttonID
        Case "button1"
            'get the relevant data or whatever using the ParamArray args
            Call GetData(args(0),args(1))
        ...
    End Select

End Sub

edited Aug 17 '13 at 23:31

answered Aug 17 '13 at 00:28

Cor_Blimey

3,260
1
14
20

Well, Im trying to create dynamically a list of itens fr database that might be edited, deleted or inserted, for the first two buutons I need to get the ID on click, and Im avoiding to use the change event for all the cells, so I prefer to have Something associated in the comand button – Roger Barreto Aug 17 '13 at 16:06
Yeah, that worked out, thanks! I have tryed this approach before but didnt notice that the call must have single quotes. Surelly that will do the trick ! – Roger Barreto Aug 17 '13 at 23:16
Guess what prefix you have to use if one of the parameter values is a string, and contains a single quote (character 34)? :-O – TheBlastOne May 23 '14 at 11:34
@TheBlastOne yep, strange isn't it ;). It is because two lots of double quote marks (e.g. "") inside a string resolve to a single quote mark which is why to just have a string of nothing more than a single quote mark it is the weird """" :) – Cor_Blimey May 23 '14 at 18:24
@Cor_Blimley No. Or at least not sure if I understand correctly :-) Truth is: Two double quotes within a VB string resolve to one single double quote. That´s why they are there: to get the string parameter values put correctly into double quote pairs. The single quote character does not need to be escaped. So far, so clear. The question is: How would you escape a single quote within the single-quoted string? Should one be contained in one of string parameter values, the code will fail if no escaping is done. – TheBlastOne May 23 '14 at 18:42
@TheBlastOne ah I see your point - I thought you meant a single double quote. I am not aware that there is any way to escape a single quote (if there is then please let me know!). The functionality I point out in my answer is already undocumented behaviour - I think really it is *as is*. It shouldn't be used for passing in arbitrary strings. It should be used instead for passing in sufficient identifying information to locate the relevant arbitrary string in a variable stored elsewhere. – Cor_Blimey May 23 '14 at 19:46

score 1 · Answer 2 · answered May 11 '16 at 07:31

-- For people who find this page in 2016+ --

Cor_Blimey's solution doesn't work in Excel 2003. You have to add commas instead of blanks between the arguments, like so:

Shape.OnAction = _
    "'SubToCallOnAction """ & _
    Shape.Name & """,""" & _
    databaseTable & """,""" & 
    databaseField & """'

Dynamically call a macro passing parameters from shape OnAction property

2 Answers2

Linked