Can't get parameters on macros working...

  • Thread starter Thread starter Rob Nicholson
  • Start date Start date
R

Rob Nicholson

Consider a checkbox created at run-time something like this:

Dim CheckBox As Shape
Set CheckBox=Me.Shapes.AddFormControl(xlCheckBox, 0,0,100,0)
CheckBox.OnAction="'ClickHandler'"

Sub ClickHandler()
Debug.Print "Clicked!"
End Sub

Run the code and click on the checkbox and the ClickHandler runs.

However, I'd love to be able to pass a parameter and I've read several posts
whereby it says you can set OnAction to 'ClickHandler "Hello"' and it will
pass "Hello" as the paramater:

Dim CheckBox As Shape
Set CheckBox=Me.Shapes.AddFormControl(xlCheckBox, 0,0,100,0)
CheckBox.OnAction="'ClickHandler ""Hello""'"

Sub ClickHandler(Text)
Debug.Print "Clicked!"
End Sub

This just doesn't work - the code runs without complaining but ClickHandler
never gets called when you click.

What am I doing wrong?

Thanks, Rob.
 
Should work in xl2000 SR1 and earlier. Believe support for this was dropped
with the latest Service Release of xl2000 and later versions. I don't have
any official word on this, but based on past postings in this group of what
versions were used by people having problems.
 
Actually it would be:

Sub SetOnAction()
ActiveSheet.CheckBoxes(1).OnAction = _
"'ClickHandler ""Hello""'"

End Sub

Sub ClickHandler(sStr)
MsgBox sStr
End Sub

"'ClickHandler Hello'" comes up with a blank message box.

Tested. and works. Excl 2000 SR-1 US English Windows 2000 Pro
 
Should work in xl2000 SR1 and earlier. Believe support for this was
dropped
with the latest Service Release of xl2000 and later versions. I don't
Click to expand...
have

Ahh that might explain it - we're on the latest service pack.

Cheers, Rob.
 
Tested. and works. Excl 2000 SR-1 US English Windows 2000 Pro

Doesn't work on SP-3 :-(

Sub Init()
While Shapes.Count
Shapes(1).Delete
Wend
Dim Checkbox As Shape
Set Checkbox = Shapes.AddFormControl(xlCheckBox, 200, 200, 100, 0)
Checkbox.OnAction = "'Sheet1.ClickHandler ""Hello""'"
End Sub

Sub ClickHandler(Text)
MsgBox "ClickHandler: " & Text
End Sub

The code doesn't complain when Init is run but that click handler just
doesn't fire. Remove the parameter and have a simple Sub ClickHandler() and
it works fine.

Anyway, found a workaround: Application.Caller can be used to determine
which checkbox fired the event. So I built a simple Dictionary containing
the parameters, key'd by the checkbox name which I can then lookup using
CheckBoxes(Application.Caller).Name (or something like that).

Cheers, Rob.
 
Back
Top