A La Carte Menus

Excel allows you to customise its menus giving you the power to can add or remove menu choices from your spreadsheets.

This is a another useful technique for restricting a user from undesirable menu choices and for adding your own menu items to your applications.

The method by which this achieved is quite different to that used in earlier versions of Excel.

In Excel 97 the menu bar is treated as if it is a toolbar so the techniques used to change it are similiar to those used in changing toolbars.

Let's look at an example.

Let's suppose we want to add a Menu choice to the Main menu bar.

We will make a dialog box similiar to the one below appear when we select our new menu choice.

menumess

Step 1 - Create the macro you want executed when the menu item is selected

In this case we will just display a message box.


Sub aboutme()
  resp=Msgbox("Thank you for selecting my menu item",0,"Hi There")
end sub

Step 2 - Customising the Menus

Select Tools Customize from the menu bar.

From the Toolbars tab select Worksheet Menu Bar

menu1

Now select the Commands Tab

In the Categories box scroll down to the New Menu option and select it.

menu2

In the Commands box a New Menu item appears.

Select the New Menu item.

Holding the mouse button down over this item will cause a button to appear, you should drag and drop this button onto the main Excel menu bar.

Your menu should look similiar to the graphic below.

menu3

We can now change the name of the New Menu item to About Me.

From the Customize dialog box select Modify Selection and change the Name property to About Me

menu4

The final step is to add the required action to the menu item by assigning a macro.

Use the Assign Macro to assign the Aboutme macro to the new menu item.

Press OK to complete the assignment

Press the Close button to close the Customize dialog box.

Now select the About Me menu option that should now be available from the menu. That's it.

When writing your own menu driven systems remember to return the menus to their original state when you have finished with them.

There is a Reset button on the Customize dialog box to do this.