How to make a reusable button from a macro?

Question

Having a working macro, I'd like to reuse it. Namely, to create a button on a toolbar (with a name + icon) that will launch a macro. Tried some tutorials (example: http://www.officetooltips.com/excel/tips/create_a_toolbar_button_or_menu_item_to_run_a_macro.html), but I'd also like to pack the creation code in some file, so that when clicked, the macro would be installed.

Is there an easy way to do it, and if yes, than how? (the best option would work for both Windows and Mac)

Answer 1

Update 4/20: great comment about the complexity of building an add-in. That being said, I'm sure the old timers here would say that something worth doing is worth doing right :). Here is a short walkthrough for creating an add-in:

(1) Save an xlsm or xlsb file with a name that's easy to increment for versions of your add-in.

(2) Add the following scripts into ThisWorkbook to ensure that you create a menu bar when the workbook is opened and when the workbook is activated:

Private Sub Workbook_Open()
    Call CreateMenuBar
End Sub

Private Sub Workbook_Activate()
    Call CreateMenuBar
End Sub

(3) Create a new module and add the following code to create, delete and update your menu bar:

Option Explicit
Sub CreateMenuBar()

    Dim MenuObject As CommandBarPopup
    Dim MenuItem As Object
    Dim SubMenuItem As Object

    'clear the old menu bar
    Call DeleteMenuBar("&MyMenuBar")

    'create the menu bar and drop down options
    Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, _
        before:=10, Temporary:=True)
    MenuObject.Caption = "&MyMenuBar"
    MenuObject.OnAction = "UpdateMenuBar"

    'first level menu option
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
    MenuItem.Caption = "&First Menu Stuff"

        'link to first script
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        SubMenuItem.Caption = "&First Script"
        SubMenuItem.OnAction = "Script1"

        'link to second script
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        SubMenuItem.Caption = "&Second Script"
        SubMenuItem.OnAction = "Script2"

    'first level menu option
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
    MenuItem.Caption = "&Second Menu Stuff"

        'link to third script
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        SubMenuItem.Caption = "&Third Script"
        SubMenuItem.OnAction = "Script3"

End Sub

Sub DeleteMenuBar(MenuName As String)
    On Error Resume Next
    Application.CommandBars(1).Controls(MenuName).Delete
    On Error GoTo 0
End Sub

Sub UpdateMenuBar()
    'do special checks, like verifying sheets, in this routine
End Sub

(4) Verify your scripts work and save the file.

(5) Save the file again as an xlam or xla file and distribute that to users. Boom!

--Original post below--

Here's what an add-in looks like on a Windows Excel instance:

And here's what an add-in looks like on a Mac Excel instance:

An add-in can be very handy if you develop many scripts for a fleet of users and want to ensure they're all using the same code.

Answer 2

I posted this on another question, but it wasn't what they were looking for. Would this work for you?

In the options for Excel, click on Customize Ribbon. Above the list of things you can add there should be a dropdown box where you can select Macros. The list should then be populated with macros to add to your ribbon!