Page 315 - Excel Workbook for Dummies

33_798452 ch23.qxp 3/13/06 7:51 PM Page 298
298 Part VII: Macros and Visual Basic for Applications
adds a Help button to the custom input dialog box that users can click to access
the custom help file in the Help window.

Try It

Exercise 23-2: Making a Recorded Macro Interactive by Adding an Input
Dialog Box

Launch Excel to open a new Book1 workbook in the program. You will then unhide
the PERSONAL.XLS workbook from Sheet1 of this workbook. Open the Company_
Name macro that you edited in the previous exercise in the Visual Basic Editor and
add the commands to display a dialog box prompting the user for the worksheet title
to enter when the macro is run:

1. Choose the Window➪Unhide menu command to open the Unhide dialog box.
2. While PERSONAL.XLS is selected in the Unhide Workbook list box, select the OK
button.

3. Press Alt+F8 to open the Macro dialog box and then select the Company_Name
macro in the list box before you select the Edit button.
4. Position the insertion point in the Code window at the beginning of the
ActiveCell.FormulaR1C1 statement and press Enter to insert a new line.

5. Press the ↑ key to move the insertion point up the beginning of the new blank
line.
Note that the status indicator at the end of the Standard toolbar indicates the
insertion point is now located in column 5 of line 8 (Ln 8, Col5).
Now you’re ready to create the three variables — InputPrompt, InputTitle, and
DefaultText — that contain the values you want used as the prompt, title, and
default arguments of the InputBox function. You create all variables for the code
at the beginning of the macro subroutine by declaring their names and setting
them equal to the values you initially want used. Then, when you’re ready to
enter the InputBox function in the code, all you have to do is enter the variable
names in the order of the InputBox arguments. Note that by using variables to
supply the prompt, title, and default arguments of the InputBox function, you
avoid creating such a long line of code that is not only hard to read but hard to
edit as well.
6. Type the following code on line 8 to create the InputPrompt variable and then
press the Enter key to start a new line 9:
InputPrompt = “Enter the title for this spreadsheet in the
text box below and then select OK:”
7. Type the following code on line 9 to create the InputTitle variable and then press
the Enter key to start a new line 10:
InputTitle = “Spreadsheet Title”
8. Type the following code on line 10 to create the DefaultText variable and then
press the Enter key to start a new line 11:
DefaultText = “Mind Over Media, Inc.”
Next, you declare a fourth variable, CompanyName. This variable actually con-
tains the InputBox function using the InputPrompt, InputTitle, and DefaultText
variables you just created as its prompt, title, and default arguments.
9. Type the following code on line 11 to create the CompanyName variable that
contains the InputBox function:
CompanyName = InputBox(InputPrompt, InputTitle, DefaultText)