Running Excel macros stored in add-in (xla) files
Q: I have an Excel macro that I need to share with other colleagues at my office. I've saved the workbook that contains the macro as an add-in. The add-in then shows up in the add-ins list and I am able to select the box so it loads. My question is, now that it's loaded, how do I call the macro? I've tried to assign it to menus/toolbars, etc, but the macro doesn't show up in the macro list, so I can't assign it.
A: The way to do this is to assign the macro to a toolbar button or menu item in code. Here are the 3 subroutines I use to add a new menu and populate it with 2 items in my 'iOTA' application. Note that the Auto_open subroutine first tests for the custom menu and if it exists, deletes it before recreating it. The Auto_close subroutine deletes the toolbar, restoring the user's Excel setup when the Add-in is closed.
By the way, you'll need a reference to the Microsoft Office object library.
_____________________________________
Sub Auto_open()
Dim c
Remove_New_Menu
With Application.CommandBars(1).Controls. _
Add(msoControlPopup, , , 9, True)
.Caption = "iOTA"
Set c = .Controls.Add(msoControlButton)
c.Caption = "Consolidate files"
c.OnAction = "Consolidate"
Set c = .Controls.Add(msoControlButton)
c.Caption = "Create iOTA report"
c.OnAction = "DoItAllForMe"
End With
End Sub
_____________________________________
Sub Auto_Close()
Remove_New_Menu
End Sub
_____________________________________
Sub Remove_New_Menu()
Dim c
For Each c In Application.CommandBars(1).Controls
If c.Caption = "iOTA" Then c.Delete
Next
End Sub