Spreadsheets with something extra: how to add explanatory messages and input boxes to your cells.

Have you ever created a spreadsheet only to open it a couple of months later to discover you couldn't remember how to update it or what data it needed? Or, if you prepared the spreadsheet for others in your organization, did they keep calling you for instructions on how to use it?

You can eliminate those problems and, as a by-product, ensure the accuracy of spreadsheets by enhancing them with easy-to-create graphic messages and input boxes that can provide reminders, explain various functions of the data tables, help users find and enter the right data and even refuse to let them proceed unless they follow a prescribed procedure. In short, those boxes serve as automated stand-ins for the spreadsheet creator, who can design them to appear whenever a user opens the spreadsheet. Though you have to write message and input boxes in the Visual Basic for Applications (VBA) language, which is built into Microsoft Excel, you will be happy to know it is easy to learn.

GET THE MESSAGE

Let's begin by creating a message box. The message may be a greeting to the user, instruction about using the spreadsheet or a reminder to complete a task. The message syntax, in its simplest form, is: MsgBox "prompt", where the words between the quote marks are the message.

To make a message box, open Excel and press Alt+F11 to launch the Visual Basic Editor. Another way to start the editor is to click on Tools, Macro and then select Visual Basic Editor. Once it begins, click on Insert, then Module to open a VBA screen; that's the place where you will write the VBA code, which is technically called the subroutine or subprocedure.

All subroutines must start with the word Sub and finish with End Sub. After typing Sub, give the routine a name followed by open and closed parentheses. As a practical matter, the name you assign should describe your routine so it will be easy to identify.

A subroutine for a welcoming message box, called Hello, would look like this:

Sub Hello ()

MsgBox "Hello!!"

End Sub

Exhibit 1, page 53, shows a sample of such a message box and the code that produced it. After users read the message box and press the OK button, it disappears from the screen.

[ILUSTRATION OMITTED]

You can change the type of button and icon appearing in the message box by adding code numbers after the message. Exhibits 2 and 3 (page 53) show the various codes for each.

For example, if you want the message box to show the OK and Cancel buttons and the Information message icon, add the total values associated with them--in this case it is 65 (1 + 64)--and enter that number after the message preceded by a comma. Exhibit 4, below, shows the code and the resulting message box.

[ILLUSTRATION OMITTED]

To change the self-promoting Microsoft Excel default title of the message box from appearing in the upper left-hand corner, type in your choice, in quotes, immediately after the comma and after your button/icon number. Exhibit 5, page 54, shows you the code and the resulting message box where the title has been changed to Greetings from Accounting.

[ILLUSTRATION OMITTED]

Exhibit 6, page 54, shows a different form of message box containing Yes and No buttons along with a critical message icon. Later in the article we'll describe how to use this type of message box, which invites a response from users and will not let them proceed until they answer the question.

[ILLUSTRATION OMITTED]

A message box allows you to display a message of up to 1,024 characters--so you have plenty of room for informative text. If you want to use multiple lines in your message box, add & Chr(13) & at the points you want a line break. Exhibit 7, page 54, is an example of a message box with line breaks.

[ILLUSTRATION OMITTED]

A message box also can retrieve information from your Excel spreadsheet and include it as part of your message. The information can be added before, inside or after any part of your message. The code for retrieving the information from Excel is & Range("cell location").Value &. Exhibit 8, page 54, shows a message where the person's name is retrieved from cells A1 and B1.

[ILLUSTRATION OMITTED]

Now that you know how to write a basic message box, let's add additional VBA code that will send a user to different places in your worksheet depending on the button pushed. We'll use the message box developed in exhibit 6 to demonstrate a routine in which, if the user pushes the Yes button, the cursor will move to sheet 3, cell A1 of the workbook; and if the user pushes No, the cursor moves to A1 of the current worksheet. For that, you will need additional buttons; the standard ones are listed in exhibit 9, page 54.

For the Yes button the code is 6; for No the code is 7. A temporary variable now needs to be added to the routine to store this information. We will call this temporary variable returnvalue--although you may use any name--and add it before the word MsgBox followed by an equal sign. In this case, because the routine is required to return data, the MsgBox function is surrounded by parentheses rather than quote marks.

To implement the Yes, No function, we will use an If ... Then ... Else statement. As exhibit 10, page 54, shows, the If ... Then ... Else statement is written to test the user's choice. If the temporary variable returnvalue equals 6, the user will be taken to sheet 3, cell A1 of the workbook, and if returnvalue does not equal to 6, the user will be sent to A1 of the current worksheet.

[ILLUSTRATION OMITTED]

PROMPTING A PROMPT

