Linking a commandbar button to a macro in an addin

[ecawilkinson]

Hi,

I have manged to create a custom toolbar in the VB IDE (VBA Editor), with 2 buttons that are visible and enabled. I have an addin named "ChrisRibbon.xlam", which has code that runs when various buttons on the ribbon are pressed. I want to add a procedure (a sub) into that workbook and have it called when I press one of the buttons of the toolbar in the VBA editor (toolbar called ChrisExtras). I tried to do this by the following code:

dim btn as CommandBarButton

Set btn = Application.VBE.CommandBars("ChrisExtras").Controls(1)
btn.OnAction = "ChrisRibbon.xlam!AddSub" ' the code should add a skeleton sub to the active codepane

I have tried various different syntaxes for the OnAction property but nothing works. I do not even get an error message. It will not even run it if I make OnAction = "=MsgBox(""Hi World!"")". The above code does not give any error message, it just runs and finishes. If I query btn.OnAction, it shows exactly what I entered, so I have no idea what is going on. Any help would be much appreciated.

thanks,
Chris

[Jan Karel Pieterse]

You can't assign a macro to a button in the VBE like that, it requires a class module using

Public WithEvents cBtn as CommandBarButton

and then use the Click event of the cBtn object:

 Private Sub cBtn_Click(ByVal Ctrl as Office.CommandbarButton, CancelDefault As Boolean)
'your click code here
CancelDefault=True
End Sub

[ecawilkinson]

You can't assign a macro to a button in the VBE like that, it requires a class module using

Public WithEvents cBtn as CommandBarButton

and then use the Click event of the cBtn object:

 Private Sub cBtn_Click(ByVal Ctrl as Office.CommandbarButton, CancelDefault As Boolean)
'your click code here
CancelDefault=True
End Sub

Thank you very much. That did the trick.

[snb]

The way I do this:

Sub M_snb()
  With Application.CommandBars("Worksheet Menu Bar")
    .Controls.Add(1).Caption = "Example"

    With .Controls.Add
      .Caption = "Subitem"
      .OnAction = "'__adapted snb.xlam'!Thisworkbook.M_Username"
      .Style = msoButtonIconAndCaption
      .FaceId = 3731
    End With
  End With
End Sub