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).
Then I left click and drag the shape and location of this 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.
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.
Now I’m going to set up an expression that will input the value of the Units.
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.
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.
Now I type the Price input prompt I want from the user.
Price = InputBox(“What Price?”)
Range(“B2”).Value = 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.
The last line of code I type is a formula result.
Range(“B3”) = Units * Price
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.
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.