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