When the slash key (/) is pressed, the control panel displays the main command menu, or the list of eleven different commands, beginning with Worksheet and ending with Quit. While working with a worksheet, all eleven commands may not be needed by the user. Lotus 1-2-3 permits the user to develop specialized menus that are driven by macros.

A macro menu (hereafter called a custom menu) is a menu created along with the current worksheet and operates just like the main command menu. it allows the automation of such tasks as entering data, printing, saving, and virtually any other task a user may wish to perform. The custom menu is executed by pressing the macro key (Alt on most computers) and a designated letter such as M for menu. A custom menu will be displayed at the control panel just as all previous menus have been. Once in the custom menu, an option or command is selected by pressing the first letter of the option or by moving the cell pointer to the option and then, pressing Enter.

The procedure for creating a custom menu is to set up the range with menu options and with the corresponding macro commands. The menu range consists of at least three rows: the first row contains the options, the second row contains the explanation of each option, and the remaining rows contain the macro instructions to be executed if the option is selected.

Building the Worksheet

Before creating the custom menu, build the three month income and expense worksheet as shown in Figure 1. Start off by doing die following:

* If necessary, clear the current worksheet by selecting: / Worksheet Erase Yes;

* Set the global format to comma and zero decimal places by selecting: / Worksheet Global Format, 0 Enter;

* Set Column B's width to 24 by selecting: / Worksheet Column Column- Range Set-width Bl Enter 24 Enter;

* Set C and D's column widths to 14, by selecting: / Worksheet Column Column-range Set-width C1..D1 Enter 14 Enter.

Type the labels as shown. Remember that cells E2, E3, B5, C5, D5, and E5 will contain formulas and not labels. Rows 4 and 6 are repeating labels " \ - " and "/=," respectively. Right align the labels in row 1 by selecting / Range Label Right Bl..el Enter.

Creating the Custom Menu

For illustration purposes, the macro code used to build the custom menu will be placed right below the worksheet. Normal practice is to place the custom menu in the lower right quadrant of the worksheet, outside the working area of the worksheet.

Starting in cell A10, carefully enter the additional information as shown in Figure 2. As the macros or commands are entered, remember to enter macro code as labels by preceding each slash (/) with an apostrophe. In addition, the backslash (\) in cell AIO must be preceded by an apostrophe. Notice that in many instances, a tilde ( ), is entered; this symbol represents pressing the Enter key. Do not skip any columns or rows to make room for any entries. For now, do not worry if long labels overlap into an adjacent cell. Explanations for each entry will follow.

Before a macro can be invoked, it must be named. To name the macros in cells B10 and B13, move the cell pointer to cell A10 and select / Range Name Labels Right A10..al3 Enter. Issuing these commands, assigns two cells in column B with the two names in column A. Hence, cell B10 has been named \M and cell B'3 has been named MENU. Save the worksheet as "INC-EXP".

What's on the Menu

The macro command that allows the user to create the custom menu is MENUBRANCH location. In this instance, when die program encounters the MENUBRANCH MENU command in cell B10, it displays the custom menu at the control panel, based on the contents of the range whose upper left corner is the location. The location, the cell named MENU or cell B13 is where the custom menu begins. When a menu option or subroutine is chosen, the macro commands are executed down the column that contains the menu option selected. Upon completion of the subroutine, the BRANCH \Ml macro command branches control back to the custom menu. Notice that documentation for the purpose of the custom menu is contained in cells C10 and c11.

Me first row (row 13) of the custom menu begins with the cell Bl3) that contains the first menu option, "DATA ENTRY." This is the cell that the MENUBRANCH MENU command branches. The other menu options are found to the right of the first option, on the same row. Cell C13 contains the second menu option, PRINT," and cell D13 contains the third menu option, "SAVE." A custom menu may have up to eight options. More advanced menus may have a number of submenus where an unlimited number of options can be created.

The second row (row 14) of die custom menu consists of a row of menu prompts. This menu prompt row must be the row immediately underneath die row of menu options. Cell B14 contains the menu prompt "Enter Data" for the first menu option "DATA ENTRY." Cells C14 and D14 contain the prompts "Print Report" and "Save Worksheet," respectively.

