How do you pass an argument to a method which is registered with the onAction event in Excel VBA?
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.
Change this:
.OnAction = "CheckboxChange"
To this:
.OnAction = "'CheckboxChange""" & rCell & """'"
""" = 3 double quotes
"""'" = 3 double quotes & 1 single quote & 1 double quote
with chr(34) instead of quote ("), it might be easier,
I have an example with 3 string arguments:
.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,
Nom_Fichier is a string variable,
Old_Val too,
ThisWorkbook.Sheets("Import_Objets").Cells(Item_Num, q * 2).Value is a string too, from a cell in a sheet.
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.
