Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
R/T 1004 - Unable to get Button prop of Ws Class | Excel Discussion (Misc queries) | |||
TEMPLATE IN EXCEL FOR MONTHLY PAYMENTS FOR MORE THAN ONE PROP | New Users to Excel | |||
Drop Down Selection fires a module? | Excel Worksheet Functions | |||
Thanks - creating a commandbarbutton with a custom bitmap | Excel Programming | |||
creating a commandbarbutton with a custom bitmap | Excel Programming |