将参数传递给使用 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:47:03  来源:igfitidea点击:

Passing Arguments to Method registered with onAction Event(VBA - Excel)

excelvba

提问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等。设置OnActionCheckboxChange宏。要获得点击的复选框,请Evaluate(Application.Caller)CheckboxChange. Application.Caller将是复选框的名称,并Evaluate返回复选框对象本身。使用这个对象你可以得到它的Nameor LinkedCell