Get User Submitted Data from a Prompt in Excel using VBA Macros

How to prompt a user for their input in Excel.

There is a simple way to do this using VBA and macros; we use the InputBox function. This causes a small window to appear that has a place for the user to enter data.

In this tutorial I will show you examples of how to get a user's input using this method; this is a practical tutorial, which means that I am not going to talk about every little thing you can do with the InputBox function, many of which are almost never used.

Sections:

Example 1 - Prompt a User for their Input

Example 2 - Default Input Values and Window Title

Example 3 - Determine if the User hit the Cancel Button

Notes

Example 1 - Prompt a User for their Input

This is the most basic example.

1. Type InputBox and then an opening and closing parenthesis and the text that you want to be visible to the user.
   

   InputBox("Type your input below:")

   
   Note that the text is surrounded with double quotation marks.
2. If you go back to Excel and run this macro, it looks like this:
   
   You can see the text we input, two buttons, and the input area.
   The user can type whatever they want into the input area and if they then hit OK, the data will go back to the macro, but if they hit Cancel, no data will be returned (more on this below).
3. Now that we have the input window, we need to actually get that input into Excel; currently, nothing happens with the input.
   Go back to the VBA editor window and set a variable equal to the InputBox function like this:
   
   Here, I created the variable userinput, just by typing it, and then typed an equals sign in front of the code we already had.
   Now, whatever the user types into the window will be accessible through the userinput variable.
4. So you can see the input, I will add a line to output it in a message box pop-up window like this:
   
5. Go back to Excel and run it.
   
   When you hit OK,  you should see the input in a message box:
   
   That's it!

You don't have to output the data the user entered into a message box; I did that just so we could get a visual verification that everything worked.

Creating a user input window is really easy to do, just remember to set a variable equal to it so you can do something with the input.

Example 2 - Default Input Values and Window Title

There are two other arguments for InputBox that are often used. They allow you to input a title into the window and a default value that appears in the input area.

Here is the code:

InputBox("Type your input below:", "Title", "Input")

The steps to make this are quite simple:

1. Type InputBox(
2. Type the text you want in the window, surrounded by double quotation marks: "Type your input below:"
3. Type a comma and then type the text you want for the title of the window: ,"Title"
4. Type another comma and then the text that you want to be the default value for the input area: ,"Input"
5. Then, type a closing parenthesis )

All we did different from the previous example was to add two more arguments, the title and default input.

Here it is in the VBA window:

Notice that I also set the variable userinput equal to the InputBox function. If you don't understand why, read Example 1 above.

Here is how it looks in Excel:

Example 3 - Determine if the User hit the Cancel Button

To figure out if the user clicked the cancel button, we need to test the value returned by InputBox.

Unfortunately, when the user hits the Cancel button, the value returned will be an empty string, which is the same as the user entering nothing and hitting the OK button. This makes it a little less precise as far as knowing which button the user actually clicked, so you will just have to work around this in your code logic.

Use this code to test if the user hit Cancel or entered nothing:

If userinput = vbNullString Then
    MsgBox "User hit Cancel or Input nothing."
Else
    MsgBox "The user input: " & userinput
End If

This is a simple IF statement and it will return a message box with the user's input if they input anything or with a basic message if they input nothing.

Userinput is a variable that holds the output of the InputBox and follows the format of the examples above.

The important part of the code is this:

If userinput = vbNullString

vbNullString represents the value that is returned by InputBox if the user enters nothing and hits OK or hits the Cancel button. So, we test if the input, which is stored in the userinput variable, equals vbNullString or not and then we decide what to do with that information in the IF statement.

Here is the full VBA macro code for this example:

Sub GetUserInput_3()

userinput = InputBox("Type your input below:")

If userinput = vbNullString Then

    MsgBox "User hit Cancel or Input nothing."

Else

    MsgBox "The user input: " & userinput

End If

End Sub

Notes

The InputBox function is my favorite way to get input from the user because it is simple to use and it forces the user to do something before they move on; in other words, they can't ignore the input window and keep working, they must do something with it.

This tutorial was meant to show you how to use the InputBox in its most basic and most often used forms. There are many more things that you can do with this function, but they will be covered in another InputBox tutorial; as for this tutorial, it covers 95% of the cases where you will need to use the InputBox function.

Make sure to download the sample Excel file attached to this tutorial so you can copy the Macros and work with them in Excel.