Can't get parameters on macros working...
 

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.

Can't get parameters on macros working...
 

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.

--
Regards,
Tom Ogilvy

"Rob Nicholson" wrote in message
...
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.

Can't get parameters on macros working...
 

Try:

CheckBox.OnAction = "'ClickHandler Hello'"

--

Vasant

"Rob Nicholson" wrote in message
...
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.

Can't get parameters on macros working...
 

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

--
Regards,
Tom Ogilvy


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Try:

CheckBox.OnAction = "'ClickHandler Hello'"

--

Vasant

"Rob Nicholson" wrote in
message
...
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.

Can't get parameters on macros working...
 

Thanks, Tom ... I was too hasty! :-)

Regards,

Vasant.

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Try:

CheckBox.OnAction = "'ClickHandler Hello'"

--

Vasant

"Rob Nicholson" wrote in
message
...
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.

Can't get parameters on macros working...
 

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

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

Cheers, Rob.

Can't get parameters on macros working...
 

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.