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