An Excel VBA InputBox Tutorial for Allowing User Input in Excel

Have you ever had a worksheet that needed specific input from the user for certain calculations? Most applications rely on data input from users to determine various functions or procedures. Excel has a number of functions that allow you to gather user input for your applications. VBA allows you to create message boxes, user input forms and input boxes to get user input. This tutorial will show you how to use the Excel VBA InputBox function to get user input for your worksheets. For lessons on how to modify the user interface within Excel and how to use forms and other VBA functions, sign up for the Ultimate Excel VBA course.

This tutorial assumes you have some good basic Excel spreadsheet knowledge and experience. Excel 2013 Made Easy is a great introduction on how to use Excel. Sign up for the course to learn how to get the most from your spreadsheets.

How to Use VBA and Functions in Excel

There are two ways to access the VBA editor in Excel. You can click on the developer tab and then select “Visual Basic” from the menu.

You can also add an ActiveX control to your form and then double click that control to open the editor.

For our tutorial example we will add an ActiveX control called a command button and then use that button to activate our InputBox. You can associate the InputBox with any event like the addition of a new worksheet or when a user opens the worksheet.

The Excel VBA Tutorial: Make Your First Macro and Get to Know the Tools is a great introduction that will teach you how to use the various tools associated with creating macros.

Now that we have a command button, we need to add our InputBox function to the command button to acquire the user input we need.

How the Excel VBA InputBox Works

The InputBox function in Excel allows you to create a box that allows the user to input data depending on the type of data specified by the InputBox.

The InputBox relies on a number of parameters to determine what data it accepts and what it looks like. The parameters required for the InputBox include the following:

Application.InputBox(Prompt[, Title][, Default][, Xpos][, Ypos][, HelpFile][, Context], [Type:=])

The Prompt parameter displays the message in the text box. This is how you tell the user what information is required:

• The Title parameter creates the title string for the InputBox and the default is nothing. This parameter is an optional parameter.
• The Default parameter is an optional parameter that allows you to display a default value in the text box.
• The Xpos and Ypos parameters allow you to determine where the dialog box is located. These are optional parameters and seldom used.
• The HelpFile and Context are also optional parameters that allow you to associate a custom help file with the dialog box.
• The Type parameter is used to specify the data type you want the user to enter. Types include type 1 for numbers, type 2 for text or strings, type 4 for True or False, Type 8 for cell references or range objects, type 16 for error values and type 64 for values of arrays.

Create a Simple InputBox For Number Input

Let’s create a simple InputBox that accepts number input from the user. We will use the number as a salesperson ID for our worksheet and show or hide different sales columns depending on what userID the user enters in out InputBox. The code to create the input box looks like this:

inputData = Application.InputBox(“Enter your salesID:”, “Input Box Text”, Type:=1)

This is our original worksheet:

Bob has UserID number 1, Jack has UserID number 2 and Jane has userID number 3. We have inserted a Command box to ask for the salesperson’s userID. We will also use the VBA “IF” function to decide which columns to hide and which to show depending on the salesperson ID entered. The VBA IF ELSE Statements for Powerful Microsoft Applications article is a great tutorial on how to use the “IF” function in Excel VBA.

This is what the VBA code looks like:

  Private Sub CommandButton1_Click()Dim inputData As String
  '
  ' Get the data
  'inputData = Application.InputBox("Enter your sales ID:", "Input Box Text", Type:=1)
  '
  ' Check to see if any data was entered
  '
  If inputData = 1 Then
  '
  ' If so, display Bob's sales
  '
  Columns("E:F").Hidden = True
  Columns("A:C").Hidden = False
  Columns("D").Hidden = False
  ElseIf inputData = 2 Then
  '
  ' If so, display Jack's sales
  '
  Columns("F").Hidden = True
  Columns("D").Hidden = True
  Columns("A:C").Hidden = False
  Columns("E").Hidden = False
  ElseIf inputData = 3 Then
  '
  ' If so, display Jane’s sales
  '
  Columns("D:E").Hidden = True
  Columns("A:C").Hidden = False
  Columns("F").Hidden = False
  End If
  End Sub

When a salesperson enters his or her userID, the worksheet hides the sales columns of the other sales representatives and only displays that particular salesperson’s sales. If the user clicks 1, for Bob then the resultant worksheet looks like this:

If the salesperson enters ID number 2, then the worksheet looks like this:

If you are not sure how to create the command button or how to create and record macros, then the  Introduction to VBA Macros course will take you step-by-step through the process of creating macros and using VBA in Excel.

Create a simple InputBox for Text Input

If we use type 2 to allow the salesperson to enter their name instead of salesID, then the code for the input box will look like this:

inputData = Application.InputBox(“Enter your name:”, “Input Box Text”, Type:=1)

Now when the user types in his name, the relevant sales column will be revealed and the other sales columns will be hidden. If Bob types in his name, this is what the worksheet will look like:

And when Jane enters her name, the worksheet will look like this:

Data input really allows you to create very powerful applications using Excel VBA. To learn to unlock the true power of Excel, take a look at the Visual Basic for Excel – Microsoft VBA Excel Tutorial. This certified course will show you how to create macros, functions and procedures that will take your use of Excel to the next level.