Assign OnAction to button in Add-In?

Topic

New Reply

Gary,

The code for your “StartChase” macro should be in a ‘normal’ module, not in a module ‘behind’ a sheet.

Reply | Quote

Replies

Hans,

Thanks for this – you can see how much programming I’ve done in Excel! – I will give this a try when I get back to work tomorrow.

Regards,
Gary

Reply | Quote

Hi,

I’ve been having a good wrestle creating my first Excel add-in, and am stuck on what I hope is one last snag:

I’m using the following code to create a toolbar and button when Excel opens (the add-in will be in the startup folder) – this code is in the ThisWorkbook module of the add-in):

Private Sub Workbook_Open()
Dim cbrAMG As CommandBar
Dim ctlNewBtn As CommandBarButton
Set cbrAMG = Application.CommandBars.Add(Name:="AMG_Chaser", _
   Position:=msoBarTop, Temporary:=True)
With cbrAMG
    .Visible = True
    Set ctlNewBtn = .Controls.Add(Type:=msoControlButton, Temporary:=True)
    With ctlNewBtn
        .FaceId = 2151
        .OnAction = "StartChase"
        .Caption = "Send Chaser Msgs"
    End With
End With
End Sub

This code succeeds in creating the toolbar, the button and the tooltip. But when I click on the button to run the macro, I get an error message “The macro ‘AMG_ChaserAddIn_v1.xla!StartChase’ cannot be found.”

(The “StartChaser” macro is actually sitting, expectantly, in the code module behind Sheet1 of the add-in, waiting to be called.)
Any clues on how to programatically set the correct reference to this macro for the button’s OnAction property would be much appreciated – I’m guessing the difficulty has something to do with the way macros are available in add-ins but am drawing a blank at that point.

Thanks,
Gary

Reply | Quote

Gary,

Just forgot to mention this: I strongly advice you to add a few lines of code to the Workbook_Open event, and eventually even to the Workbook_BeforeClose event, to delete the toolbar you created.

Private Sub Workbook_Open()
Dim cbrAMG As CommandBar
Dim ctlNewBtn As CommandBarButton
On Error Resume Next
Application.CommandBars(“AMG_Chaser”).Delete
Set cbrAMG = Application.CommandBars.Add(Name:=”AMG_Chaser”, Position:=msoBarTop, Temporary:=True)
With cbrAMG
.Visible = True
Set ctlNewBtn = .Controls.Add(Type:=msoControlButton, Temporary:=True)
With ctlNewBtn
.FaceId = 2151
.OnAction = “StartChase”
.Caption = “Send Chaser Msgs”
End With
End With

End Sub

and

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars(“AMG_Chaser”).Delete
End Sub

The before_close event will delete the toolbar when you uncheck the add-in.

Excel stores the toolbar settings to its xlb file when you quit Excel. When you start up Excel the next time, the toolbar will be there, so, the add-in will cause an error when trying to create a toolbar that already exists. You should delete the existing toolbar before you run the code that creates it.

Reply | Quote

Hans,

I somehow managed to miss your followup post a month ago, when you posted it ( ).

Just wanted to mention that I had already put code in to destroy the toolbar when the Excel app is closed.

The actual add-in was deployed a few weeks ago, so far no problems. Thanks again for your help in getting me set right!

Gary

Reply | Quote

I don’t want to find out the hard way so I thought I’d better ask:

By using the _Open event and _Close event to deploy and delete a custom toolbar, What would happen if the spreadsheet or Excel did not close properly, assuming that work had been saved? I’m thinking power loss, computer lock up, accidental re-boot, etc. Just trying to prevent an issue before it happens…Murphy will enter the picture.

Reply | Quote

If you look at Hans Pottel’s code closely, you will see that the Workbook_Open event deletes the toolbar if it exists (with an On Error Resume Next before it to avoid raising an error if the toolbar doesn’t exist), and after that creates it anew.

Regards,
Hans (not Pottel)

Reply | Quote