Adding new data records to an Excel list

Using an input form to accept new data

Forms allow spreadsheet users to enter data which is then added to a list, possibly on another worksheet. The form may incorporate some method of checking to ensure that the new record is valid.

Two alternative processes are described here, one using a visual basic form to accept the data and another taking the data from a form displayed within a range of cells. In both examples the data is appended to a list on another worksheet.

(part one) A simple Excel visual basic input form

The user clicks a button [Add] contained on a worksheet. A dialog box (Figure 2) then appears, requesting three pieces of information. When the user has completed the fields and clicked the OK button (or pressed <Enter>) the form is closed and the data added to a list on another worksheet.

Figure 1. A worksheet called 'Forms1' containing the variables used by the form and macro.	Figure 2. An Input dialog box which is opened when the user clicks on the [Add] button.

Steps to create the form.

Open the visual basic editor ([Developer] Visual Basic ) and create a new form (Insert | UserForm). From the control toolbox, click on the TextBox icon and drag a new field onto the blank form.

Each TextBox has a series of properties to define it. Within the 'Properties' box, give the fields memorable names (fName, fAmount and fDate) and link the 'controlsource' property of each one to a worksheet cell ('=Forms1!C1'). This provides a field with an initial default value (such as todays date).

Figure 3. The Project window within the VBA editor, enabling you  to click and select either the form or the module of VB code.

Select a CommandButton control from the Forms toolbox (View | Toolbox) and place it on the bottom right corner of the form. Change it's name property to something such as fConfirm.

A small amount of visual basic code needs to be linked to the button to define it's purpose. Right click on the new CommandButton and select View Code from the short menu.

This code runs when the [OK] button on the form is clicked. It looks in cell B3 to find the last row of data on the table (NewRow). It then checks to see if the fName field has been entered. If the length is zero it displays a warning message and returns the user to the field. Finally it adds the three field values to the next empty row of cells on the 'results' worksheet (see Figure 4).

The ControlSource property for the field controls is not essential and it does have complications. The relationship is two-way and so any formula in the linked worksheet cell gets overwritten with the text that is entered in the form control.

A new visual basic macro is required to open the form and display any required default values in the fields. Within the visual basic editor, insert a new module (and if necessary double click on its name to open it).

The first few lines of code set the values of the worksheet cells to which the form controls are subsequently linked. The value of the last row number on the data table is detected; the name is reset as empty; the amount to zero; and the date to todays date.

The code then opens the form and tells the cursor to move to the fName field.

This code must be linked to an [Add] button on the 'main' worksheet. Once the code is written, close the VB editor and select the 'main' worksheet. The 'Forms' toolbar is needed in order to create the [Add] button. Select [Developer] Controls| Insert | Form controls. Click on the button icon and drag a button shape on your worksheet. Then select the name of your 'Button1_Click' macro to assign it to the button.

Figure 4. A worksheet called 'results' containing the list to which each new set of data is appended.	Figure 5. A worksheet called 'forms2' containing an alternative input form within a range of cells (B3:B5) and an [Add] button to append the data to a list.

Click here to see an example of exporting the contents of a form directly into a text file

(part two) An Excel input form within a worksheet

It isn't necessary to create a VBA Form to collect the data. The user could be asked to enter it into a range of cells on a worksheet as shown in the Figure 5 above. (In a real life example the error checking bits and pieces in columns C and D should be placed elsewhere or hidden).

A similar [Add] button is required to append the data to a list on the 'table' worksheet. The following macro should be typed into a new module in the Visual Basic Editor and then linked to a button placed on your worksheet.

The macro reads the last row number from cell D7 and checks cell C6 to see if any errors were identified in the form data. If any errors are identified a warning message is shown and then the macro terminates. If all is well however the form values are added to the end of the list on the 'table' worksheet (figure 4) and the form is cleared, ready for new data.

Some checks can be added to ensure that the values entered in the form meet your anticipated requirements.

The 'Add' macro will not append the data to the list if any of the error checks are positive.

Some users prefer not having to click on a button each time they wish to add a new record. A macro keyboard shortcut can be added to run either of the 'Add' macros illustrated on this page. Select [Developer] Code | Macros and then select your macro name and click the Options... button. This presents you with a field in which you can choose a letter (such as 'd') which when pressed in combination with the <Ctrl> key will run the 'Add' macro. It is best not to use letters such as c, v, f or a which may be familiar to users for other standard windows shortcuts.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.