Contextures

Excel Data Entry and Update Form

With this Excel data entry form, you can enter or update records on the data entry worksheet (named Input).

Excel Data Entry and Update Form Overview

Thanks to Dave Peterson, who created the first version of the data entry form.

With this Excel data entry form, you can enter or update records on the data entry worksheet (named Input). The form's features help you enter your data quickly and accurately:

  • Drop down lists, created with data validation, help users enter the correct information.
  • Make some fields mandatory, so users can't leave them blank.
  • Click a button, to save the record and clear the data entry cells.
  • Use navigation buttons to see the saved data
  • Select a specific record to view or update.

    excel data entry form

Data Entry Form Workbook Setup

There are 3 worksheets used in the data entry and data storage process:

  • Input - the data entry sheet. This can be protected, so users are only able to enter data in the unlocked cells. In the sample file, the sheet is protected, with no password.
  • PartsData - the database sheet. This can be hidden, so users don't accidentally delete the data or change the layout
  • LookupLists - item lists used for data validation drop down lists.

Enter New Data

To enter a record:

  1. Go to the data entry worksheet (named Input)
  2. Type a value in each mandatory data entry cell, or select from a drop down list
  3. The Price calculation is based on a VLOOKUP formula, and the Total formula multiplies the quantity by the price.
  4. Click the Add button, to copy the record to the database of stored records, located on the PartsData sheet.
  5. If mandatory fields are not filled in, a warning message will appear, and the record is not added to the database.
  6. If the Order ID is already in the database, you'll see a warning.
  7. If all the mandatory fields have been filled in, the data entry cells are automatically cleared, so you're ready to enter another new record.

select from drop down

Navigate Through Stored Records

To see the records that are stored on the database sheet, use the Navigation buttons.

Click the navigation buttons to go to:

  • First record |<
  • Previous record <
  • Next record >
  • Last record >|

Or, type a record number in the yellow cell, to view that record number

data entry form navigation

Select a Specific Record

Instead of scrolling through all the records, you can select a specific record, based on its ID number. In the sample workbook, the Order number is used as the ID number.

  1. Select the yellow cell under the "Select Order ID" heading.
  2. Click the drop down arrow, and select an ID number in the list

select data entry record

Update a Record

While you are viewing a record, you can change its data, then click the Update button to copy those changes to the database.

For example, if you discovered that there was an error in the order quantity, you could change it. The Total formula on the Input sheet would automatically recalculate, to show the revised amount.

When you're finished changing the record, click the Update button, and the revised data will appear in that record on the database sheet.

Note: If the Order ID is not in the database, you'll see a warning message, that asks if you want to add as a new record.

Start a New Record

You can also add a "New" button to the data entry form. This feature has been added the Version 2 sample file, which has Add, Update, New and Delete buttons

In this version, 3 named ranges were added:

  1. DataEntryClear -- cells D5:D8 on the Input sheet, where data is typed or selected from drop down lists

    data entry range to be cleared

  2. IDNum -- Cell D5 on the Input sheet, where the Order ID is entered

    id number

  3. NextID -- Cell H1 on the LookupLists sheet, which calculates the next available ID number, using the following formula:
    =IFERROR(MAX(PartsData!C:C)+1,0)

    calculate next id number

The New button runs a macro that clears the DataEntryClear range, and puts the next available ID number in the IDNum cell.

click the new button

The Update Code

Before updating the database record, the Update code checks to see of all the mandatory data entry cells are filled in. If they aren't, a message appears, and the code stops running. This prevents you from accidentally overwriting an existing record with blank cells.

If all the data entry cells are filled in, the Update code:

  • puts the current date and time in the selected record's row of the database
  • adds the User Name from the Excel application
  • copies the revised data to the database
  • clears the data entry cells on the Input sheet

Customize the Data Entry Form

To see a quick overview of how you can customize the Excel Data Entry Form, you can watch these 2 short video. There are written instructions below the video:

Change Field Names

Add New Fields

Update Navigation Code for New Fields

Add New Input Areas

Change Sheet Names

Make Changes to the Data Entry Form

How to Change Sheet Names in the Macros

Change Field Names

