Building & Customization of menu's in Excel.

The following code has come from Microsoft and i have found it invaluable when it came to building new menu's or altering menu's i have built using the Ozgrid Custom Menu i posted in another blog.


'Return an ID for a command bar control
'The following example code returns the ID for the active menu bar:
Sub Id_Control()
Dim myId As Object
   Set myId = CommandBars("Worksheet Menu Bar").Controls("File")
   MsgBox myId.Caption & Chr(13) & myId.ID
End Sub

'Determine the name of the active menu bar
'The following example code returns the name for the active menu bar:
Sub MenuBars_GetName()
   MsgBox CommandBars.ActiveMenuBar.Name
End Sub


'Save the active state (for built-in or for customized menu bars)
'You may want to declare the OriginalMenuBar variable a public variable so that
'a subroutine can use it in another subroutine, such as an Auto_Close subroutine.
'Declaring and using the variable this way resets the user's previous menu bar to its original state.
'The following sample macro resets the menu bar:
Public OriginalMenuBar As Object
Sub MenuBars_Capture()
   Set OriginalMenuBar = CommandBars.ActiveMenuBar
End Sub

'Display a custom command bar
'The following example creates and displays a custom My Custom Bar menu bar,
'and then replaces the built-in menu bar:
Sub MenuBar_Show()
Dim myNewBar As Object
   Set myNewBar = CommandBars.Add(Name:="Custom1", Position:=msoBarFloating)
   ' You must first enable your custom menu bar before you make it visible.
   ' Enabling a menu bar adds it to the list of available menu bars on
   ' the Customize dialog box.
' Setting the menubar property to True replaces the built-in menu bar.
   myNewBar.Enabled = True
   myNewBar.Visible = True
End Sub

'Delete a custom command bar
'The following example code deletes the custom menu bar that is named Custom 1:
Sub MenuBar_Delete()
   CommandBars("Custom1").Delete
End Sub

'Hide a command bar
'The following example code removes the built-in Chart menu bar from the
'list of available menu bars:
Sub MenuBar_Disabled()
   CommandBars("Chart").Enabled = False
End Sub

'Display a command bar
'The following example code adds the built-in Chart menu bar from the
'list of available menu bars:
Sub MenuBar_Enabled()
   CommandBars("Chart").Enabled = True
End Sub

'Restore a built-in command bar
'Restoring a menu bar resets the default controls (for both menus and menu items).
'The following example code restores the built-in Chart menu bar:
Sub MenuBar_Restore()
   CommandBars("Chart").Reset
End Sub

'Note You can only reset built-in menu bars. You cannot reset a custom menu bar.

'Add a custom menu control to a command bar
'The following example code adds the name of a menu that you add programmatically to the Worksheet menu bar. 
'For example, this code adds the menu name New Menu to the to the Worksheet menu bar.

'Note You can give this menu any name that you want.

Sub Menu_Create()
Dim myMnu As Object
   Set myMnu = CommandBars("Worksheet menu bar").Controls. _
      Add(Type:=msoControlPopup, before:=3)
   With myMnu
   ' The "&" denotes a shortcut key assignment (Alt+M in this case).
      .Caption = "New &Menu"
   End With
End Sub

'Disable a menu control on a command bar
'A menu control that is disabled appears dimmed and is not available on a command bar.
'The following example disables the New Menu menu:
Sub Menu_Disable()
   CommandBars("Worksheet menu bar").Controls("New &Menu").Enabled = False
End Sub

'Enable a menu control on a command bar
'The following example code enables the New Menu menu that you disabled in the "Disable
'a menu control on a command bar" section:
Sub Menu_Enable()
   CommandBars("Worksheet menu bar").Controls("New &Menu").Enabled = True
End Sub

'Delete a menu control on a command bar
'The following code example deletes the New Menu menu that you created in the "Add a
'custom menu control to a command bar" section from the Worksheet menu bar:
Sub Menu_Delete()
   CommandBars("Worksheet menu bar").Controls("New &Menu").Delete
