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.
STEP1 Right click anywhere on the ribbon and select "Customize the Ribbon" from the drop down menu.
STEP2 An option dialog box will open. Click on the DEVELOPER check box (it is under "Customize the Ribbon Main Tabs") and press ok.
STEP3 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".
STEP4 Click on the Command Button in the ActiveX Controls.
STEP5 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)
STEP6 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.
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).
STEP7 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.
STEP8 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.
STEP9 Write the value of the variable myValue to cell B5 (you can write the value to any cell you like)
After writing the above code you can close the window (Visual Basic Editor) from the upper right. Don't worry Excel won't close.
This will be the result if you entered the value 2313 in the InputBox.
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)
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.
STEP12 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