将参数传递给使用 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.Checkbox
control has the property LinkedCell
. Set this property to the value of rCell.Address
or the like. Set OnAction
to the CheckboxChange
macro. To get the clicked checkbox, use Evaluate(Application.Caller)
inside of CheckboxChange
. Application.Caller
will be the checkbox's name, and Evaluate
returns the checkbox object itself. Using this object you can get its Name
or LinkedCell
.
该Excel.Checkbox
控件具有 属性LinkedCell
。将此属性设置为 的值rCell.Address
等。设置OnAction
为CheckboxChange
宏。要获得点击的复选框,请Evaluate(Application.Caller)
在CheckboxChange
. Application.Caller
将是复选框的名称,并Evaluate
返回复选框对象本身。使用这个对象你可以得到它的Name
or LinkedCell
。