How to Add Dialog Boxes to Excel Macros with Visual Basic Editor
You can use the InputBox function to add dialog boxes to your Excel 2013 macros. 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 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 that you want entered. The InputBox function uses the following syntax:
In this function, only the prompt argument is required with the rest of the arguments being 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, you enter the functions Chr(13) and Chr(10) in the text (to insert a carriage return and a linefeed in the message, respectively).
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 name of the application 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, as follows:
Position the insertion point in the Code window at the beginning of the ActiveCell.FormulaR1C1 statement and press Enter to insert a new line.
Now that you’ve added a new line, you need to move the insertion point up to it.
Press the up-arrow 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 argument 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.
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.
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.
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.
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.
Select Mind Over Media on line 12 and replace it with SpreadsheetTitle (with no quotation marks).
Save the edited macro by clicking the Save button on the Visual Basic toolbar and then return to the worksheet by clicking the View Microsoft Excel button or pressing Alt+F11. Then, click the Hide button in the Window group of the VIEW tab.
Now, you’re ready to open a new workbook and run the edited macro by pressing Ctrl+N.
The figure shows the Code window with the edited Company_Name macro after adding the statements that make it interactive.
The following figure shows the Spreadsheet Title dialog box in action in the worksheet. This input dialog box now automatically appears and prompts you for input whenever you run the edited and now fully interactive version of the Company_Name macro.
To go ahead and enter Mind Over Media into the current cell and then format it by using the rest of the macro commands, you just click OK in this custom dialog box. To enter and format the name of another company, you simply type the name of the company (which automatically replaces Mind Over Media in the text box) before you click OK.