The third row (row 15) contains macro commands that will be automatically executed when a menu option is selected. The macro commands for "DATA ENTRY" start in cell B15 and continue down the column. The macro commands "/XNEnter income

amount"and/XNEnterexpense

amountprompttheusertoenter

numbers and store the numbers in their respective current cells. The cell pointer instructions of "RJU" (the short version of RIGHTUP in cell B18, move the cell pointer one cell to the right and up one cell which happens to be the income cell for February. The last cell in the column, B19 contains BRANCH \M which branches control back to the custom menu. In the remainder of the rows, standard commands are employed for printing a report and saving the worksheet. Upon completion of a particular menu option, control branches back to the custom menu.

While this is a very simple custom menu, proper macro coding will require that each macro code, as contained in cells B15..bl9, be entered as macro commands with its own name in a separate area, a couple of rows below the custom menu. This practice provides a more structured approach to macro menu development where adequate documentation is given and where errors are easily located and corrected.

Selecting an Option

from the Menu

If the program returns an error-message, or beeps while invoking a macro, something may have gone wrong. Press the Esc key, which will take the user out of the menu mode and back to the ready mode. In addition, pressing the Ctrl and Break keys simultaneously will also return the user to the ready mode should the Esc key fail to do so. Before preceding, make sure that all macros, labels, and formulas are entered correctly including the names of the cells B10 and B13, as described earlier. After corrections are made, try invoking the custom menu. Move the cell pointer to cell B2, January's income cell. Hold down die Alt key and then press the letter M. Absent any error message or beeps, the custom menu is displayed at the control panel right above the worksheet. Notice that the cell pointer is highlighting "DATA ENTRY," one of three menu options and at the too right of the screen, the mode indicator displays the MENU mode. At the bottom of the screen, the status indicator displays CMD indicating that a macro is in the process of execution.

Start the data entry routine. Press Enter or the letter D to start "Data Entry." The message area displays "Enter income amount." Type 5000" and press Enter. The cell pointer moves down one cell. The message area displays "Enter expense amount." Type "300" and press Enter. Of course, the profit cell, B5 should display 2,000."

Again, should the program return an error message or should any beeps sound, press either the Esc key or Ctrl key and Break keys.

Once January's data has been entered, the custom menu is redisplayed at the control panel. in addition, the cell pointer has moved to cell C2, February's income cell. The user now has the option of entering February's figures, printing a report with January's figures or saving the worksheet.

Try printing the worksheet. Press the letter P or move the cell pointer to the "Print" option and press Enter. The message area displays: "Enter print range:" Type A1..E6 for the print range and press Enter. The printer begins to print the report. when the printer finishes, the custom menu is displayed.

To save the worksheet, just press S for save. There is no need to name the worksheet because it was named earlier as "INC-EXP." As indicated by the commands in cell D15, the worksheet will be saved with its current name and replace itself. To leave or quit the custom menu just press the Esc key. Remember to start the custom menu again for another month, just move the cell pointer to the income cell for that month (C2 or D2), hold down the Alt key and press M.

Automatic Retrieval of the

Custom Menu

Any time this worksheet is retrieved, the custom menu is executed by holding down the Alt key and pressing the letter M. However, the custom menu can be further automated by using an auto-executing macro. An auto- executing macro is simply a macro that automatically runs when the worksheet is retrieved. If a macro is assigned a range name of \0 ("backslash zero"), it becomes an auto-executing macro. However the macro cannot be invoked by pressing Alt and O("zero"). When the worksheet is loaded with the File Retrieve command, the program will search for a macro contained in the worksheet with the name of \O. if the program finds a \0 macro, it will immediately execute the macro.

For example, the macro that displays the custom menu has been named \M. To make MENUBRANCH MENU an auto-executing macro, assign cell B10 a second range name of \0 by selecting: / Range Name Create \0 Enter B10 Enter. Move the cell pointer to cell B2 and save the worksheet as INC- EXP1." Then retrieve INC-EXP1" to see how the auto-executing macro works. As a result of retrieving the worksheet that contains the auto- executing macro named \O, the custom menu appears at the control panel. Thus, every time this worksheet is retrieved, the custom menu will automatically be displayed at the control panel without the user having to invoke the menu. For now, press the Esc key to leave the custom menu.

Capture the Power