Page 749 - Excel 2013 All-in-One For Dummies

Using the Visual Basic Editor 731
Now that you’ve added a new line, you need to move the insertion point up to it.
2. Press the ↑ key to position the insertion point at the beginning of the new line.
On this line, you want to create a variable that supplies the prompt argu- ment to the InputBox function. To do this, you state the name of the variable (InputMsg in this case) followed by its current entry. Be sure to enclose the message text on the right side of the equal sign in a closed pair of double quotation marks.
3. Type the following code to create the InputMsg variable on line 8 and then press the Enter key to start a new line 9:
InputMsg = “Enter the company name or title for this worksheet in the text box below and then click OK:”
Next, you create a variable named InputTitle that supplies the optional title argument for the InputBox function. This variable makes the text “Spreadsheet Title” appear as the title of the Input dialog box. Again, be sure to enclose the name for the dialog box title bar in quotation marks.
4. Type the following code to create the InputTitle variable on line 9 and then press Enter to insert a new line 10:
InputTitle = “Spreadsheet Title”
Next, you create a variable name DefaultText that supplied the optional default argument to the InputBox function. This variable makes the text, “Mind Over Media,” appear as the default entry on the text box at the bottom of the custom Company Name Input dialog box.
5. Type the following code to create the DefaultText variable on line 10 and then press Enter to insert a new line 11:
DefaultText = “Mind Over Media”
Next, you create a final variable named CompanyName that specifies
the InputBox function as its entry (using the InputMsg, InputTitle, and DefaultText variables that you just created) and stores the results of this function.
6. Type the following code to create the SpreadsheetTitle variable that uses the InputBox function on line 11:
SpreadsheetTitle = InputBox(InputMsg, InputTitle, DefaultText)
Finally, you replace the value, “Mind Over Media”, in the ActiveCell. FormulaR1C1 property with the SpreadsheetTitle variable (whose value is determined by whatever is input into the Spreadsheet Title Input dialog box), thus effectively replacing this constant in the macro with the means for making this input truly interactive.
Book VIII Chapter 2
VBA Programming