End Sub

'Restore a menu control on a command bar
'The following example code restores the built-in Chart menu bar on the Worksheet menu bar:
Sub Menu_Restore()
Dim myMnu As Object
   Set myMnu = CommandBars("Chart")
   myMnu.Reset
End Sub


'Add a separator bar to a menu control
'The following example code adds a separator bar before the Worksheet command on the
'Insert Menu:
Sub menuItem_AddSeparator()
   CommandBars("Worksheet menu bar").Controls("Insert") _
   .Controls("Worksheet").BeginGroup = True
End Sub

'Note To remove a separator bar, set the BeginGroup property to False.
'Create a custom command control on a menu
'The following example code creates a new command that is named Custom1 on the Tools menu
'of the Worksheet menu bar, and then runs the Code_Custom1 macro when you click Custom1:
Sub menuItem_Create()
   With CommandBars("Worksheet menu bar").Controls("Tools")
      .Controls.Add(Type:=msoControlButton, before:=1).Caption = "Custom1"
      .Controls("Custom1").OnAction = "Code_Custom1"
   End With
End Sub

'Put a check mark next to a command control
'The following example code puts a check mark next to the Custom1 command if it is not
'selected, and then removes the check mark if the Custom1 command is selected:
Sub menuItem_checkMark()
Dim myPopup As Object

   Set myPopup = CommandBars("Worksheet menu bar").Controls("Tools")
   If myPopup.Controls("Custom1").State = msoButtonDown Then
      ' Remove check mark next to menu item.
      myPopup.Controls("Custom1").State = msoButtonUp
      MsgBox "Custom1 is now unchecked"
      Else
        ' Add check mark next to menu item.
         myPopup.Controls("Custom1").State = msoButtonDown
         MsgBox "Custom1 is now checked"
    End If
End Sub

'Disable a command control on a command bar
'The following example code disables the Custom1 command that you created on the Tools
'menu in the "Create a custom command control on a menu" section:
Sub MenuItem_Disable()
Dim myCmd As Object
   Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
   myCmd.Controls("Custom1").Enabled = False
End Sub

'Enable a command control on a command bar
'The following example code enables the Custom1 command that you disabled in the
'"Disable a command control on a command bar" section:
Sub MenuItem_Enable()
Dim myCmd As Object
   Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
   myCmd.Controls("Custom1").Enabled = True
End Sub

'Delete a command control on a menu
'The following example code deletes the Save command on the File menu:
Sub menuItem_Delete()
Dim myCmd As Object
   Set myCmd = CommandBars("Worksheet menu bar").Controls("File")
   myCmd.Controls("Save").Delete
End Sub

'Restore a built-in command control on a menu
'To restore a command control on a menu, you must know the identification (ID) number
'for the control. To determine the ID number, see the "Return an ID for a command bar
'Control " section. The following example deletes and then restores the Save command that "
'you deleted in the "Delete a command control on a menu" section:
Sub menuItem_Restore()
Dim myCmd As Object
   Set myCmd = CommandBars("Worksheet menu bar").Controls("File")
   ' Id 3 refers to the Save menu item control.
   myCmd.Controls.Add Type:=msoControlButton, ID:=3, before:=5
End Sub

'Add a submenu
'The following example code adds a new submenu that is named NewSub to the Tools menu
'on the Worksheet menu bar:
Sub SubMenu_Create()
Dim newSub As Object
   Set newSub = CommandBars("Worksheet menu bar").Controls("Tools")
   With newSub
      .Controls.Add(Type:=msoControlPopup, before:=1).Caption = "NewSub"
   End With
End Sub

