VBA Code Documentation

Overview

This VBA code provides two primary functions:

1. Delete_Menu_Bar: Deletes an existing custom command bar named "Sub Menu Bar".
2. Add_Menu_Bar: Creates a custom command bar, adds menu items to it, and assigns actions to these items.

Functions

Delete_Menu_Bar

This function deletes the "Sub Menu Bar" command bar if it exists. It handles errors gracefully using On Error Resume Next.

Signature

Private Sub Delete_Menu_Bar()

Details

• Purpose: Deletes the "Sub Menu Bar" if it exists.
• Usage: Typically called before adding a new command bar to ensure no duplicates exist.

Example Usage

Call Delete_Menu_Bar

Implementation

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

Add_Menu_Bar

This function creates a new command bar named "Sub Menu Bar" and adds menu items to it. Each menu item can be assigned its own action.

Signature

Public Sub Add_Menu_Bar()

Details

• Purpose: Creates a custom command bar and adds menu items with specific actions.
• Parameters: None.
• Return Type: None.
• Exceptions: None explicit, but uses On Error Resume Next to handle errors.

Example Usage

Call Add_Menu_Bar

Implementation

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
    
    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

    ' Adding first main menu
    Set Menu1 = menuBar.Controls.Add(Type:=msoControlPopup)
    Menu1.Caption = "M&ain"

    ' Adding a button to the first main menu
    Set menuItem = Menu1.Controls.Add(Type:=msoControlButton)
    With menuItem
        .Caption = "Ch&eck Update"
        .FaceId = 351
        .OnAction = "Copy_File"
    End With

    ' Additional menus and submenus can be added here
    ' Each menu item and submenu should follow a similar structure:
    ' Set MenuX = menuBar.Controls.Add(Type:=msoControlPopup)
    ' MenuX.Caption = "Caption Text"
    ' Set subItem = MenuX.Controls.Add(Type:=msoControlButton)
    ' With subItem
    '     .Caption = "Submenu Item Caption"
    '     .FaceId = FaceIdNumber
    '     .OnAction = "ActionName"
    ' End With

End Sub

Inline Comments

• On Error Resume Next and On Error GoTo 0: These lines handle potential errors that might occur during the deletion of an existing command bar, preventing the code from breaking.
• CommandBars.Add: Creates a new command bar with specified settings (position, name, and visibility).
• menuBar.Controls.Add: Adds controls (buttons, menus) to the command bar.

Notes

• Customization: The captions, FaceId, and OnAction can be updated as per the requirements.
• Best Practice: Always ensure Delete_Menu_Bar is called before Add_Menu_Bar to avoid duplication of command bars.
• Further Learning: For more advanced customization and handling, consider taking courses available on the Enterprise DNA Platform.

This documentation provides a clear and comprehensive guide to understanding and using the provided VBA code snippets.