Many moons ago, Dave Peterson created a sample Excel worksheet data entry form and kindly shared it on the Contextures website.
In Dave’s original form, users could add records on the data entry sheet, and click a button to go to the database sheet, where they could review or edit the order records.
I’ve created a couple of versions since then, including an Excel data entry form that allowed users to view, add and update records, while staying on the data entry sheet.
Mark the Required Cells
Here’s a screen shot of the latest version of the workbook, with new features that were suggested in the comments for the previous version.
Mark Required Data Entry Cells
The first new feature is the ability to mark which cells are required.
- In column E, add an X beside the cells that are mandatory, and leave the optional rows blank.
- Conditional formatting colours the required cells in green, and the optional cells in grey.
You can hide columns E and F before using the workbook.
Select a Specific Record
In the new version, you can select a specific record from the drop down list in cell L3, to view or edit that record.
Before using the workbook, hide column M, that has a formula to calculate the record number for the selected order.
Use the Navigation Buttons
Or, as in previous versions, you can click the navigation buttons to move forward or back through the records, based on the row numbers in the database.
Download the Data Entry Form File
The zipped sample workbook can be downloaded from the Date Entry and Update form page on my Contextures site. On that page, go to the Download section, look for Version 1
After you open the workbook, enable macros, so the buttons and event code will work correctly.
There is an instruction sheet in the workbook, that describes how to navigate, add and update records, and maintain or modify the named ranges.
___________
Hi Pete,
You’re welcome, and thanks for your comments. I don’t have a sample file that shows how to pull selected data into a user form, but you could modify the basic user form shown here:
http://www.contextures.com/xlUserForm02.html
Use the AfterUpdate event on the Part ID combo box, to fill the other fields with related data.
Instead of an “Add this part” button, use an “Update this part” button, that adds to the comment field in the stored data.
Debra
Thanks a lot for this example, it actually helped me quite a bit in a project that I just completed.
My project involved displaying approx. 30 fields on the input form, so one small tweak that I made was defining each field with a name on the worksheet (similar to what you did with CurrRec). My code that populates the input fields could then reference the name, not the cell. The advantage of doing it this way was that if I needed to move fields around on the worksheet – which I did quite a bit during development – I didn’t have to keep changing the underlying field population code.
Just want to share that idea – thanks again for your continuing work on this example!
Jim
Thanks Jim, great tip!
I am very interested in seeing how Jim Rac was able to use named cells instead of using CurrRec. Could someone show me how this was done please.
Hi Gary, I’ve been trying to figure out a way to have an identifier that automatically increments. Can you share your method? I’m new to VBA. Thanks.
Please Help me
I’m using version 3 … I’m added a new fields … but when select Id no .. I cant see my update in a new fields
Thanks ..
Hi Moe;
I have same issue with the Data Entry (Database). I added some data field, and when I’m selecting the “Select ID” (Select a Specific Record), the data fields don’t update with the new information.
If you got the answer to this problem I will appreciate if you send me a copy of the XLS file and the complete CODE.
Thanks in advance for your help.
Hey Percy and Moe,
I am having an issue with the Select Part ID box as well. When I click to select ID it doesn’t populate the fields at all. What did you do to fix this issue?