Excel - Adding shortcut Menu items

There are a dozen or so Excel files which I have to open repeatedly, add small bits (such as account or work tracking) and save. It makes life much more efficient and less tiresome to have quick links to these files built into a custom Excel menu. The process isn't obvious, especially being obscured by some required dragging which is hard to stumble upon. So here's my step-by-step guide.

Precaution: your setup may not allow you to store stuff in Personal.xls. The following is all a waste of time if you haven't got that enabled.

These instructions are for Excel 2003 and it's not that different for Excel 2007.

1. Make the Macro

Open Excel and prepare a one-line macro to open the target sheet as follows:

Use the menu option Tools > Macro > Visual Basic Editor or Alt-F11. You get the potentially scary VBA environment, but don't worry, you won't be here long.

Find the VBA Project window, and, if there is not already a "Module1" listed (or similar) then choose from the menu Insert > Module.

If the window is not already open (labelled "PERSONAL.XLS - Module1 (Code)") then double-click the label "Module1" in the VBAProject Explorer, outlined above. In that window, type in some code like the following (but amending the locations to suit yourself of course). Sub OpenAccounts2007() Workbooks.Open Filename:="F:\Excel\Acc2007.xls" End Sub

Save and Close. Get back to Excel.

2. Preparing the Menu bar

The very first time you do this, you need to put one item on the main bar. This item will form the "parent" of a tree of menu items. Having completed this once, you don't need this step when adding specific File-open macros.

Move the pointer over the menu bar, just to the right of "Help" . Right-click and choose "Customize" at the bottom of a long list of potential menu bars.

From the list of categories (left list box) choose "New Menu" at the bottom, then you are shown just one option "New Menu" in the Commands (right list box).

Here comes the non-obvious interface bit: drag this "New Menu" command from its source place, up to the main menu bar, just to the right of the "Help" standard item. The icon will change to add a plus sign in the hope that you recognise official Microsoft approval for this unusual action. But it shifts to an X while you fly over the enemy territory of the work area, then shifts back to a plus sign at the friendly menu bar. Drop it there.

This new menu parent will predictably name itself "New Menu". Right-click over it and choose the "Name:" option, type in a name to suit yourself.

3. Join the Macro to the Menu

Next task is to associate the File Open snippet of code with a new specific menu link.

Returning to the "Customize" dialog box again, in the Categories list, click "Macros" (near the bottom). In the Commands list, click and hold "Custom Menu Item". Drag this up to your new menu parent. The moment you hover over the parent, still holding down the left-mouse button, then some space will drop down below the parent. Move down to this space and then let go of the mouse button. This "drops" your Custom Menu Item onto your custom parent. Phew.

Because you have still got the Customize dialog open, the menu-tree remains open dangling below your custom parent. The new item provisionally calls itself "Custom Menu Item" so right-click and name it suitably using the "Name:" option.

Then right-click again on the new custom item (not the new parent) and find "Assign Macro.". Click this and you are shown the names of functions & subs in you VBA module. Choose the one that you created way back in Step 1 part 3 (seems like a lifetime ago).

Click "OK" and Close.

All your efforts above will get saved automatically in your Personal.xls. The next time you open Excel, you'll get your spanking-new custom menu and be taking your first steps on the road to Excel efficiency.