Spreadsheets With Something Extra

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

Exhibit 1 above 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.

You can change the type of button and icon appearing in the message box by adding code numbers after the message. Exhibit 2 and exhibit 3 at right show the various codes for each.

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 32 Warning query icon 48 Warning message icon 64 Information message icon 0 Blank Blank

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.

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 at right shows you the code and the resulting message box where the title has been changed to Greetings from Accounting. Exhibit 6 at right 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. 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 at right is an example of a message box with line breaks. 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 at right shows a message where the person’s name is retrieved from cells A1 and B1.

Exhibit 4 Exhibit 5 Exhibit 6 Exhibit 7 Note : The end of the first MsgBox code line contains an underscore character (_) preceded by a space. This format indicates the code line continues onto the next line. Exhibit 8 Note : Maintain a space between the double quotes in the MsgBox code line.

To implement the Yes , No function, we will use an If…Then…Else statement. As exhibit 10 below 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. 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) &. Advertisement
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 at right shows an input box that captures the user’s name and places it in cell B5 of a worksheet named Cost . 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.		Exhibit 10 Note : Words following the single apostrophe are comments and ignored by VBA. Exhibit 11 Input box VBA code Exhibit 12

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 above.

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.

Exhibit 13

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 below:

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.

Exhibit 14

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 right). 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.	Exhibit 15
When finished, release the button and an Assign Macro dialog box will appear, as shown in exhibit 16 above. 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. 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 .	Exhibit 16

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.

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 lois.mahoney@bus.ucf.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 judith.welch@bus.ucf.edu .