'Add a command to a submenu
'The following example code adds a new command that is named SubItem1 to the NewSub
'submenu, and then it runs the Code_SubItem1 macro when you click SubItem1:
Sub SubMenu_AddItem()
Dim newSubItem As Object
   Set newSubItem = CommandBars("Worksheet menu bar") _
   .Controls("Tools").Controls("NewSub")
   With newSubItem
      .Controls.Add(Type:=msoControlButton, before:=1).Caption = "SubItem1"
      .Controls("SubItem1").OnAction = "Code_SubItem1"
   End With
End Sub

'Disable a command control on a submenu
'The following example code disables the same SubItem command that you created in the
'"Add a command to a submenu" section :
Sub SubMenu_DisableItem()
   CommandBars("Worksheet menu bar").Controls("Tools") _
   .Controls("NewSub").Controls("SubItem1").Enabled = False
End Sub


'The following example enables the same SubItem command:
Sub SubMenu_EnableItem()
   CommandBars("Worksheet menu bar").Controls("Tools") _
   .Controls("NewSub").Controls("SubItem1").Enabled = True
End Sub

'Delete a command on a submenu
'The following example deletes the SubItem1 command that you created on the NewSub
'submenu in the "Add a command to a submenu" section:
Sub SubMenu_DeleteItem()
   CommandBars("Worksheet menu bar").Controls("Tools") _
   .Controls("NewSub").Controls("SubItem1").Delete
End Sub

'Disable a submenu control
'The following example code disables the NewSub submenu that you created on the Tools
'menu in the "Add a submenu" section:
Sub SubMenu_DisableSub()
   CommandBars("Worksheet menu bar").Controls("Tools") _
   .Controls("NewSub").Enabled = False
End Sub

'Note To enable the disabled control, set the Enabled property to True.
'Delete a submenu control
'The following example code deletes the NewSub submenu that you created on the Tools
'menu in the "Add a submenu" section:
Sub SubMenu_DeleteSub()
   CommandBars("Worksheet menu bar").Controls("Tools") _
   .Controls("NewSub").Delete
End Sub


'Create a new shortcut menu bar
'The following example code creates a new shortcut menu bar that is named myShortcutBar:
Sub Shortcut_Create()
Dim myShtCtBar As Object
   Set myShtCtBar = CommandBars.Add(Name:="myShortcutBar", _
   Position:=msoBarPopup)
'   ‘ This displays the shortcut menu bar.
'   ‘ 200, 200 refers to the screen position in pixels as x and y coordinates.
   myShtCtBar.ShowPopup 200, 200
End Sub

'Note The shortcut menu bar appears empty because no controls (menu items or submenus)
'have been added to it.



'Create a command on a shortcut menu bar
'The following example code creates a new menu command that is named Item1 on the
'myShortcutBar shortcut menu bar and it runs the Code_Item1 macro when you click Item1:
Sub Shortcut_AddItem()
Dim myBar As Object
   Set myBar = CommandBars("myShortcutBar")
   With myBar
      .Controls.Add(Type:=msoControlButton, before:=1).Caption = "Item1"
      .Controls("Item1").OnAction = "Code_Item1"
   End With
   myBar.ShowPopup 200, 200
End Sub

'Disable a command control on a shortcut menu bar
'The following example code disables the Item1 command that you created in the
'"Create a command on a shortcut menu" section:
Sub Shortcut_DisableItem()
   Set myBar = CommandBars("myShortcutBar")
   myBar.Controls("Item1").Enabled = False
   myBar.ShowPopup 200, 200
End Sub

'Note To enable the disabled item, set the Enabled property to True.
'Delete a command on a shortcut menu bar
'The following example code deletes the menu command that is named Item1 on the
'myShortcutBar shortcut menu bar:
Sub Shortcut_DeleteItem()
   Set myBar = CommandBars("myShortcutBar")
   myBar.Controls("Item1").Delete
   myBar.ShowPopup 200, 200
End Sub

'Delete a shortcut menu bar
'Deleting the shortcut menu bar removes all the items. You cannot restore a deleted
'custom menu bar. To restore it, you must re-create it and all the menu items and
'the submenus.
'The following example code deletes the myShortCutBar shortcut menu bar that you created in the "Create a command on a shortcut menu bar" section:
Sub Shortcut_DeleteShortCutBar()
   CommandBars("MyShortCutBar").Delete
