CommandBars OnAction Triggers Multiple Times

[m.dw]

Hi All,

I am adding a context menu button via

CommandBarButton testMenu = ExcelCommandBarUtil.GetCommandBars()["cell"].Controls.AddButton();
testMenu.OnAction = @"FooAction(""Hello, World"")";
testMenu.Caption = "Foo";

 FooAction is implemented as

public static void FooAction(string s)
{
MessageBox.Show(s);
}

When I click on the menu, the message box pops up twice and then I get a message "Cannot run the macro 'FooAction("Hello, World")'.  The macro may not be available in this workbook or all macros may be disabled.

I have played around with different permutations of the onAction string to no success.  Please help!

 

[Govert van Drimmelen]

Calling macros with parameters from a CommandBar OnAction is a bit of a black art.

You might try it without parentheses, but with extra single quotes around the whole command:

testMenu.OnAction = @"'FooAction ""Hello, World"" '";

-Govert

[m.dw]

Thanks -- however no success.

I tried the following, using VBA to create the menus (seems to have the same effect as building with c#).  Results for each trial are commented inline.  It seems to work when invoking a macro defined in the vba ("LocalFooAction"), but not when invoking the method in the ExcelDNA c# ("FooAction").  

Is there some custom registration required for the c# method to make it behave more like the VBA?

Sub InitMenus()
    For Each ctl In CommandBars("Cell").Controls
        If InStr(1, ctl.Caption, "TEST") = 1 Then
            ctl.Delete
        End If
    Next ctl
    
    Set btn = CommandBars("Cell").Controls.Add(msoControlButton)
    btn.OnAction = "FooAction(""Hello, World"")"
    btn.Caption = "TEST FooAction(""Hello, World"")"
    'Result: MsgBox pops up twice w/Hello followed by "can't run macro Foo Action..."
    
    Set btn = CommandBars("Cell").Controls.Add(msoControlButton)
    btn.OnAction = "'FooAction(""Hello, World"")'"
    btn.Caption = "TEST 'FooAction(""Hello, World"")'"
    'Result: "can't run macro FooAction..."
    
    Set btn = CommandBars("Cell").Controls.Add(msoControlButton)
    btn.OnAction = "LocalFooAction(""Hello, World"")"
    btn.Caption = "TEST LocalFooAction(""Hello, World"")"
    'Result: MsgBox pops up twice w/Hello
    
    Set btn = CommandBars("Cell").Controls.Add(msoControlButton)
    btn.OnAction = "'LocalFooAction(""Hello, World"")'"
    btn.Caption = "TEST LocalFooAction(""Hello, World"")"
    'Result: Success!

 

    Set btn = CommandBars("Cell").Controls.Add(msoControlButton)
    btn.OnAction = "'LocalFooAction ""Hello, World""'"
    btn.Caption = "TEST 'LocalFooAction ""Hello, World""'"
    'Result: Success!
    
    Set btn = CommandBars("Cell").Controls.Add(msoControlButton)
    btn.OnAction = "'FooAction ""Hello, World""'"
    btn.Caption = "TEST 'FooAction ""Hello, World""'"
    'Result: "can't run macro "c\...\Book2.xlsm'!'FooAction..."
    
End Sub
Sub LocalFooAction(s As String)
    MsgBox (s)
End Sub

[m.dw]

For now, I am packaging a module with the macro

Sub LocalFooAction(s As String)
    Call Application.Run("FooAction", s)
End Sub

but this seems sub-optimal..

[Govert van Drimmelen]

Why do you need to parameter in the macro call?

One reason might be that you want to use the same macro from many different menu items. This works is one case that works better with the new Ribbon menus. There you get some context of which button is pressed in the callback, so you can use the tag or the name of the button in the event handler.

For command bar buttons, you might try to hook the button event instead of setting the OnAction value. That might give you some context in you callback.

-Govert

[m.dw]

Hi Grover,

I am using the parameters to pass context.   Is there an event on CommandBarButton -- that would be perfect, but it seems commented out (http://bit.ly/1NLji6w)?

Taking your line of thinking -- creating a static state / context object could be a solution as well..

Best.

Marc