Consulting

Results 1 to 8 of 8

Thread: make a macro menu in excel

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location

    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.

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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.

  3. #3
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    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

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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.

  5. #5
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

    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
    Last edited by Aussiebear; 04-29-2023 at 08:29 PM. Reason: Adjusted the code tags

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    The code basically says ( found here ) says...

    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.
    Last edited by Aussiebear; 04-29-2023 at 08:33 PM. Reason: Adjusted the code tags

  7. #7
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    Firefty,

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

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •