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