Create Customize Excel Menu

I have to apologize that I put off the supposedly next password article for this one. I must discuss this one first because the next password article will need this program module routines. I will be discussing here how to create a customize menu that we can insert in the existing Excel menu. The menu can be used in your program applications in many ways so it is something that you should know when developing Excel programs.

So let's get started to create our own customized menu.

Step 1: Open you Excel file and run the Visual Basic Editor (VBE) by clicking Tools>Macro>Visual Basic Editor.

Step 2: When VBE appears, add a new module by clicking Insert>Module at the VBE menu.

Step 3: At the new module, enter the following code snippets:


Sub PutProBusinessMenu()
    Dim cmdControl As CommandBarControl
    Dim cmdbarMenu As CommandBar
    Dim cmdControlMenu As CommandBarControl
    Dim intHelpMenuItem As Integer

    ClearProBusinessMenu

    Set cmdbarMenu = _
    Application.CommandBars("Worksheet Menu Bar")
     
    intHelpMenuItem = _
    cmdbarMenu.Controls("Help").Index
     
    Set cmdControlMenu = _
    cmdbarMenu.Controls.Add(Type:=msoControlPopup, _
    Before:=intHelpMenuItem)
                  
    cmdControlMenu.Caption = "&Pro-business"
     
    With cmdControlMenu.Controls.Add( _
    Type:=msoControlButton)
        .Caption = "Execute Process"
        .OnAction = "ExecuteProcess"
    End With
End Sub

Sub ClearProBusinessMenu()
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar"). _
    Controls("&Pro-business").Delete
    On Error GoTo 0
End Sub

Sub ExecuteProcess()
    MsgBox "Process is executed."
End Sub

The first snippet is about creating the menu before the Help option. The second one is deleting it from the menu. You need the second snippet to clear your own menu when the file is closed or when the user move from your workbook to another workbook that doesn't use your program. The next step is about doing that.

The last snippet is the module to be executed when the option in our new menu is executed. This part is where you put your own code when you want to use this program in your own application.

Step 4: Click ThisWorkbook item in the Project window at the left side of your VBE. When the ThisWorkbook module opens up and enter the following code snippets.


Private Sub Workbook_Activate()
    PutProBusinessMenu
End Sub

Private Sub Workbook_Deactivate()
    ClearProBusinessMenu
End Sub

Step 5: Save your Excel file, close it and try to open it again. You will see that you have the new menu right before the Help in the main menu. Open up another Excel file and try to switch between our Excel file and the other one. You will see that the menu appears only when our Excel file is active.

That's how to do it. Next time, as promised, the Excel password protection for your Excel and VBA program. We will put a password protection to this customized menu and we will discuss so much more about Excel program security.

Subscribe now to see the next topic by clicking the link below: