Menu command macro

The SolvStat.xls macro is an Auto Open macro when you Open the document it will appear on screen and then Hide itself. It installs a new menu command. Solver Statistics..., directly under the Solver... command in the Tools menu. If the Solver Add-In has not been loaded, the Solver Statistics... command will be at the top of the menu. The command will remain in the menu imtil you exit from Excel. 

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 didn t assign a shortcut key to the macro when you recorded it, but would like to do so "after the fact", choose Macro from the Tools menu and Macros... from the submenu. Highlight the name of the macro in the Macro Name list box, and press the Options... button. You can now enter a letter for the shortcut key CONTROL+(key) or SHIFT+CONTROL+(key) in Excel for Windows, OPTION+COMMAND+(key) or SHIFT+OPTION+COMMAND+(key) in Excel for the Macintosh. 

A command macro (a Sub procedure) can automate any sequence of actions that can be performed by the use of menu commands or keystrokes. Many simple but useful command macros can be created entirely by using the Recorder, as described in Chapter 13. But command macros can also carry out much more complicated actions. In this chapter we ll look at some examples of creating more advanced Sub procedures. 

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. 

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 Bar and Assign a Macro to It... 

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. 

ADDING A MENU COMMAND BY MEANS OF AN AUTO OPEN MACRO... 

To install a new menu command whenever Excel is started, create an Auto Open procedure in the Personal Macro Workbook. To install a new menu command when a particular workbook is opened, create an Auto Open procedure in that workbook. Use the MenuBars and Menus VBA keywords to specify where the new Menultem is to be added, with the Add keyword. The syntax of the Add method is ... 

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. 

ADDING A MENU COMMAND BY MEANS OF AN AUTO OPEN MACRO In Excel 5/95, any macro with the name Auto Open will be run automatically whenever the workbook containing that macro is opened. You can still employ Auto Open procedures in Excel 97 or Excel 2000. 

Choose Record New Macro... from the Macro menu, and CONTROL-i-a (Windows) or OPTION+COMMAND+a (Macintosh) as the shortcut key. 

Macro In" box should display This Workbook if not, choose This Workbook. Enter "e" in the box for the shortcut key, then press OK. The Macro Stop toolbar will appear (Figure 13-6), indicating that a macro is being recorded. If the Macro Stop toolbar doesn t appear, you can always stop recording by using the Tools menu in the Macro submenu the Record New Macro... command will be replaced by Stop Recording. 

The text of the menu item that will appear in the menu is Caption. The name of the macro that runs when the new menu item is selected is OnAction. ShortcutKey is available only for Macintosh. If Before, which specifies the position in the menu, is omitted, the command is added at the end of the menu. 

---