Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Getting User Input in a Dialog Box.
If you need to get input from a user under control of a macro, one method you can use is to employ the InputBox function. This function displays a dialog box and allows the user to type a response. The result is a string, returned to your macro, which you can then process and use.
The syntax for the InputBox function is as follows:
sResponse = InputBox(sPrompt, sTitle, sDefault)
There are three parameters you can use with InputBox (each of them strings), although only the first one is absolutely required. In this syntax, sPrompt is the text you want displayed as the user prompt, sTitle is the text to display in the title bar of the dialog box, and sDefault is the default text string offered to the user in the dialog box. The user can edit or accept the default string, as desired.
As an example, the following code lines can be used to display a dialog box and ask the user for his or her name:
Dim sUserName as String Dim sPrompt as String Dim sTitle as String Dim sDefault as String sPrompt = "Please check your name and make any corrections" sTitle = "Name Entry" sDefault = "John Doe" sUserName = InputBox(sPrompt, sTitle, sDefault)
When this code is completed, the sUserName variable contains whatever the user entered. You can then, in your macro, use that input in any other way that you see fit.
The InputBox function is great for grabbing one piece of data at a time, as you can only post a single "question" using it. If you need to get a bunch of user input for use in your macro, then you'll need to rely either on a UserForm or grab the input directly from a worksheet. (Both approaches have been discussed in other ExcelTips.)
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11416) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Getting User Input in a Dialog Box.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Place a cell reference in a macro, modify the structure of your worksheet, and you may soon find that the cell reference ...
Discover MoreNeed to put together a bunch of characters to create a text string? You can do it in your macros by using the String ...
Discover MoreUsing macros to step through each cell in a selection is a common occurrence. What if that selected range is made up of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-05-03 08:01:40
Willy Vanhaelen
@Lee Wolf
Try:
*** "Z:\SALES\SALES REPORTS\" & sBackDate, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
2018-05-02 14:16:44
Lee Wolf
Is there a way to pass sUserName to a string in a filename? Such as below where I define sBackDate in place of sUserName. The string below (indicated with ***) does not work with or without the "=" sign. Any help would be appreciated.
Dim sBackDate As String
Dim sPrompt As String
Dim sTitle As String
Dim sDefault As String
sPrompt = "Please erase date and enter last Saturday's date backwards"
sTitle = "Backwards Date Entry"
sDefault = "19620515 Raw Data.xlsm"
sBackDate = InputBox(sPrompt, sTitle, sDefault)
ChDir "Z:\SALES\SALES REPORTS"
ActiveWorkbook.SaveAs Filename:= _
*** "Z:\SALES\SALES REPORTS\=sBackDate", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
2016-07-10 20:19:03
Mark Fitzgerald
There is also an Application.InputBox method which can be used to return validated data of various types - formulas, numbers, text, logicals, ranges, error values and arrays.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments