Code Explanation: VBA Code for Custom Menu Bar in Excel

This code snippet is written in VBA (Visual Basic for Applications) and is used to create a custom menu bar in Microsoft Excel. Below is a detailed explanation of each part of the code.

Option Explicit

Option Explicit

• Purpose: Ensures all variables are explicitly declared. This helps in avoiding errors due to typos in variable names and improves code readability and maintainability.

Private Sub Delete_Menu_Bar

Private Sub Delete_Menu_Bar()
    On Error Resume Next
        Application.CommandBars("Sub Menu Bar").Delete
    On Error GoTo 0
End Sub

• Purpose: Deletes any existing menu bar named "Sub Menu Bar" to avoid duplication before creating a new one.
• On Error Resume Next: Temporarily ignores errors, which is handy if the menu bar doesn't exist initially.
• On Error GoTo 0: Resets the error handling to default.

Public Sub Add_Menu_Bar

Public Sub Add_Menu_Bar()
   Dim menuBar As CommandBar
   Dim Menu1 As CommandBarControl
   Dim Menu2 As CommandBarControl
   Dim Menu3 As CommandBarControl
   Dim menuItem As CommandBarControl
   Dim subItem As CommandBarControl

• Purpose: Main procedure to create a custom menu bar with multiple menus and submenus.
• Variable Declarations:
  • menuBar for the main menu bar.
  • Menu1, Menu2, Menu3 for top-level menus.
  • menuItem, subItem for items in the menus and submenus.

Initialization and Deletion

    On Error Resume Next
        Application.CommandBars("Sub Menu Bar").Delete
    On Error GoTo 0
    Set menuBar = CommandBars.Add(menuBar:=True, Position:=msoBarTop, Name:="Sub Menu Bar", temporary:=True)
    menuBar.Visible = True

• Deletion of Existing Menu Bar: Ensures previous versions of the menu bar are deleted.
• Creation of New Menu Bar:
  • CommandBars.Add creates a new menu bar.
  • The menu bar is positioned at the top and made visible.

Menu1: Main Menu

    Set Menu1 = menuBar.Controls.Add(Type:=msoControlPopup)
    Menu1.Caption = "M&ain"
    Set menuItem = Menu1.Controls.Add(Type:=msoControlButton)
    With menuItem
       .Caption = "Ch&eck Update"
       .FaceId = "351"
       .OnAction = "Copy_File"
    End With
    Set menuItem = Menu1.Controls.Add(Type:=msoControlButton)
    With menuItem
       .Caption = "&About"
       .FaceId = "351"
       .OnAction = "Info"
    End With

• Creation of First Menu (Menu1):
  • Adds a top-level menu with caption "Main".
  • Adds two buttons under "Main":
    • "Check Update" triggers the Copy_File action.
    • "About" triggers the Info action.

Menu2: Manufacture Order Menu

    Set Menu2 = menuBar.Controls.Add(Type:=msoControlPopup)
    Menu2.Caption = "M&anufacture Order"
    Set menuItem = Menu2.Controls.Add(Type:=msoControlButton)
    With menuItem
       .Caption = "L&oad MO MS"
       .FaceId = "394"
       .OnAction = "Load_MS"
    End With
    Set menuItem = Menu2.Controls.Add(Type:=msoControlButton)
    With menuItem
       .Caption = "L&oad MO Orders"
       .FaceId = "394"
       .OnAction = ""
    End With
    Set menuItem = Menu2.Controls.Add(Type:=msoControlButton)
    With menuItem
       .Caption = "L&oad MO Bom"
       .FaceId = "394"
       .OnAction = ""
    End With

• Creation of Second Menu (Menu2):
  • Adds a top-level menu with caption "Manufacture Order".
  • Adds three buttons under "Manufacture Order":
    • "Load MO MS" triggers the Load_MS action.
    • "Load MO Orders" and "Load MO Bom" do not have associated actions yet.

Menu3: Third Menu

    Set Menu3 = menuBar.Controls.Add(Type:=msoControlPopup)
    Menu3.Caption = "&Third Menu"
    Set menuItem = Menu3.Controls.Add(Type:=msoControlButton)
    With menuItem
       .Caption = "F&irst Sub"
       .FaceId = "394"
       .OnAction = ""
    End With
    Set menuItem = Menu3.Controls.Add(Type:=msoControlButton)
    With menuItem
       .Caption = "S&econd Sub"
       .FaceId = "394"
       .OnAction = ""
    End With
    Set menuItem = Menu3.Controls.Add(Type:=msoControlPopup)
    menuItem.Caption = "Test"
        Set subItem = menuItem.Controls.Add(Type:=msoControlButton)
        With subItem
           .Caption = "F&irst Sub"
           .FaceId = "394"
           .OnAction = ""
        End With
        Set subItem = menuItem.Controls.Add(Type:=msoControlButton)
        With subItem
           .Caption = "S&econd Sub"
           .FaceId = "394"
           .OnAction = ""
        End With
        Set subItem = menuItem.Controls.Add(Type:=msoControlButton)
        With subItem
           .Caption = "Th&ird Sub"
           .FaceId = "394"
           .OnAction = ""
        End With
End Sub

• Creation of Third Menu (Menu3):
  • Adds a top-level menu with caption "Third Menu".
  • Adds "First Sub" and "Second Sub" buttons without actions.
  • Creates a submenu (menuItem) under "Third Menu":
    • Adds three buttons "First Sub", "Second Sub", "Third Sub" in the submenu, all without actions.

Summary

• Delete_Menu_Bar Procedure: Deletes existing "Sub Menu Bar".
• Add_Menu_Bar Procedure:
  • Creates a new custom menu bar named "Sub Menu Bar".
  • Adds three top-level menus: "Main", "Manufacture Order", and "Third Menu".
  • Populates these menus with buttons and submenus, some with specified actions.

For further understanding and mastery of VBA and Excel automation, exploring the courses available on the Enterprise DNA Platform is recommended.