Have you ever had an Excel worksheet that needed specific input from the user for some calculations? Most applications rely on 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.

Input Box

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.

The ribbon

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

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

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

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”.

ActiveX Control

Click on the Command Button in the ActiveX Controls.

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).

The code

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 a new window (Visual Basic Editor).

Variable declaration

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 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 variables anything you like.

Now add the following line of code to show the inputbox.

myValue = InputBox(“Enter a number”)

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

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.

The result

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

Function formatting

Now, suppose you want to change the title of the InputBox function. You can use the InputBox function’s 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 brace will be the message displayed inside the InputBox and the words in the second curly brace will be the title of the InputBox.

The result will be an InputBox with 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.

Related posts:

How to Use Visual Basic Editor in Excel How to Use Spin Button in Excel Do While Loop in Excel Vba