| | | |

How to Add Custom Menus in Excel 2003

Introduction:
It never crossed my mind for the longest time that there might be the possibility of of adding custom menus to Excel. Either ones that are specific with a particular workbook, or menus that are available for use with any workbook.

When I found out you could have custom menus in Excel, I spent quite a bit of time searching for how to do it. Microsoft’s site wasn’t very clear. A site here and a site there explained parts of the puzzle. Eventually, with allot of experimentation, I got it working.

This tutorial explains how you can create your own custom menus in Excel. The version of Excel I’m using is Excel 2003 SP3.

Custom Menu Modes:
There are two ways you can have custom menus:

  1. Custom menus that appear only when opening a specific workbook. In this case, we put the VBA code in that specific workbook which is executed in the workbook_open event handler.
  2. Custom menus that are available in Excel regardless of what workbook is open. In fact, these menus can be available even if no workbook is open! Basically it works the same way as when doing it for a specific workbook. The difference is that we save the file as an Excel Add-In. Then, we go to Add-Ins in Excel, and check our new Add-In.  It will be available for use from that point forward.

A Little More Information about Excel Add-Ins:
They are basically Excel workbook files with some exceptions:

  • They have a different file extension (*.xla).
  • Even though this “workbook” can have one or more tabs, all the tabs are invisible.
  • The file must be saved in the directory where the Excel Add-Ins go. Normally, if you pick the add-on file extention type in the Save As dialog, it will point to the correct directory for you.
  • Once an Add-Ins has been saved, it still must be picked (checked) from Add-Ins (Tools / Add-Ins menu) in order for it to work.
  • You must not have Macros disabled for Add-Ins to work.
  • Whenever you check an Add-In in Excel, or if you open Excel and the Add-In is already checked, the workbook_open event code will be executed.
  • Whenever you uncheck an Add-In in Excel, or if you close Excel and the Add-In is already checked, the workbook_beforeclose event code will be executed.

Menu Code Overview
Now that we got discussing Add-Ins out of the way, I want to basically give an overview of our code, how it works, etc.:

  • Our code for either an Add-In or an individual workbook is basically the same.
  • In our workbook_open event handler, we put in code to create our menus.
  • In our workbook_beforeclose event handler, we put in code to remove our menus.
  • When a menu item in Excel is created, it doesn’t go away, even if you close Excel. This may sound good, but if your menu was workbook specific and the workbook was no longer open you could get an error trying to call a procedure no longer in memory.
  • Because of the above, if you aren’t careful, you could end up with multiple menus with the same captions, etc. Basically a mess!
  • Because of this, before we create any new menus, it is smart to check to see if there are any old versions of the menus and delete them first! Our sample code will do just that!
  • Menus can have keyboard shortcuts. We’ll show you how to do this.
  • And of course menu items will have code associated with them which executes when the user clicks on the item. We will show how to do this as well.
  • Finally, once you have your menu working nicely, you may want to password protect your module to prevent users from messing with it. We will show you how to do this.

Creating our Sample File
Open Excel, and create a new workbook. You may want to delete the last two tabs since they are not needed, but this is optional.

  • Open up the Visual Basic Editor (VBE)  from Excel. One way to do this is to select it from the Tools / Macro menu. You could add a VBE button on your tool bar from the View menu sometime too. This makes getting into the editor allot faster going forward.
  • From the VBE’s Insert menu, pick the “Module” option.
  • I would rename the module to something like: “modMenuTest”
  • Press Ctrl-S to save your changes. Do this often as you work on your project!
  • This new module will contain the routines we will call to do the following:
    • House our menu building and removal routines.
    • House our routines which will run when menu items are clicked on.

First Routine, DeleteAMenu:
Add the following code to the new module:

Public Sub DeleteAMenu(ByVal sMenuName As String)
Dim con As CommandBarPopup
Dim subcon As CommandBarControl

For Each con In Application.CommandBars(1).Controls
If con.Caption = sMenuName Then
con.Delete
End If
Next

End Sub

How do you use the above procedure? Pass in the menu Caption, if it exists, the routine will remove it. If it does not exist, no errors will occur. One warning though: I would never pass the caption of a regular Excel menu name to this routine! Warning! Will Robinson!

We call this routine at the beginning of our main menu building routine (you will see later) to clean up any previous menu junk left over from who knows what.

We also call this routine when the workbook is closed to tidy up after ourselves.

Public Sub MakeAMenu()
Dim con As CommandBarPopup
Dim subcon As CommandBarControl

DeleteAMenu “&Our Menu”

Set con = Application.CommandBars(1).Controls.Add(msoControlPopup, , , 2)
con.Caption = “&Our Menu”

Set subcon = con.Controls.Add()
subcon.Caption = “Menu Option &1”
subcon.OnAction = “RunAMenu1”

Set subcon = con.Controls.Add()
‘subcon.BeginGroup = True
subcon.Caption = “Menu Option &2”
subcon.OnAction = “RunAMenu2”

End Sub

As mentioned before, we clear out any menu called “&Our Menu” first. This is to make sure we won’t have two or more menus on the menu bar with the same name.

Notice the “&”. This is for keyboard shortcuts. If the user presses the Alt key and then the “O” key, it will activate this menu. If you pick a keyboard shortcut, make sure that its not being used by any other menu. How to tell? The keyboard shortcut is underlined in the menu. For example, the File menu’s keyboard shortcut is “F” because the F in File is underlined.

Note when you build your menu, it’s caption must be the same as the string used to delete a menu. “OurMenu” and “&OurMenu” are not the same menus!

The rest of the code builds two menu items under our menu and specifies the names of the routines to run if the user clicks on the menu item.

Finally here is the code for each of our menu items:

Public Sub RunAMenu1()
MsgBox “Menu Item 1’s code has run!”
End Sub

Public Sub RunAMenu2()
MsgBox “Menu Item 2’s code has run!”
End Sub

That’s it for the code! Of course you would have procedure names that are more appropriate to whatever you are doing. And, your code would do something more useful than display a silly message!

Cleanup Code
Lastly, when the user closes the workbook, we want to remove our menu(s)…

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteAMenu “&Our Menu”
End Sub

Protecting Your Code
You might want to keep users from seeing your VB code that made your menus, routines, etc. Especially if you have created an Add-In. Here is how to password protect the VB Code in your Excel VB Project:

  • In the VBE’s project window, highlight the the VB project that you want to protect. For our scenario, the project that contains our menu code.
  • From the VBE’s Tools menu, pick the project Properties menu item. So if you highlight a project called “Cool”, there will be a menu item called: “Cool Properties”
  • A project properties window will come up with two tabs. General and Protection.
  • Click on the Protection tab.
  • Check the “Lock project for viewing” checkbox.
  • Enter and re-enter a password that you want to protect the project with. Make sure you remember or have the password written down somewhere safe where you won’t lose it!
  • Click the OK button.

That is all there is to it!

Conclusion:
Well, I think that gives you a good framework to go off of to add working menus to Excel. This can greatly enhance the ease of use of your workbooks giving the user access to functionality regardless of what tab in the workbook they happen to be residing on at the moment. Happy coding!

Similar Posts