Return to VBA Code Examples

VBA InputBox – Get Input from a User

This tutorial will demonstrate how to get input from a user with the VBA Input Box.

The VBA Input Box allows us to prompt the user to input information.  The information can then be used in our VBA Code or in an Excel worksheet.

The VBA Input Box with a Variable

We can declare a variable in our VBA code which then stores the information that is typed into the Input Box.  We can then use this variable in our code.

Depending on the type of information we want returned from the input box, we can declare a specific type of variable to either return text (a string or variant variable) or to return a number (an integer, long, double or single variable).

The String Variable with an Input Box

Here is how you can create an InputBox where the data is returned into a string variable.

vba inputbox

Notice first we declare a variable. This variable will be assigned the value that the user enters.  We used variable type String in order for the Input box to accept text strings (which includes any alpha numeric characters).

The Number Variable with an Input Box

If you declare a number variable, you will need to enter a number only into the input box.

First we declare the number variable as an integer variable.   We can then also put a default value of 1 in the input box.

Inputbox integer

If we were to enter a string instead of a number and then click on OK, we would get a Type Mismatch Error as a number variable does not accept a string.

InputBox integer error

Get Input from a User

Here is another example using the most popular features of an InputBox.

tiphacks3

The following code does three things:

1. Prompts for input with an InputBox and assigns it to a variable

2. Verifies input, otherwise exits the sub

3. Returns the input in a message box

Returning the input to an Excel Sheet

You can return the input that you type into an input box into a specific cell in your Excel sheet.

vba input box enter

We can also return input data to an Excel Sheet using a variable.

In the example above, we are required to enter a number.  If we enter a number, then the variable dblAmount will put the number into the Excel sheet at cell A1.  However, if we do not enter a number, then a message box will tell us that we did not enter a number and nothing will be put into cell A1.

InputBox Excel Error

 

Using VBA InputBox in Access VBA

The VBA input box works exactly the same way in Access as it does in Excel when returning user input in the form of a message box.

However, if you wish to return the user input to the database, you would need to use the Recordset object rather than the Range object as used in Excel.

vba inv no

 

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

 

Learn More!


<<Return to VBA Examples