You can find the Excel tips you're looking at Pro-business Excel VBA Programming by entering the keyword in the textbox below and clicking the "Google Search" button.

Sunday, May 11, 2008

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


Set cmdbarMenu = _
Application.CommandBars("Worksheet Menu Bar")

intHelpMenuItem = _

Set cmdControlMenu = _
cmdbarMenu.Controls.Add(Type:=msoControlPopup, _

cmdControlMenu.Caption = "&Pro-business"

With cmdControlMenu.Controls.Add( _
.Caption = "Execute Process"
.OnAction = "ExecuteProcess"
End With
End Sub

Sub ClearProBusinessMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar"). _
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()
End Sub

Private Sub Workbook_Deactivate()
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:

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.