Custom Menu/Macro Library

[furbmikey]

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

[SamT]

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.