How to Create a VBA InputBox
That Collects User Data (2024)

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!

Table of Contents

Creating an Excel 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 - show dialog box in excel sheet

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 in dialog box is the return value to the variable

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.

In the following example, we’ll include the Title argument – which is an optional parameter.

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

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

And the result:

new text in title bar of excel vba input box - part of the optional parameters of inputbox function

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.

Excel vba text box to write user input - it shows default value - with ok button and cancel button

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.

VBA inputbox pros and cons

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.

That’s it – Now what?

Well, that was how to use the VBA inputbox function to prompt the user, via an inputbox dialog box, to add a value that’ll go into a variable.

Pretty cool, huh?💡

Gathering user input is useful – but it is a very small part of VBA programming.

If you want to learn more, click here to read more about (and enroll in) my free course on VBA.

Other resources

Playing around with inputboxes requires a good knowledge about the VBA editor in general.

If you’re not 100% confident using the visual basic editor (VBA/VB editor in short), read all about it here.

The inputbox function always comes with a variable, so if you’re going to use them extensively, you might wan’t to check out my full guide to declaring variables here.