从Shape OnAction属性动态传递参数传递的宏 [英] Dynamically call a macro passing parameters from shape OnAction property

问题描述

好吧，我的问题是我创建了一个VBA Sub，它接收一个excel单元格引用和2个文本值以及一个Variant作为参数.

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)

此Sub成功在oCell上创建了一个按钮，但是我必须将参数发送给Macro，什么是实现该目标的最佳方法?

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

在我设法解决问题的帮助下，我在这里提出了一个更简单的解决方案

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

推荐答案

您可以为OnAction分配一个字符串，该字符串具有要调用的子项及其后的参数(请注意整个字符串都用单引号引起来)

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)

赞:

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

Sub SubToCallOnAction(text As String)

    MsgBox text

End Sub

数字参数不需要引号(尽管它们将通过Number->默认字符串转换->默认数字转换传递)

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

这篇关于从Shape OnAction属性动态传递参数传递的宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！