Page 314 - Excel Workbook for Dummies

33_798452 ch23.qxp 3/13/06 7:51 PM Page 297
Chapter 23: Using the Visual Basic Editor 297

10. Switch to the Book1 workbook and then test out the changes to the Company_
Name macro by pressing Ctrl+Shift+C.
This time, the macro inserts the Mind Over Media, Inc. company name in the
merged cell A1:E1 in 14-point bold using Times New Roman as the font.
11. Close the Book1.xls file without saving your changes and then hide the
PERSONAL.XLS workbook by choosing the Window➪Hide menu command.
12. Exit Excel, this time selecting the Yes button when prompted to save your
changes.

Adding a dialog box that processes user input

One of the biggest problems with recording macros is that any text or values that you
have the macro enter for you in a worksheet or chart sheet can never vary thereafter.
If you create a macro that enters the heading “Bob’s Barbecue Pit” in the current cell
of your worksheet, this is the only heading you’ll ever get out of that macro. However,
you can get around this inflexibility by using the InputBox function. When you run the
macro, this Visual Basic function causes Excel to display an input dialog box where
you can enter whatever title makes sense for the new worksheet. The macro then
puts that text into the current cell and formats this text, if that’s what you’ve trained
your macro to do next.
To see how easy it is to use the InputBox function to add interactivity to an otherwise
staid macro, follow along with the steps for converting the Company_name macro
that currently inputs the text “Mind Over Media” to one that actually prompts you for
the name you want entered. The InputBox function uses the following syntax:

InputBox(prompt[,title][,default][,xpos][,ypos]
[,helpfile,context])

The many arguments of the InputBox function include

Prompt, which specifies the message (up to 1024 characters) that appears inside
the input dialog box, prompting the user to enter a new value (or in this case, a
new company name). To have the prompt message appear on different lines
inside the dialog box, you enter the functions Chr(13) and Chr(10) in the text.
(Chr stands for Character. The number codes insert a carriage return to start a
new line and a linefeed to position the insertion point at the beginning of the
line, respectively.)
Title optional argument, which specifies what text to display in the input dialog
box’s title bar — if you don’t specify a title argument, Excel displays the name of
the application on the title bar.
Default optional argument, which specifies the default response that automati-
cally appears in the text box at the bottom of the input dialog box. If you don’t
specify a default argument, the text box is empty in the input dialog box.
Xpos and ypos optional arguments, which specify the horizontal distance from
the left edge of the screen to the left edge of the dialog box and the vertical
distance from the top edge of the screen to the top edge of the dialog box. If
you don’t specify these arguments, Excel centers the input dialog box hori-
zontally and positions it one-third of the way down the screen vertically.
Helpfile optional argument, which specifies the name of the custom Help file that
you make available to the user to explain the workings of the input dialog box as
well as the type of data its accepts.
Context optional argument, which specifies the context number assigned to the
help topic — note that when you specify a helpfile and context argument, Excel