What Feature Allows Users To Create A Dynamic Field Without Needing To Upload A Feed?

excel vba inputbox Have yous ever had a worksheet that needed specific input from the user for certain calculations? Nigh applications rely on data input from users to make up one's mind various functions or procedures. Excel has a number of functions that permit you to get together user input for your applications. VBA allows you to create bulletin boxes, user input forms and input boxes to get user input. This tutorial will show you lot 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 y'all have some expert basic Excel spreadsheet knowledge and experience. Excel 2013 Made Piece of cake is a great introduction on how to utilize Excel. Sign upward for the course to learn how to get the virtually from your spreadsheets.

How to Use VBA and Functions in Excel

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

InputBox1

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

InputBox2

For our tutorial example we volition add an ActiveX control called a command push and then utilize that button to activate our InputBox. You can associate the InputBox with whatsoever consequence like the add-on 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 volition teach you lot how to use the diverse tools associated with creating macros.

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

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 blazon of information specified by the InputBox.

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

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

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

The Title parameter creates the title cord for the InputBox and the default is zilch. 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 y'all to decide where the dialog box is located. These are optional parameters and seldom used.
The HelpFile and Context are also optional parameters that allow y'all to associate a custom help file with the dialog box.
The Blazon parameter is used to specify the information type you want the user to enter. Types include type ane for numbers, type 2 for text or strings, type iv for Truthful or False, Type eight for jail cell references or range objects, type 16 for mistake values and type 64 for values of arrays.

Create a Simple InputBox For Number Input

Allow'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 lawmaking to create the input box looks similar this:

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

This is our original worksheet:

InputBox3

Bob has UserID number i, Jack has UserID number 2 and Jane has userID number 3. We have inserted a Command box to inquire for the salesperson's userID. We volition likewise use the VBA "IF" function to decide which columns to hibernate 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:

          Individual Sub CommandButton1_Click()Dim inputData As String                      '                      ' Become 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").Subconscious = True                      Columns("A:C").Hidden = False                      Columns("D").Hidden = False                      ElseIf inputData = 2 So                      '                      ' If and so, display Jack'south sales                      '                      Columns("F").Hidden = True                      Columns("D").Hidden = True                      Columns("A:C").Hidden = False                      Columns("East").Hidden = Faux                      ElseIf inputData = 3 Then                      '                      ' If so, display Jane'southward sales                      '                      Columns("D:East").Hidden = True                      Columns("A:C").Subconscious = False                      Columns("F").Hidden = False                      Cease 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 i, for Bob then the resultant worksheet looks like this:

InputBox4

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

InputBox5

If you are not sure how to create the command push button or how to create and record macros, and so the Introduction to VBA Macros course will accept y'all step-by-step through the procedure of creating macros and using VBA in Excel.

Create a simple InputBox for Text Input

If we use type ii to allow the salesperson to enter their name instead of salesID, and then the lawmaking for the input box volition await like this:

inputData = Application.InputBox("Enter your name:", "Input Box Text", Blazon:=1)

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

InputBox5

And when Jane enters her proper noun, the worksheet volition await like this:

InputBox6

Information input really allows you to create very powerful applications using Excel VBA. To learn to unlock the true power of Excel, take a expect at the Visual Basic for Excel – Microsoft VBA Excel Tutorial. This certified grade will show you how to create macros, functions and procedures that volition take your utilise of Excel to the side by side level.