After you download the sample Excel data entry form workbook, you can customize it by changing the field names, to match your data.

  1. On the data entry sheet, select a cell with a field name that you want to change
  2. Type a new name for the field

    change field names

  3. Switch to the Database worksheet
  4. Make the same change to the field name in the column headings.

change database fields

Add New Fields

If you need more fields that are in the sample Excel data entry workbook, you can add as many new fields as you need (up to the column limit in your version of Excel).

  1. On the data entry sheet, unhide columns E:F. These contain markers and formulas, to control which fields are mandatory, and which are optional

    copy formula down

  2. Select a label cell, and the 3 cells to the right, where you want to insert the new field. The new field will be inserted directed above the selected cells.
  3. Right-click on one of the selected cells, and click Insert.
  4. In the Insert window, select Shift Cells Down, and click OK

    insert cells

  5. In column F, use the Fill Handle, to copy the formula from the row above.

    copy formula

  6. If the new row copies the data validation from the row above, you can change it, to use a different list as the source. Or, clear the data validation, so users can type any value into the cell.

    clear data validation

  7. On the PartsData sheet, insert a new column, between the same two fields where you added the new field on the Input sheet. In this example, the new field is inserted between the Part and Location columns.

    add field column

  8. Add more fields, if you need them, and hide columns E:F when you're finished.

Update Navigation Code for New Fields

If you add new fields, you'll need to change the range of cells that are copied, when the navigation arrows are clicked on the worksheet.

  1. To view the code for a navigation button, right-click on it, and click Assign Macro

    assign macro

  2. In the Assign Macro dialog box, click Edit.

    Assign Macro dialog box, click Edit

  3. Scroll to the top of the module, and change constant that stores the number of data entry cells -- lCellsDE. In the screen shot below, there is a new field, so the 6 will be changed to 7.

    change constant that stores the number of data entry cells

    NOTE: In some versions of the workbook, the constants are not shown at the top. If you don't see the constants, follow the steps below, to change the column numbers in the code

  • On modViewData and the Input sheet module, look for code that is copying data from the historyWks sheet. In the code below, the copying starts in column 3 and ends in column 6:
    historyWks.Range(historyWks.Cells(lRecRow, 3), _
         historyWks.Cells(lRecRow, 6)).Copy
  • Change those numbers from 3 and 6, if necessary, to copy the data that you want to paste onto the Input sheet when viewing a record. Make sure that you don't paste over the formulas on the Input sheet!
  • On modData, change the code that copies data to hostoryWks, if necessary. Look for the lines where the starting column for the paste are shown:
    .Cells(nextRow, 3).PasteSpecial _
          Paste:=xlPasteValues, Transpose:=True

Add New Input Areas

In the sample workbooks available to download, one of the files has two input areas, and additional sheets that are linked to those input areas.

two input areas

If you need more input areas, follow the steps below, and use the setup and naming structure for the existing input areas as a guide. In this example, a third input area is being added:

  • Input sheet: Create new input cells, and name that range as InputC
  • PartsData sheet: Add columns for new fields
  • Input Links sheet, add cells in row 4, copy formulas across in rows 5, 6, 9
    • Adjust the InputCopy named range to include the new formula cells
  • SelRecordLinks sheet, set up formulas for new input range (use same cells as on Input sheet)
    • Name the value cells as SelValC
  • In the code (Alt+F11), add lines for new input area (InputC), wherever there is code for InputA and InputB. To find them, press Ctrl+F, to open the Find window, Find: InputA, Search: Current Project. Then, copy the line for InputB, paste, and change to InputC.
  • find InputB in code

Change Sheet Names

If you change the sheet names in your copy of the Worksheet Data Entry Form workbook, follow these steps to modify the macro code, so it will recognize the revised sheets.

NOTE: Watch these steps in the How to Change Sheet Names in the Code video, above.

View the Macro Code

REMINDER: Make a backup copy of your workbook, before changing the macro code, or making any other major changes.

To see the macro code, right-click on the Input sheet tab, and click View Code

view code command

At the left, in the Project Explorer, the workbook name is listed, with folders that contain its Excel objects and code modules.

workbook in project explorer

Sheet Names

