Dynamically call macro transfer options from the OnAction property of a form

Question

Well, my problem is that I created a Sub VBA that gets a link to excel Cell and 2 text values ​​and a Variant as parameter.

Sub CreateButton(oCell, sLabel, sOnClickMacro, oParameters) 

This subo managed to create a button above oCell, but I have to send the parameter to the macro, what is the best way to achieve this?

If you dug up some ways that didn’t work, as well as other dirty ones that don’t make me fill confortable

---

Using this help, I was able to solve the problem, I am suppressing here a simpler working solution for

 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 

Answer 1

You can assign an OnAction string that has a subroutine to call, followed by its arguments (note the entire string enclosed in single quotes)

how

 Shape.OnAction = "'SubToCallOnAction ""Hello World!""'" Sub SubToCallOnAction(text As String) MsgBox text End Sub 

Quotation marks are not needed in numeric arguments (although they will be passed through Number -> default string conversion -> default number conversion)

So, I think what you want to do is pass the name of the button the button was clicked on:

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

A more advanced and flexible use might be something like this:

 '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 

Answer 2

- for people who find this page in 2016+ -

Cor_Blimey's solution does not work in Excel 2003. You must add commas instead of spaces between arguments, for example:

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