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.)
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Using macros to step through each cell in a selection is a common occurrence. What if that selected range is made up of ...
Discover MoreDo you get tired of the dialog box that says "do you want to enable macros" that is displayed when you open a workbook. ...
Discover MoreMacros give you a great deal of control over creating, finding, renaming, and deleting files. This tip focuses on this ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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 © 2018 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