Consulting

Results 1 to 2 of 2

Thread: Custom Menu/Macro Library

  1. #1

    Custom Menu/Macro Library

    I'm looking for a solution that will help to provide a more dynamic way to update a list of macros. Currently, I'm simply going back into the code below and adding additional lines for the new macros that are added to the list. What I'm looking to do is update this list, from a library stored on a common folder, without having to insert additional lines of code.

    Any assistance is greatly appreciated.

    Option Explicit
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next 'in case the menu item has already been deleted
    Application.CommandBars("Worksheet Menu Bar").Controls("My Macros").Delete 'delete the menu item
    End Sub

    Private Sub Workbook_Open()
    Dim cmbBar As CommandBar
    Dim cmbControl As CommandBarControl
    
    
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
    
    With cmbControl
    .Caption = "&My Macros" 'names the menu item
    With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
    .Caption = "My Macro No 1" 'adds a description to the menu item
    .OnAction = "RunMyMacro1" 'runs the specified macro
    .FaceId = 1098 'assigns an icon to the dropdown
    End With
    
    With .Controls.Add(Type:=msoControlButton)
    .Caption = "My Macro No 2"
    .OnAction = "RunMyMacro2"
    .FaceId = 108
    End With
    
    With .Controls.Add(Type:=msoControlButton)
    .Caption = "My Macro No 3"
    .OnAction = "RunMyMacro3"
    .FaceId = 21
    End With
    End With
    End Sub
    Last edited by SamT; 05-03-2016 at 02:48 PM. Reason: Added CODE Tags with Editor's # Icon

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Store the parameters on a very hidden sheet and loop thru them with code. On my broken computer, i have an example where the author created an entire custom menu with a small loop of code and an extensive parameter layout on one sheet.

    IIRC, Column"A" was for Menu Bar items, "B" was for Sub Menus, and "C" was for even lower Items on the hierarchy. Parameters were stored by offset to the Name.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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