How to use an Input Box in Excel

In this post, I’ll share how to collect data in an Excel spreadsheet using an Input Box. I’ll try to make it as easy as possible with lots of screenshots. The purpose of my example is to have someone click a button in Excel that collects data. That “input box”  will lead to a box that asks them to enter units and then a price value. After inputting those values, Excel will perform VBA code that will automatically enter the number of units in B1, the price value in currency in B2 and a formula result in B3.

Let’s get started. Using Excel’s Developer tab, I click the Insert menu then Command Button(Active X Control).

insert command button active x control developer tab excel

Then I left click and drag the shape and location of this command button.

command button

I then right click the command button and click View Code. This takes me to the Visual Basic editor in which I can create the VBA code to make all this happen. Switching between Excel and the code window can use the keyboard shortcut AlT+F11.

Active X command button view code right click

With the insertion point between the Private Sub CommandButton1() and End Sub lines I create variable declarations by typing:
Dim Units as Integer
Dim Price as Currency
Dim Total as Currency

Dim stands for dimension. It is used to create space or dimensions of allocated memory. It helps set up declaring a variable.
Dim declarations of varables

Now I’m going to set up an expression that will input the value of the Units.
I type
Units = InputBox(“How many units did you sell?”)
Range(“B1”).Value = Units

The user will get a prompt to enter units and then the variable I declared (Dim) is going to store that value in memory and enter it in cell B1.

Units command Button screenshot

Whatever the user enters in the InputBox will immediately go in cell B1. Also have to enter a value or else a null value will cause the code to debug.
Units input box Excel

Now I type the Price input prompt I want from the user.
Price = InputBox(“What Price?”)
Range(“B2”).Value = Price

Price = INputBox Excel what price

And this is what happens in Excel after for this portion of the code. User enter a price and the price is then entered automatically in cell B2.

Price Input Button Excel screenshot

Price Input Button Excel

The last line of code I type is a formula result.

Range(“B3”) = Units * Price
Command Button Total

I then create a caption for the Command Button by clicking on the properties button (from the Developer Tab) and type a caption I want the button to have. I need to make sure I’m in Design Mode to do this or else it will just want to run the macro.
Command Button Caption

Download my Excel keyboard shortcuts here. And if you like this post, make sure you sign up to receive others by joining my mailing list.

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s