End Sub

'Restore a command on a built-in shortcut menu bar
'The following example code restores the default commands on the worksheet Cell
'shortcut menu bar:
Sub Shortcut_RestoreItem()
   CommandBars("Cell").Reset
End Sub


'Submenus on shortcut menus
'You can create submenus on shortcut menu bars. Submenus appear to the side of the
'parent menu when you click a command control. A command that is a submenu control
'has a small, black arrow that is located to the right of its name.
'Create a new submenu on a shortcut menu bar
'The following example adds a new submenu that is named NewSub on the worksheet
'Cell shortcut menu:
Sub ShortcutSub_Create()
   CommandBars("Cell").Controls.Add(Type:=msoControlPopup, before:=1) _
   .Caption = "NewSub"
    ' This displays the shortcut menu bar.
    ' 200, 200 refers to the screen position in pixels as x and y coordinates.
   CommandBars("Cell").ShowPopup 200, 200
End Sub

'Note The submenu is empty because no menu items have been added to it.
'Create a command control on a submenu that is located on a shortcut menu bar
'The following macro adds the SubItem1 command to the submenu NewSub that you
'created on the Cell shortcut menu, and then runs the Code_SubItem1 macro when you
'Click SubItem1:
Sub ShortcutSub_AddItem()
Dim newSubItem As Object
Set newSubItem = CommandBars("Cell").Controls("NewSub")
   With newSubItem
      .Controls.Add(Type:=msoControlButton, before:=1).Caption = "subItem1"
      ' This will run the subItem1_Code macro when subItem1 is clicked.
      .Controls("subItem1").OnAction = "Code_subItem1"
   End With
   ' This displays the Cell shortcut menu bar.
   ' 200, 200 refers to the screen position in pixels as x and y coordinates
   CommandBars("Cell").ShowPopup 200, 200
End Sub

'Disable a submenu item control on a shortcut menu
'The following example code disables the SubItem1 command on the NewSub submenu:
Sub ShortcutSub_DisableItem()
   CommandBars("Cell").Controls("NewSub") _
   .Controls("subItem1").Enabled = False
   ' This displays the Cell shortcut menu bar.
   ' 200, 200 refers to the screen position in pixels as x and y coordinates.
   CommandBars("Cell").ShowPopup 200, 200
End Sub

'Note To enable a disabled item, set the Enabled property to True.
'Delete a submenu item control on a shortcut menu
'The following example deletes the SubItem1 command on the NewSub submenu:
Sub ShortcutSub_DeleteItem()
   CommandBars("Cell").Controls("NewSub").Controls("subItem1").Delete
   ' This displays the Cell shortcut menu bar.
   ' 200, 200 refers to the screen position in pixels as x and y coordinates.
   CommandBars("Cell").ShowPopup 200, 200
End Sub

'Disable a submenu control on a shortcut menu
'The following example code disables the NewSub submenu on the Cell shortcut menu bar:
Sub ShortcutSub_DisableSub()
   CommandBars("Cell").Controls("NewSub").Enabled = False
   ' This displays the Cell shortcut menu bar.
   ' 200, 200 refers to the screen position in pixels as x and y coordinates.
   CommandBars("Cell").ShowPopup 200, 200
End Sub

'Note To enable a disabled item, set the Enabled property to True.
'Delete a submenu control on a shortcut menu
'The following example code deletes the NewSub submenu that you created on the
'Cell shortcut menu bar:
Sub ShortcutSub_DeleteSub()
   CommandBars("Cell").Controls("NewSub").Delete
   ' This displays the Cell shortcut menu bar.
   ' 200, 200 refers to the screen position in pixels as x and y coordinates.
   CommandBars("Cell").ShowPopup 200, 200
End Sub