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.

--
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.

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.

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.

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.

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.

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.