Input Box in Excel VBA

Have you ever had an Excel worksheet that needed specific input from the user for some calculations? Most applications rely on data inputs from users to determine various functions or procedures. Excel has a number of functions that allow you to gather user input for your applications. This article will show you how to use the Excel VBA InputBox function to get user input for your worksheets.

An InputBox is a function that allows the user to enter a value into a dialog box. The result of an InputBox is Stored Normally to a variable and remembered for later use in a procedure.

Note: result of an InputBox is always a String Value

Follow these steps to insert an InputBox function

Note: you can skip step 1 and step 2 if the DEVELOPER tab is already open.

STEP-1 Right click anywhere on the ribbon and select “Customize the Ribbon” from the drop down menu.

Customize The Ribbon

STEP-2 An option dialog box will open. Click on the DEVELOPER check box (it is under “Customize the Ribbon Main Tabs”) and press ok.

Add Developer Tab

STEP-3 The developer tap is now visible and is present next to the view tab on the top menu bar. Now click on the DEVELOPER tab and select “Insert“.

Insert Controls

STEP-4 Click on the Command Button in the ActiveX Controls.

Command Button ActiveX

STEP-5 Drag the command button on your Excel worksheet.

Note: you can change the color of the command button, the font, font style font color and even the name of the command button by right clicking on it and going to properties (make sure design mode is selected)

STEP-6 Right click on the command button (make sure the Design mode is selected). From the drop down menu select “View Code”.

A new window (Visual Basic Editor) will open which will have a dialog box in the center. You will write the code in this dialog box.

Command Button View Code

Note: you can also double click on your command button (make sure the Design mode is selected) to open the new window (Visual Basic Editor).

STEP-7 First, declare the variable myValue of type Variant in the dialog box.

Dim myValue As Variant

IMPORTANT: WRITE THIS LINE OF CODE BETWEEN Private Sub and End Sub. OTHERWSE IT WILL NOT WORK

Note: we use a variable of type Variant Here Because a Variant variable can hold ANY type of value. This way the user can enter text, numbers, etc. myValue is the name of the variable you can name your variable anything you like.

STEP-8 Now add the following line of code to show the inputbox.

myValue = InputBox(“Enter a number”)

Note: add these line of code below the previous one.

STEP-9 Write the value of the variable myValue to cell B5 (you can write the value to any cell you like)

Range(“B5”).Value=myValue

InputBox VBA code

After writing the above code you can close the window (Visual Basic Editor) from the upper right. Don’t worry Excel won’t close.

STEP-10

This will be the result if you entered the value 2313 in the InputBox.

Button Result Value

STEP-11

Now suppose you want to change the title of the InputBox function. You can use the InputBox function optional argument to do this. The following code will allow you to change the title of the InputBox function

myValue = InputBox(“Enter a number”,”Hi”,1)

Input Code

Note: 1 is the default value. The default value will be used if the user has not provided any other input. The words in the first curly braces will be the message displayed inside the InputBox and the words in the second curly braces will be the title of the InputBox.

STEP-12 The result will be an InputBox having the title Hi. That’s it you have now successfully created an InputBox function.

Note: Place your cursor on the Dialog box in the Visual Basic Editor and click F1 for help on other optional arguments

Input Box Dialog Box

Template

You can download the Template here – Download