How can you make it easy for people to enter and edit data in Excel, but keep them away from the data storage worksheet?
Last year, I posted a Worksheet Data Entry Form in Excel, where users could enter and view Excel data. It was based on a worksheet data entry form that Dave Peterson created.
I’ve created a new version, where users can enter, view and edit the Excel data.
Version 1: Add New Records
In Dave’s original worksheet data entry form, users could add records on the data entry worksheet, and click a button to go to the database sheet, and review or edit the order records.
Version 2: View Existing Records
In version 2, I added a few buttons to Dave’s workbook, to allow users to scroll through the existing records.
With the navigation buttons, you could go to the first, previous, next or last record, or type a record number, to go to a specific record.
Version 3: Update Existing Records
In the latest version of the Excel Worksheet Data Entry form, I’ve added an update feature.
As in the previous version, there are data validation drop down lists, to select Item and Location.
The Price calculation is based on a VLOOKUP formula, and the Total formula multiplies the quantity by the price.
After you select a record, you can change its data, then click the Update button to copy those changes to the database.
For example, in the record shown above, if you discovered that there was an error, you could change the quantity from 500 to 200. The Total formula would automatically recalculate, to show the new total of $200.00.
Then, click the Update button, and the revised quantity and total would appear in that record on the database sheet.
The Update Code
Before updating the database record, the Update code checks to see of all the data entry cells are filled in. If they aren’t, a warning message appears, and the macro stops running. This prevents you from accidentally overwriting an existing record with blank cells.
If all the data entry cells are filled in, the code:
- writes the current date and time in the applicable row of the database
- adds the User Name from the Excel application
- copies the data to the database
- clears the data entry cells
Then, with a cleared data entry sheet, you can go on to add, view and edit other records, or save and close the workbook.
Download the Sample File
The zipped sample workbook, in Excel 2003 format, can be downloaded from the Contextures website: Worksheet Data Entry Form
___________
@amit The form is designed to update based on a unique Order number. In the stored data, would be several records with the same product names, so it would be difficult to pull the correct record to update. You could filter the stored data on the PartsData sheet, and make the changes there.
@Rajesh I’ll post a new article later this week, explaining how to add new fields in the data entry sheet.
@Steve The Order ID and Quantity cells have data validation that requires whole numbers in a specific range. You can change or remove the data validation on those cells.
@Sansanouk, there’s an updated version of the form, where you can leave some cells blank:
http://blog.contextures.com/archives/2010/10/01/new-improved-excel-data-entry-form/
Debra,
I am using Excel Data Entry Worksheet to create a database to store information about excess computers.
However i am unable to figure out how to Update Entries with using a different solution than the one you used.
I like to update button but don’t like the View Records.
Wanted to have a update button on the database spreadsheet like you have to go to the main page.
Other issues i am running into is.
When i protected the work sheet, the entry is entered into the database but the contents is not cleared since its protected
I need to protected the worksheet, which allows me to tab between the rows i am entering the data in.
Thank you,
Travis T
This is brilliant! I know nothing about VBA but with your easy instructions have turned this template into a survey for a field study. Thanks!!
Hi Debra, This “Edit Records in Excel Worksheet Data Entry Form” is exactly what I have been looking for. Thank you.
But I can’t find the instructions to make modifications…can you please point me in the right direction? I need to add additional fields.
Susan
Hi Debra, This “Edit Records in Excel Worksheet Data Entry Form” is exactly what I have been looking for. Thank you.
But I can’t find the instructions to make modifications…can you please point me in the right direction? I need to add additional fields.
Susan
Hi Debra – I really like what you have created – just wondering if a later version of excel would use different code in certain line items? It runs fine in later versions of excel, but if I coded in excel 2010 for example, would I need to consider alternatives in the code?
Michael
This code help for me thank you very much. But i want to prevent entering duplicate values to database by focusing on one cell content. pls help