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
Thanks to Dave Peterson, who created the
first version of the data
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.
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
Enter New Data
To enter a record:
- Go to the data entry worksheet (named Input)
- Type a value in each mandatory data entry cell, or select from
a drop down list
- The Price calculation is based on a VLOOKUP
formula, and the Total formula multiplies the quantity by the
- Click the Add button, to copy the record to the database
of stored records, located on the PartsData sheet.
- If mandatory fields are not filled in, a warning message will
appear, and the record is not added to the database.
- If the Order ID is already in the database, you'll see a warning.
- If all the mandatory fields have been filled in, the data entry
cells are automatically cleared, so you're ready to enter another
Navigate Through Stored Records
To see the records that are stored on the database sheet, use the
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
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.
- Select the yellow cell under the "Select Order ID" heading.
- Click the drop down arrow, and select an ID number in the list
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:
- DataEntryClear -- cells D5:D8 on the Input sheet, where
data is typed or selected from drop down lists
- IDNum -- Cell D5 on the Input sheet, where the Order ID
- NextID -- Cell H1 on the LookupLists sheet, which calculates
the next available ID number, using the following formula:
The New button runs a macro that clears the DataEntryClear range,
and puts the next available ID number in the IDNum cell.
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
- adds the User Name from the Excel application
- copies the revised data to the database
- clears the data entry cells on the Input sheet
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.
- On the data entry sheet, select a cell with a field name that
you want to change
- Type a new name for the field
- Switch to the Database worksheet
- Make the same change to the field name in the column headings.
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).
- On the data dntry sheet, unhide columns E:F. These contain markers
and formulas, to control which fields are mandatory, and which are
- 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.
- Right-click on one of the selected cells, and click Insert.
- In the Insert window, select Shift Cells Down, and click OK
- In column F, use the Fill Handle, to copy the formula from the
- 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.
- 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
- Add more fields, if you need them, and hide columns E:F when you're
Update the 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.
- To view the code for a navigation button, right-click on it, and
click Assign Macro
- In the Assign Macro dialog box, click Edit.
- 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.
NOTE: In some versions of the workbook, the constants
are not shown at the top, and you will have to change the column
numbers in the code on the Input sheet module, modData and modViewData.
- On modViewData and the Input sheet module, look for code that
is copying data from the historyWks sheet, e.g.:
- Change those number from 3 and 6, if necessary, to copy that 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:
Watch the Excel Data Entry Form Customize Video
To see a quick overview of how you can customize the Excel Data Entry
Form, you can watch this short video.
Download the Excel Data Entry Form
There are 3 versions that you can download:
- Version 1 has Add and Update buttons, so you can view, edit and
add records. You can download a copy of the
Data Entry Form and customize it to suit your data. The file
is in Excel 2003 format, and is zipped. After you unzip the file
and open it, enable macros, so you can use the worksheet buttons.
- Version 2 has Add, Update, New and Delete buttons, so you can
view, edit, add, start new, and delete records. Checks database
for Order ID, to prevent duplicates. Use this one with caution,
because the deleted records are gone forever! You can read
the details on this version, and download
the Data Entry Form With Delete Button.
- Version 3 has two
data entry areas, and data is copied to and from these cells
by using links on other worksheets. You can expand on this sample
file, using the same technique to add more data entry ranges, and
fields in your database sheet.
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