[RESOLVED] CommandBar button OnAction - pass parameter

[VBAhack]

Is it legal to pass parameter values to the sub triggered by OnAction? I tried it and for some reason the sub associated with OnAction fires twice when clicked whereas the parameterless version only fires once. Why twice?

Sub CmdBarTest()
    Dim cbar As CommandBar, cbctl As CommandBarButton, i As Integer
    Set cbar = Application.CommandBars.Add(Name:="Custom Toolbar6", _
        Position:=msoBarFloating, MenuBar:=False, Temporary:=True)
    cbar.Visible = True
    For i = 1 To 6
        With cbar.Controls.Add(Type:=msoControlButton)
            .Style = msoButtonCaption
            .Caption = "test" & i
            .OnAction = "Action1(3)"        'Action1 fires twice when button clicked
'            .OnAction = "Action2"          'Action2 fires once when button clicked
        End With
    Next i
End Sub
 
Sub Action1(i%)
    Debug.Print "Action1 " & i
End Sub
 
Sub Action2()
    Debug.Print "Action2"
End Sub

[RobDog888]

What if you pass it ByVal instead of the default ByRef?

[VBAhack]

Hi RobDog,

Same result.

Sub Action1(ByVal i%)
    Debug.Print "Action1 " & i       'still fires twice
End Sub

There'd be a straightforward workaround such as toggling a flag, but seems strange that it would do this. I'm using Excel 2002 by the way.

[DKenny]

VBAHAck
You can use the Parameter property of the control to achieve this result without the dupes.

Sub CmdBarTest()
    Dim cbar As CommandBar, cbctl As CommandBarButton, i As Integer
    
    CommandBars("Custom Toolbar6").Delete
    Set cbar = Application.CommandBars.Add(Name:="Custom Toolbar6", _
        Position:=msoBarFloating, MenuBar:=False, Temporary:=True)
    cbar.Visible = True
    For i = 1 To 6
        With cbar.Controls.Add(Type:=msoControlButton)
            .Style = msoButtonCaption
            .Caption = "test" & i
            .Parameter = i
            .OnAction = "Action1"
        End With
    Next i
End Sub
 
Sub Action1()
Dim ctlCBarControl  As CommandBarControl
Dim i As Integer
    
    Set ctlCBarControl = CommandBars.ActionControl
    If ctlCBarControl Is Nothing Then Exit Sub
    'Examine the Parameter property of the ActionControl to determine
    'which control has been clicked
    i = CInt(ctlCBarControl.Parameter)
    
    Debug.Print "Action1 " & i
End Sub

[RobDog888]

lol I forgot and didnt even double check the button props.

[VBAhack]

Set ctlCBarControl = CommandBars.ActionControl

Brilliant! Never heard of this before. Thanks!

http://msdn.microsoft.com/library/de...HV05221249.asp

[RobDog888]

But if you use WithEvents of the Office.CommandBarButton class you dont need to select caase as each procedure will tie to ech WithEvents used.