How to Use the InputBox Function in Excel 2016 VBA - dummies

How to Use the InputBox Function in Excel 2016 VBA

By John Walkenbach

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.

InputBox syntax

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
(optional)
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 InputBox function displays this dialog box.
The InputBox function displays this dialog box.

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 example of using the InputBox function.
Another example of using the InputBox function.

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.

Using the Application InputBox method to get a range.
Using the Application InputBox method to get a range.

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.