CommandBarButton.OnAction prop fires when set? Arg!
 

I'm programmatically building a menu bar with a series of
command buttons but I'm having trouble assigning the
command for the button. Every time I set the .OnAction
property, the action fires. Then future clicks of the
button do nothing. This is really frustrating since I
know it should be an easy fix. How do I assign the value
of the command I want to call for a command button on my
command bar. Here is an excerpt from my
code;

Dim cmdOpen As CommandBarButton
Set cmdOpen = cbpPGDFile.Controls.Add
(msoControlButton, , , , True)
cmdOpen.Caption = "Open/New PGD File"
cmdOpen.OnAction = MsgBox("I don't want this
to run when setting the property but it does!")

CommandBarButton.OnAction prop fires when set? Arg!
 

I doesn't matter what I put there. My original program
called "myprog.xla!mysub" which worked but fired when it
was declared and then never fires when the button is
clicked. I only want it to fire when the user clicks the
button. I'm stumped. Any other ideas? Thanks for the
feedback.
-Kevin

-----Original Message-----
I think you're looking for something like:

cmdOpen.OnAction = thisworkbook.Name & "!mymacronamehere"


kevin wrote:

I'm programmatically building a menu bar with a series
of
command buttons but I'm having trouble assigning the
command for the button. Every time I set the .OnAction
property, the action fires. Then future clicks of the
button do nothing. This is really frustrating since I
know it should be an easy fix. How do I assign the
value
of the command I want to call for a command button on
my
command bar. Here is an excerpt from my
code;

Dim cmdOpen As CommandBarButton
Set cmdOpen = cbpPGDFile.Controls.Add
(msoControlButton, , , , True)
cmdOpen.Caption = "Open/New PGD File"
cmdOpen.OnAction = MsgBox("I don't want
this
to run when setting the property but it does!")

--

Dave Peterson

.

CommandBarButton.OnAction prop fires when set? Arg!
 

Hi Kevin,

I doesn't matter what I put there. My original program
called "myprog.xla!mysub" which worked but fired when it
was declared and then never fires when the button is
clicked. I only want it to fire when the user clicks the
button. I'm stumped. Any other ideas? Thanks for the
feedback.

The OnAction property of a commandbar should *not* be the actual code
that you want to run when the button is clicked, but instead should be
a reference to a routine that you want to run.

So instead of:

cmdOpen.OnAction = MsgBox("Runs When First Set!")

You need to use:

cmdOpen.OnAction = "'" & ThisWorkbook.Name & "'!SomeMacroName"


'Put in a standard module
Sub SomeMacroName()
MsgBox "Runs when button is clicked!"
End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk