Getting user input by adding a custom dialog box
One of the biggest problems with recording macros is that any text or values that you have the macro enter for you can never vary thereafter. If you create a macro that enters the heading Mind Over Media 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 prompts you for the name that you want entered. The InputBox function uses the following syntax:
InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])
In this function, only the prompt argument is required; the rest of the arguments are optional. The prompt argument specifies the message that appears inside the input dialog box, prompting the user to enter a new value (or in this case, a new company name). The prompt argument can be up to a maximum of 1,024 characters. If you want the prompt message to appear on different lines inside the dialog box, enter the functions Chr(13) and Chr(10) in the text (to insert a carriage return and a linefeed in the message).
The optional title argument specifies what text to display in the title bar of the input dialog box. If you don't specify a title argument, Excel displays the application name on the title bar.
The optional default argument specifies the default response that automatically 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.
The xpos and ypos optional arguments 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 horizontally and positions it approximately one-third of the way down the screen vertically.
The helpfile and context optional arguments specify 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 that it accepts. As part of the process of creating a custom Help file for use in the Excel Help system, you assign the topic a context number appropriate to its content, which is then specified as the context argument for the InputBox function. When you specify a Help file and context argument for this function, Excel adds a Help button to the custom input dialog box that users can click to access the custom Help file in the Help window.
Before you can add the line of code to the macro with the InputBox function, you need to find the place in the Visual Basic commands where the line should go. To enter the Mind Over Media text into the active cell, the Company_Name macro uses the following Visual Basic command:
ActiveCell.FormulaR1C1 = "Mind Over Media"
To add interactivity to the macro, you need to insert the InputBox function on a line in the Code window right above this ActiveCell.FormulaR1C1 statement. To supply the first three arguments of the InputBox function (prompt, title, and default), you create variables whose declared values are used. Finally, you create a variable that contains the entire InputBox function and sets it equal to the ActiveCell statement that begins the macro instructions.
To make these editing changes to the basic recorded macro, follow these steps:
- Position the insertion point in the Code window at the beginning of the ActiveCell.FormulaR1C1 statement and press Enter to insert a new line.
- Press the ↑ key to position the insertion point at the beginning of the new line.
- 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 title for this
worksheet in the text box below and
then click OK:"
On this line, you want to create a variable that supplies the prompt argument to the InputBox function. To do this, 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. - Type the following code to create the InputTitle variable on line 9 and then press Enter to insert a new line 10:
InputTitle = "Company Name"
Create a variable named InputTitle that supplies the optional title argument for the InputBox function. This variable makes the text Company Name 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. - 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"
Create a variable named DefaultText that supplies 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. - Type the following code to create the CompanyName variable that uses the InputBox function on line 11:
CompanyName = InputBox(InputMsg, InputTitle, DefaultText)
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. - Select "Mind Over Media" on line 12 and replace it with CompanyName (with no quotation marks).
Replace the value "Mind Over Media" in the ActiveCell.FormulaR1C1 property with the CompanyName variable (whose value is determined by whatever is input into the Company Name input dialog box), thus effectively replacing this constant in the macro with the means for making this input truly interactive.