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
___________
sir,
in this Edit Records in Excel Worksheet Data Entry Form i can make only 60 cells for data entry in one page.
but i need to enter more that 170 cells for entry.
How can i increase no of cells upto 170 cells to enter my data?
can u help me?
ebin charles paul
First of all, thank you very much for this very helpful example!
I am making my own changes to this example to use it as a project list. I’m only beginning with VBA code, so could you give me some indication how I could implement the following?
I would like to add a unique ‘project number’ for each new entry in the database sheet.
example: Currently there are 15 projects in the database. When a new project is added, I would like to have a cell showing “Project-016? (even when older projects are removed from the database, the next number should be 016).
I would be very grateful if you could point me in the right direction.
best regards,
Luke
I don’t finish my studies in VB to give good suggestion towards
this regard.
But since i like your project and it is useful to my job, i used it.
if you can help me to enter morethan 170 cells at single entry, i’ll be greatful to you.
thank you
regards
ebincharles
Thank you so much for the example. I am using it as the basis for a spreadsheet I am developing. I am trying to set up a data input screen for signing in and signing out. I would like to have one database that has name, date, time in, time out in one record. So far, I have created an input screen to record time in. When the individual signs out – how can I locate the individual’s record for signing in and then populate the time out field?
Thank you for your thoughts,
Tracy
Thanks.. I will look this up.. Wanted something similar to this..
Guys,
This is a great example of an excel database which is extremely user friendly.
I have amended the data to except 30 input criteria and everything is Ok with the xception to the formula for dates which is cleared every time ? or cost in the example..
Could you give me a clue as to why ??
BR
Gary