将参数传递给使用 onAction 事件注册的方法（VBA - Excel）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1410115/
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
Passing Arguments to Method registered with onAction Event(VBA - Excel)
提问by Manoj
How do you pass an argument to a method which is registered with the onAction event in Excel VBA?
如何将参数传递给在 Excel VBA 中使用 onAction 事件注册的方法？
The code I have is:
我的代码是：
With ActiveSheet.CheckBoxes.Add(rCell.Left, rCell.Top, rCell.Width, rCell.Height)
.Interior.ColorIndex = xlNone
.Caption = ""
.OnAction = "CheckboxChange"
End With
I want to pass the "rCell" to the "CheckboxChange" sub routine. Any way to do that. Basically I want to know the cell in which the checkbox was present in the CheckboxChange sub routine.
我想将“rCell”传递给“CheckboxChange”子例程。任何方式来做到这一点。基本上我想知道 CheckboxChange 子例程中存在复选框的单元格。
采纳答案by guitarthrower
Change this:
改变这个：
.OnAction = "CheckboxChange"
To this:
对此：
.OnAction = "'CheckboxChange""" & rCell & """'"
""" = 3 double quotes
""" = 3 个双引号
"""'" = 3 double quotes & 1 single quote & 1 double quote
"""'" = 3 个双引号 & 1 个单引号 & 1 个双引号
回答by Patrick Lepelletier
with chr(34) instead of quote ("), it might be easier,
使用 chr(34) 而不是引用 (")，可能会更容易，
I have an example with 3 string arguments:
我有一个带有 3 个字符串参数的示例：
.OnAction = "'" & ThisWorkbook.Name & "'!'Importer_Items_Temp_par_Menu_Déroulant " & Chr(34) & Nom_Fichier & Chr(34) & " , " & Chr(34) & Old_Val & Chr(34) & " , " & Chr(34) & ThisWorkbook.Sheets("Import_Objets").Cells(Item_Num, q * 2).Value & Chr(34) & "'"
.
.
Where Importer_Items_Temp_par_Menu_Déroulant is a macro,
其中 Importer_Items_Temp_par_Menu_Déroulant 是一个宏，
Nom_Fichier is a string variable,
Nom_Fichier 是一个字符串变量，
Old_Val too,
Old_Val 也是，
ThisWorkbook.Sheets("Import_Objets").Cells(Item_Num, q * 2).Value is a string too, from a cell in a sheet.
ThisWorkbook.Sheets("Import_Objets").Cells(Item_Num, q * 2).Value 也是一个字符串，来自工作表中的一个单元格。
回答by Aleksey F.
The Excel.Checkboxcontrol has the property LinkedCell. Set this property to the value of rCell.Addressor the like. Set OnActionto the CheckboxChangemacro. To get the clicked checkbox, use Evaluate(Application.Caller)inside of CheckboxChange. Application.Callerwill be the checkbox's name, and Evaluatereturns the checkbox object itself. Using this object you can get its Nameor LinkedCell.
该Excel.Checkbox控件具有 属性LinkedCell。将此属性设置为 的值rCell.Address等。设置OnAction为CheckboxChange宏。要获得点击的复选框，请Evaluate(Application.Caller)在CheckboxChange. Application.Caller将是复选框的名称，并Evaluate返回复选框对象本身。使用这个对象你可以得到它的Nameor LinkedCell。
