Input Box in Excel VBA
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.
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.
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“.
STEP-4 Click on the Command Button in the ActiveX Controls.
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.
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
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.
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)
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
Template
Further reading: Basic concepts Getting started with Excel Cell References
