vba 从形状 OnAction 属性动态调用传递参数的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18283786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically call a macro passing parameters from shape OnAction property
提问by Roger Barreto
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.
好吧，我的问题是我创建了一个 VBA Sub，它接收一个 excel Cell 引用和 2 个文本值以及一个 Variant 作为参数。
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 ?
这个 Sub 成功地在 oCell 上创建了一个按钮，但我必须向宏发送一个参数，实现这一目标的最佳方法是什么？
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
回答by Cor_Blimey
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)
您可以为 OnAction 分配一个字符串，该字符串具有要调用的子项，后跟其参数（注意整个字符串用单引号括起来）
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)
数字参数不需要引号（尽管它们将通过 Number -> 默认字符串转换 -> 默认数字转换传入）
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
回答by LePatay
-- For people who find this page in 2016+ --
-- 对于在 2016+ 年找到此页面的人 --
Cor_Blimey's solution doesn't work in Excel 2003. You have to add commas instead of blanks between the arguments, like so:
Cor_Blimey 的解决方案在 Excel 2003 中不起作用。您必须在参数之间添加逗号而不是空格，如下所示：
Shape.OnAction = _
"'SubToCallOnAction """ & _
Shape.Name & """,""" & _
databaseTable & """,""" &
databaseField & """'
