Excel and Office RAQ

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

   

Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing

Services

Knowledge measurement audit

Item bank services

Item design course

Products

WaterMarker

iOTA

STASiS

ACiS

CaSelector

CaSCADE

Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in

Contact

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

  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

 

 

Back to other RAQ topics