Have you ever wondered how to create a data entry box to enter data into a particular cell in Excel? Sometimes you want to do this to guide a user so the data always ends up in the correct cell or perhaps you want to start working in VBA Excel with a good example. This article describes how to create an input box in Excel using VBA and the exercise is a good illustration of VBA usage for the newcomer. Just to let you know, we created a VBA course to help you learn the basics.

The VBA code structure

Before writing out code we'll explore how Visual Basic code is structured by creating an initial example using Excel's macro recorder. So launch Excel with a new worksheet. We're going to create VBA code using the macro recorder to add the word "Test" into cell A5. So start the macro recorder. To do this in Excel 2003 it's Tools, Macros, Record New Macro. In Excel 2007/2010 it's the View Tab, click the Macro down arrow, Record Macro. Give the macro the name TEST and start recording. Then select cell A5, type in the text TEST and press enter key. That's the macro finished, so stop recording. In Excel 2003 it's the blue stop button on the macro recording toolbar. In 2007/2010 it's the blue stop button on the lower status bar, far left.

Now we can test the macro, so delete the text in cell A5 and select any other cell. Then run the macro TEST. To do this open the macro panel. in Excel 2003 it's Tools, Macros, Macro. For Excel 2007/2010 its View Tab, Macro. In the macro panel select the macro and click Run. The macro then adds the word TEST to cell A5.

To see how the macro has been created we'll look at the Visual Basic code by editing the macro. So we want to again open the macro panel and select the macro, but don't run it. Instead in the macro panel, with the macro selected, click Edit. The VBA editor opens. You'll see your macro called TEST in the right of the screen. You can see the macro name and the VBA code for the macro actions. Notice too that the code has the first line SUB followed by the macro name, and a closing line END SUB.

We're going to create our own VBA code just under TEST. So select the line just after END SUB and type in the following line of code. We'll put the code in capitals here so you can see it easily but it doesn't need to be in caps. Type in SUB INPUT1 then press Enter. Notice that the VBA code is changed slightly to add an open and close bracket after this first line, and an END SUB line is added in the line after the cursor. All VBA code has similar opening and closing lines.

Next type in these three additional lines of code
DIM MYNUMBER
MYNUMBER=INPUTBOX("PLEASE ENTER NUMBER","MY NUMBER INPUT BOX")
RANGE("A1").VALUE=MYNUMBER

That's the code all finished. So our VBA code or subroutine has the name INPUT1 and it's stored within the Excel file in the same place as our earlier example. Save the file with an appropriate name, exit the VBA screen by clicking the top right cross, and you'll return to the regular Excel screen.

To test the VBA code we've just written you need to again open the macro panel. This time you'll see INPUT1 listed. Select it and click Run. Your Input box should appear. Try typing any number, then click OK in the Input box, and the number should now be placed in cell A1. So the VBA works successfully.

If you want to edit the VBA code, choose the macro panel again, click once on INPUT1 to select it, then click Edit. The VBA window opens again showing your code in the panel on the right. Try carefully changing the "A1" to "D5" and then close the VBA panel to return to regular Excel view. Then run the VBA code again, type in a different number, and this time the data should be added to cell D5.

To finish this exercise we want to add some comments to the VBA to remind yourself what the code does. So open the macro panel, select INPUT1 and click Edit. In the VBA code add an extra line after the opening SUB line. To do this place the cursor at the end of the line and press Enter so the cursor is in a new line. Now type an apostrophe. (This is usually on the same key as the @ symbol). After the apostrophe type OPENS INPUT BOX FOR USER TO ENTER NUMBER WHICH IS THEN ENTERED INTO CELL D5. Notice that this entire line is coloured green. This means that the line is a comment, and will be ignored by the VBA. Then close the VBA editor.

So that completes our example of creating a user input box using Excel VBA. Using the box guarantees the data always goes to cell D5. You can use other input boxes to enter data into other cells or have an input box enter data into successive cells. Interested in learning more about Excel VBA? A really effective way is to attend a training course.This way you can really build on your VBA skills in a short time.