Excel and Office RAQ

Running Excel macros stored in add-in (xla) files


Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing


Knowledge measurement audit

Item bank services

Item design course








Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in


Home page

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


  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()


End Sub


Sub Remove_New_Menu()

  Dim c

  For Each c In Application.CommandBars(1).Controls

    If c.Caption = "iOTA" Then c.Delete


End Sub



Back to other RAQ topics