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.
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
This is the most basic example.
InputBox("Type your input below:")
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.
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:
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:
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
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.