Custom menu command

Here are some (but not all) of the ways to run a command macro from the Macro dialog box, as described in this chapter by means of a shortcut key, as described in this chapter by means of a custom menu command, as described in Chapter 18 by means of a custom button on a toolbar, as described in Chapter 19 by means of a button on a worksheet, as described in Chapter 8 as a subroutine called by another macro, as described in Chapter 15... 

If you have written a number of related macros, you may even decide to create a custom menu bar with several custom menus, each containing custom commands. While both "Custom Menu Command" and "New Menu" can be... 

To add a custom menu command to a menu, use the procedure in the following box. 

To Add a Custom Menu Command to a Menu Bar and Assign a Macro to It... 

In the shortcut menu, type the text for the new menu command in the Name input box (the default text is " Custom Menu Command"). 

If you create a macro to be used by other people, you can make it easy for them to use the macro by installing a custom menu command. As well, you can install the new menu command by means of an automatic procedure, so that all the users have to do is open the macro document. There are two ways you create an automatic procedure by creating an Auto Open macro in a module sheet, or by creating a Workbook Open event procedure. These are described in the sections that follow. 

Figure 18-2 illustrates an Auto Open procedure that installs a new menu command in the Tools menu and assigns the Sub procedure named CommandHandler to it. To install your own custom menu command in a menu, replace Tools, New Menu Item and CommandHandler with the name of the menu, the text of your menu command and the name of your macro. 

Figure 18.2 An Auto Open Sub procedure to install a custom menu command.

Type is msoControlButton. Id and Parameter can be omitted see VBA On-line Help for an explanation of the use of these arguments. If Before, which specifies the position in the menu, is omitted, the command is added at the end of the menu. Set Temporary to True to make the custom menu command temporary temporary menu commands remain in the menu until you exit from Excel. 

Figure 18.4. The Sub procedure that runs when the custom menu command is chosen...

You can modify existing menus or menu bars by using the Customize command in the Tools menu. Each version of Excel has used a different method to customize menus Excel 5/95 used the Menu Editor to modify menus. Excel 97 displayed a customizable copy of the menu bar in which to make changes, but in Excel 2000 menus are customized in exactly the same way as toolbars — by dragging the menu command to the desired place on the menu bar. 

In previous chapters you learned how to create useful Sub procedures. You may find that you use a particular macro so often that it would be more convenient to have it on one of Excel s drop-down menus, rather than having to "run" it by means of the Run command from the Macro menu. Running a command macro by means of an Excel menu command makes the custom command accessible to Excel users who are not familiar with the use of macros. 

You can install either custom menus or any of Excel s built-in menus on your custom menu bar. To install custom menus, follow the procedure in the following box to install any of Excel s built-in menus, follow the same procedure but choose "Built-in Menus" from the Categories box. You can apply the same methods used for custom menus to add commands to, remove commands from, or rename the built-in menus. Menus installed on a custom menu bar are displayed with a drop-down arrow next to the menu name. Figure 18-1 shows such a custom menu bar. 

Excel Tip If you add a built-in menu to a toolbar and make changes to the menu, the changes will also appear in the built-in menu on the menu bar. Thus if you want a modified File menu on a custom menu bar, you should add a custom menu, rename it File, and then add the desired menu commands. 

Select Custom Menu Item and drag it to the desired location on the menu (a black bar will show where the command will be placed). 

In contrast to changes to menus made by using the Customize... command, menus and menu commands installed by using VBA are not saved when you quit Microsoft Excel. 

You may want to install a new menu command immediately above or below a menu command that is already present in the menu. Since menus can be customized, you shouldn t use a numerical value for the position instead, determine the position of the command programmatically. Use the Index property as shown in Figure 18-5. 

The data reduction program can be driven interactively from the console terminal or in batch mode from a command file created by the customizing function on the auxiliary functions menu. 

Field testing and sampling maybe handled in the generic plan by presenting a menu that covers all potential options available to the utility, based on both internal and external capabilities. In developing a customized plan, the incident commander can simply check off the field tests and sampling requirements that are appropriate for the specific situation. The site characterization plan may also need to be revised in the field based on the observations of the team. 

A significant change was made in the appearance of Excel s menus in Excel 2000. The default for menus is that they display recently used commands first, with a drop-down button at the bottom of the menu to display the remaining commands. If you prefer to work with "old style" menus, choose Customize... from the Tools menu, choose the Options tab and uncheck the Menus Show Recently Used Commands First box (Figure 1-9). 

In this chapter you ll learn how to add a new command to a menu, how to add a new menu to a menu bar and how to create a whole new menu bar with separate menus and commands. In this way you can add new capabilities to Excel or even create a complete custom application. 

Menu and Tbolbar— These are standard Windows features that you are probably comfortable using. The menus contain very nearly all of the commands that control AQD. The toolbar contains a smaller subset of more frequently used commands and can be customized to display your choice of buttons. 

---