Using an Input Box to Gather Data in VBA

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Most of the time, you write VBA code that uses values already present in your spreadsheet.

But what if you want to get information directly from the user?

Input boxes let you do just that!

Instead of just displaying a message (like a message box), they allow users to enter information – and then send that information directly to your macro.

Here’s how it works!

Creating an input box with VBA

Making an input box with VBA in Excel is very easy. All you need to know is this construction:

InputBox("[text]")

So, for example, you could use this:

InputBox("What’s your favorite Spreadsheeto course?")

Here’s the full VBA module that we’ll use to create this box:

Sub ssFav()
 InputBox("What’s your favorite Spreadsheeto course?")
End Sub

When we run this module, we get this input box:

vba-input-box

Simple, right?

Kasper Langmann, Co-founder of Spreadsheeto

Storing input from user entries

In the previous example, when the user hits OK, the text that they put into the input box just disappears. It’s not stored anywhere.

We can fix that by combining the command for an input box with the command for assigning a value to a variable.

First we’ll create a String variable (we’re using String because we’re planning on collecting text entries):

Dim response As String

Then we’ll assign a value to it. Instead of just assigning a regular string, we’ll assign the entry from an input box:

response = InputBox("What’s your favorite Spreadsheeto course?")

Now that we’ve stored the data in a variable, let’s do something with it. We’ll put the text into a cell:

Range("B1").Value = response

The whole module looks like this:

Sub ssFav()
  Dim response As String
  response = InputBox("What’s your favorite Spreadsheeto course?")
  Range("B1").Value = response
End Sub

When we run the module, we see the input box just like last time. Now, however, when we enter an answer and hit OK, it’s stored in B1:

input-box-store

You can store all kinds of information from input boxes, but it’s important to remember to declare the right type of variable.

If we had a variable declared as an integer and someone enters text in the input box, it’s going to cause trouble.

Once the value is correctly stored, however, you can run operations on it, write it to a cell, or do any of the other great things you’ve learned to do with VBA.

Other input box options

Here’s the full syntax for an input box in VBA:

InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

In our previous example, we only used the Prompt argument.

By including an argument for Title, we can also give the box a more descriptive title than the default, Input.” Here’s the VBA code we used:

InputBox("What's your favorite Spreadsheeto course?", "Spreadsheeto Survey")

And the result:

input-box-title

You can also include a default value in the input box, so when it appears it already has an option available for the user.

This is especially useful if you’re asking for input from numerous people who are likely to have the same answer; just set the answer as default to save them some time.

input-box-default

The Left and Top arguments let you position the input box wherever you’d like it. The distance is measured in points from the upper-left corner of the user’s screen.

You’ll have to do some experimenting if you want to find the optimum position for your input box. Because points aren’t the same as pixels, it can be a bit unpredictable.

It might be best to just leave the default values for the position, as the input box is likely to come up somewhere visible.

HelpFile and HelpContextID are advanced parameters that let you display a link to a help file (don’t worry about these right now).

Finally, Type tells Excel the type of data to send from the input box. Here are the options:

  • 0 A formula
  • 1 A number
  • 2 Text (a string)
  • 4 A logical value (True or False)
  • 8 A cell reference, as a Range object
  • 16 An error value, such as #N/A
  • 64 An array of values

As long as you declare the right variable type, you won’t have to worry about this much. Once you advanced your VBA skills and want to pass arguments like formulas and error codes, though, you’ll need to know these.

Be careful what you ask for

Using an input box is a great way to collect information from the user that can then be used in a macro. Remember, though, that users are relatively unrestricted in what they can enter into an input box.

If you’re expecting a number and they give you text, it’s going to mess up your macro.

You may want to use something that lets you limit the choices, like a drop-down list. No matter which way you decide to go, remember that it’s best to make the choices as simple as possible.

2019-03-29T09:10:41+00:00