Chomer.com

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:
Read more