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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Want to add some macros to your workbook? What do you do if you try to add the macros but the program has disabled the tools?
Discover MoreIf you have a macro that takes a long time to process a workbook, you might want to continue working in Excel while the ...
Discover MoreMacros are often used to process information in a workbook. If your macro makes changes in what is selected in the ...
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 © 2019 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