make a macro menu in excel

How is the best way to do this:

I have a list of 12 macro's that i want to be available to everyone. I have 10 people in my team and ideally want them located in a menu in excel (like the file,edit etc menus). Rather than going to peoples computer and making them each a menu, is there a way to open Excel and have them there automatically. At present all macros are located in a workbook but ideally id like the workbook (mymacros.xls) to be hidden from view in Excel.

How about making a custom add-in (.xls)? I've got a file at work that makes one for you how you want it set-up. Can post tomorrow if you'd like.

Excellent that would be great.


The only problem is i was hoping to get cracking today. I dont suppose you know where i could find one on the web?

If not if you could send me it tomorrow it would be fantastic


Cheers

Here's a link to part of what my file uses: http://j-walk.com/ss/excel/tips/tip53.htm

Designed by John Walkenbach, it works wonders. I like to add the faceID's to mine. You can find a nice little add-in for those on the same site.

Full directions should be at the site, else search MSKB for Creating an Add-in. Let me know if you still want that sample file. Just post back for any help. :)

Hi Bartoni,

I have an add-in that I distributed to my co-workers, which when installed creates a menu on the main menu bar. I have it in the "ThisWorkbook" code pane of the addin .xla file. The uninstall code removes the menu item when the add-in is uninstalled. This way my co-workers only have to install the add-in, and the rest is taken care of! Hope this helps you with a start.
Matt

Code:

---

Private Sub Workbook_AddinInstall()
 Dim WMB As CommandBar, WMBbp As CommandBarControl
'If menu currently exists, delete it
 On Error Resume Next
 Application.CommandBars("Worksheet Menu Bar").Controls("PW").Delete
 On Error GoTo 0
'Set variables
 Set WMB = Application.CommandBars("Worksheet Menu Bar")
 Set WMBbp = WMB.Controls.Add(Type:=msoControlPopup, Before:=WMB.Controls.Count)
'Add "PW" menu to worksheet menu bar, & means alt-hotkey (underlined)
 With WMBbp
    .Caption = "&PW"
    .Tag = "PW"
 End With
'Add individual menu items and assign macros to them
 With WMBbp.Controls.Add
    .Caption = "Active&Workbook"
    .OnAction = ThisWorkbook.Name & "!vpwbook"
 End With
 With WMBbp.Controls.Add
    .Caption = "Active&Sheet"
    .OnAction = ThisWorkbook.Name & "!vpwsheet"
 End With
 With WMBbp.Controls.Add
    .Caption = "Specified Workbook"
    .OnAction = ThisWorkbook.Name & "!vpwabook"
End With
End Sub

Private Sub Workbook_AddinUninstall()
 'Delete menu item upon uninstallation of add-in
 On Error Resume Next
 Application.CommandBars("Worksheet Menu Bar").Controls("PW").Delete
End Sub

---

The code basically says ( found here ) says...

Code:

---

Option Explicit
Sub CreateMenu()
    '  This sub should be executed when the workbook is opened.
    '  NOTE: There is no error handling in this subroutine
    Dim MenuSheet As Worksheet
    Dim MenuObject As CommandBarPopup
    Dim MenuItem As Object
    Dim SubMenuItem As CommandBarButton
    Dim Row As Integer
    Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
    Call DeleteMenu
    Row = 2
    Do Until IsEmpty(MenuSheet.Cells(Row, 1))
    With MenuSheet
        MenuLevel = .Cells(Row, 1)
        Caption = .Cells(Row, 2)
        PositionOrMacro = .Cells(Row, 3)
        Divider = .Cells(Row, 4)
        FaceId = .Cells(Row, 5)
        NextLevel = .Cells(Row + 1, 1)
    End With
    Select Case MenuLevel
        Case 1 ' A Menu
            Set MenuObject = Application.CommandBars(1). _
            Controls.Add(Type:=msoControlPopup, _
            Before:=PositionOrMacro, _
            Temporary:=True)
            MenuObject.Caption = Caption
        Case 2 ' A Menu Item
            If NextLevel = 3 Then
                Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
            Else
                Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
                MenuItem.OnAction = PositionOrMacro
            End If
            MenuItem.Caption = Caption
            If FaceId <> "" Then MenuItem.FaceId = FaceId
            If Divider Then MenuItem.BeginGroup = True
        Case 3 ' A SubMenu Item
            Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
            SubMenuItem.Caption = Caption
            SubMenuItem.OnAction = PositionOrMacro
            If FaceId <> "" Then SubMenuItem.FaceId = FaceId
            If Divider Then SubMenuItem.BeginGroup = True
    End Select
    Row = Row + 1
    Loop
End Sub
 
Sub DeleteMenu()
    Dim MenuSheet As Worksheet
    Dim Row As Integer
    Dim Caption As String
On Error Resume Next
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
    Row = 2
    Do Until IsEmpty(MenuSheet.Cells(Row, 1))
    If MenuSheet.Cells(Row, 1) = 1 Then
        Caption = MenuSheet.Cells(Row, 2)
        Application.CommandBars(1).Controls(Caption).Delete
    End If
    Row = Row + 1
    Loop
    On Error Goto 0
End Sub

---

That's a good post, and you may want to think about reading it. A lot of good input. :)

Firefty,

If you wouldnt mind would you be able to post/send me the addin. Many Thanks!

Not a problem! In the attached file (zipped), the macros I put in my Personal.xls file. I haven't had much time to mess around with it, so that's why they're there. And it's just a regular 'ole .xls right now. You'll need to save it as a .xla. This way you can change all your formatting, faceID's, macros, positions, etc. It's J-Walk's work, I just utilized it. Hope you can too. :)