For each worksheet, there is a Code name, followed by the name that you see on the sheet tab (in brackets). For example:

  • Code name wksPartsDataEntry, sheet tab name is Input
  • Code name is wksPartsData, sheet tab name is PartsData

In the Worksheet Data Entry Form sample file, the sheet tab names are used in the macro code. For example:

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("PartsData")

Choose a Macro Update Option

If you change the sheet tab names, use one of these options for changing the names in the macros:

  1. Change All Sheet Tab Names in Macros - temporary fix
  2. Change Macros to use Code Names - permanent fix

Later, if the sheet tab names are changed again:

  • If you used option 1, repeat those steps, to update with new sheet tab names.
  • If you used option 2, the macros will continue to run correctly, no update needed

Start the Update

For either option, follow these steps to get started:

  • In the Project Explorer list, right-click on one of the modules or sheet in your workbook, and click View Code

view the code

  • To open the Replace dialog box, press Ctrl + H (or click the Edit menu, and click Replace)

open the Replace dialog

Follow the Option Steps

Next, follow the stops for the option that you're using:

  1. Change All Sheet Tab Names in Macros - temporary fix
  2. Change Macros to use Code Names - permanent fix
Option 1 - Change All Sheet Tab Names in Macros

If you are using option 1, follow these steps.

Warning: Be sure to include the double quotes, or other parts of the code might be accidentally replaced.

  • In the Find What box, type the old sheet tab name inside double quotes
  • In the Replace With box, type the new sheet tab name inside double quotes
  • In the Search options, click on Current Project
  • Click the Replace All button

open the Replace dialog

  • Click OK when the message appears, showing the number of replacements made

open the Replace dialog

  • If other sheet tab names have been changed, repeat all the steps, for each remaining sheet
Option 2 - Change Macros to use Code Names

If you are using option 2, follow these steps

TIP: You can select the code on the worksheet, and Ctrl+C to copy it. Then, use Ctrl+V in the Replace Dialog, to paste the text.

  • In the Find What box, type this code, using the old sheet tab name inside the double quotes:
    • Worksheets("Input")
  • In the Replace With box, , type the sheet's Code Name:
    • wksPartsDataEntry
  • In the Search options, click on Current Project
  • Click the Replace All button

open the Replace dialog

  • Click OK when the message appears, showing the number of replacements made

open the Replace dialog

  • If other sheet tab names have been changed, repeat all the steps, for each remaining sheet

Database Start Row

In most of the sample workbooks, the parts database starts in row 1 on the PartsData sheet. The code calculates record numbers and last record number, by adding a 1 to the row number.

If your database headings are NOT in row 1, use the Set Data Start Row sample file (#5 in the download section below).

In that workbook, go to the modData code module, and change the DataStartRow setting, to match the row where your data headings are located.

Download the Workbook

The sample files are in xlsm format, and are zipped. After you unzip the file and open it, enable macros, so you can use the worksheet buttons.

1. Data Entry Form - Add/Update: View, edit and add records.

2. Data Entry Form With Delete. Add, Update, New and Delete buttons. Checks database for Order ID, to prevent duplicates. Use this version with caution, because the deleted records are gone forever. Click here to read the details on this version

3. Data Entry Form With Delete and Limit. Like the Delete version above, but with a limit on the items stored per location. Only the available locations are shown in the drop down list.

4. Data Entry Form - 2 Entry Sections: Data is entered in two areas, and copied to and from these cells by using links on other worksheets. If required, use the same technique to add more data entry ranges and fields in a workbook.

5. Data Entry Form - Add/Update - Set Data Start Row: View, edit and add records. Change the DataStartRow setting, if your data headings are in a different row.

6. Data Entry Form With Delete and Alert. Like the Delete version (2) above, but with alerts when new or updated data is entered. Data entry labels change colour, and message appears below buttons, reminding people to click Add or Update.

More Versions: For more versions of the Worksheet Data Entry form, go to Excel Data Entry Form - Basic

More Tutorials

Forms -- Survey Form with Option Buttons

Forms -- Excel Data Entry Worksheet Form

Forms -- Print Selected Items in Order Form

Forms -- Excel Data Entry and Update Form

Basic Excel UserForm

Create an Excel UserForm with Combo Boxes

Last updated: March 3, 2021 3:56 PM