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,897
    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,897
    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

    [vba]
    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
    [/vba]

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

    [vba]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[/vba] That's a good post, and you may want to think about reading it. A lot of good input.

  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,897
    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
  •