How to Use the InputBox Function in Excel 2016 VBA
The VBA InputBox function is useful for obtaining a single piece of information typed by the user in Excle 2016. That information could be a value, a text string, or even a range address. This is a good alternative to developing a UserForm when you need to get only one value.
Here’s a simplified version of the syntax of the InputBox function:
InputBox(prompt[, title][, default])
The InputBox function accepts the arguments listed here.
|Argument||What It Affects|
|Prompt||The text displayed in the input box|
|Title||The text displayed in the input box’s title bar
|Default||The default value for the user’s input (optional)|
An InputBox example
Here’s a statement that shows how you can use the InputBox function:
TheName = InputBox(“What is your name?”, “Greetings”)
When you execute this VBA statement, Excel displays this dialog box. Notice that this example uses only the first two arguments and does not supply a default value. When the user enters a value and clicks OK, the code assigns the value to the variable TheName.
The following example uses the third argument and provides a default value. The default value is the username stored by Excel (the Application object’s UserName property).
Sub GetName() Dim TheName As String TheName = InputBox(“What is your name?”, _ “Greetings”, Application.UserName) End Sub
The InputBox always displays a Cancel button. If the user clicks Cancel, the InputBox function returns an empty string.
VBA’s InputBox function always returns a string, so if you need to get a value, your code needs to do some additional checking. The following example uses the InputBox function to get a number. It uses the IsNumeric function to check whether the string is a number. If the string does contain a number, all is fine. If the user’s entry cannot be interpreted as a number, the code displays a message box.
Sub AddSheets() Dim Prompt As String Dim Caption As String Dim DefValue As Long Dim NumSheets As String Prompt = “How many sheets do you want to add?” Caption = “Tell me…” DefValue = 1 NumSheets = InputBox(Prompt, Caption, DefValue) If NumSheets = ““ Then Exit Sub 'Canceled If IsNumeric(NumSheets) Then If NumSheets > 0 Then Sheets.Add Count:=NumSheets Else MsgBox “Invalid number” End If End Sub
Check out the dialog box that this routine produces.
Another type of InputBox
The information presented here applies to VBA’s InputBox function. Microsoft seems to love confusion, so you also have access to the InputBox method, which is a method of the Application object.
One big advantage of using the Application InputBox method is that your code can prompt for a range selection. The user can then select the range in the worksheet by highlighting the cells. Here’s a quick example that prompts the user to select a range:
Sub GetRange() Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox _ (prompt:=“Specify a range:”, Type:=8) If Rng Is Nothing Then Exit Sub MsgBox “You selected range “ & Rng.Address End Sub
This is how it looks.
In this simple example, the code tells the user the address of the range that was selected. In real life, your code would actually do something useful with the selected range. A nice thing about this example is that Excel takes care of the error handling. If you enter something that’s not a range, Excel tells you about it and lets you try again.
The Application.InputBox method is similar to VBA’s InputBox function, but it also has some differences. Check the Help system for complete details.