At times you may want to capture written information from the user or remind yourself to leave a written message in your worksheet. Do this with an input box. The InputBox function displays a prompt in a dialog box and waits for the user to input text. The prompt tells the user what kind of input is requested (for example, the name of the recipient). The properties of an InputBox are similar to that of the MsgBox; the prompt can be 1,024 characters long and lengthy prompts can be separated into multiple lines by using the carriage return character--& Chr(13) &.

The standard code of the InputBox function is InputBox ("prompt","title"), where prompt refers to the message written in the input box and title refers to the title in the upper left-hand corner. If you want the information inserted by the user to automatically return to your worksheet, specify the target for this information. Exhibit 11, page 55, shows an input box that captures the user's name and places it in cell B5 of a worksheet named Cost.

[ILLUSTRATION OMITTED]

Now that you see how simple message and input boxes are to incorporate into your spreadsheet, you will discover you can do many handy things with them--including a combination of routines. For instance, assume you want to put a control in your worksheet that forbids the user to leave an input box blank. If the user fails to enter data in that cell, a pop-up message is triggered and the user is not allowed to proceed until he or she puts the information in. Doing this requires the use of the If ... Then ... Else statement with a message box and also a Do ... Loop. An example is shown in exhibit 12, at right.

[ILLUSTRATION OMITTED]

You can program Excel to evoke a message and input box when a workbook or worksheet opens or closes or when a user clicks on a button. To automatically have a message box appear when your workbook is opened, do the following, as shown in exhibit 13, below:

1. Enter the Visual Basic Editor (Alt+F11) and click on This Workbook in the VBA Project Explorer window.

2. From the View menu, select Code and a code window will open to allow you to type in code specifically for the workbook.

3. From within this code window, select Workbook from the drop-down menu on the left and Open from the drop-down menu on the right. That will create a subroutine, which will run automatically when a user opens your workbook.

4. Finally, inside this subroutine, type in the message or input box function that you want.

[ILLUSTRATION OMITTED]

To have another subroutine created that runs when the workbook is closed, select the BeforeClose action from the drop-down on the right. Then you can type in your new code.

If you want to evoke a message box when a user enters or exits a worksheet, follow these steps, as shown in exhibit 14, page 56:

[ILLUSTRATION OMITTED]

1. Enter the Visual Basic Editor and click on the worksheet to be activated in the VBA Project Explorer window.

2. From the View menu, select Code and a code window will open to allow you to type in code specifically for the selected worksheet.

3. Within this window, if you select Worksheet from the drop-down menu on the left and Activate from the drop-down on the right, a subroutine will be created that will run automatically upon entering your worksheet.

4. Finally, inside this subroutine, you can type the message or input box function you want.

To have another subroutine created that runs when the worksheet is exited, select the Deactivate action from the drop-down menu on the right. Then you can type in your code.

If you want the message box to appear only when a user asks for it, click on View, Toolbars, Forms. That brings up the Forms toolbar (exhibit 15, at left). Center your mouse over the button, as shown in the exhibit, left-click on it and then release it. Now move your cursor where you want the button. Note how your cursor becomes a thin cross when you move it around the spreadsheet. Once you place the button, left-click your mouse, holding it down as you adjust its size.

[ILLUSTRATION OMITTED]

When finished, release the button and an Assign Macro dialog box will appear, as shown in exhibit 16, at left. Simply click on the macro you wrote and click on OK. Your macro is now assigned to your button. When a user clicks on it, the Hello message box will appear.

[ILLUSTRATION OMITTED]

If the Assign Macro dialog box doesn't appear or you want to assign a new macro to your button, right-click when the cursor is over the button and a shortcut menu will pop up. Choose the Assign Macro selection, evoking the same dialog box. If you want a picture for your button, right-click while the cursor is on the picture and the same menu will appear. Again, choose Assign Macro.

Now that you know the fundamentals of creating message and input boxes, you're sure to come up with many ways of your own to use them and make your spreadsheets much more valuable, useful and friendly. And it takes just a small investment of time.

Exhibit 2

Value   Button

0       OK
1       OK and Cancel
2       Abort, Retry and Ignore
3       Yes, No and Cancel
4       Yes and No
5       Retry and Cancel

Exhibit 3

Value   Description                Icon

16      Critical message icon        x
32      Warning query icon           ?
48      Warning message icon         !
64      Information message icon     i
0       Blank                      Blank

Exhibit 9

Button-return   Button
value           clicked

1               OK
2               Cancel
3               Abort
4               Retry
5               Ignore
6               Yes
7               No

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces.

Boldface type identifies the names of icons, agendas, URLs and application commands.

Sans serif type indicates instructions and commands that users should type and file names.

LOIS S. MAHONEY, CPA, PhD, CMA, is an assistant professor at the University of Central Florida's School of Accounting. Her e-mail address is [e-mail]. edu. JUDITH K. WELCH, CPA, PhD, is an associate professor at the University of Central Florida's School of Accounting. Her e-mail address is [e-mail].