Distributing excel macro

Question

I have created a excel macro, now i want it distribute to others who need to use it.

All i want is if some one could please help me with steps to attach the macro to a custom tool bar button and then save both the custom tool bar and to .xla file.

Then the users can store the .xla file on to XLSTART director and when excel is launched custom tool bar appears and readyto use.

Answer 1

This code adds a new menu option, and references VBA methods, so is similiar to what you want.

Add these to the workbook VBA:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
   MenuBars(xlWorksheet).Menus("NewMenu").Delete
End Sub

Private Sub Workbook_Open()
On Error Resume Next
    MenuBars(xlWorksheet).Menus("NewMenu").Delete
    Dim statMenu As Menu
    Set statMenu = MenuBars(xlWorksheet).Menus.Add(Caption:="NewMenu")
    statMenu.MenuItems.Add Caption:="Item 1", OnAction:="RunFirstItem"
    statMenu.MenuItems.Add Caption:="Item 2", OnAction:="RunSecondItem"
End Sub

Then add some methods RunFirstItem & RunSecondItem to the module code.

Save as an .XLA and off you go.