New Improved Excel Data Entry Form

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 the latest version of the workbook, with new features that were suggested in the comments for the previous version.

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.

data entry form

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.

dataentry02

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.

dataentry03

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.

___________

You may also like...

56 Responses

  1. Gerald Strever says:

    Brilliant! Thanks Debra, and keep up this great blog.

  2. Sherry says:

    Hi.Debra,

    Thank you for awesome “data form entry”…I already tried to use this sample to create my task but stuck at “select Order ID”. When I select it not update at “D5?.I already did all instruction as you mention .Pls advise me .One more thing why you create “PartDatabase Name” ,i connot related it.

  3. Sherry says:

    Hi .. Debra..

    I had change “Application.username” to “date” to hope i can filter by date on input sheet. Could you assist me to create filter by date for select and Update .

  4. MRNO says:

    Like the changes to this version of the form. Would like to know if it can be modified to have data entered in multiple columns and rows (ie; data entered in cells D5:D10 & P5:P10 & D20:D30)? Any suggestions or ideas?

  5. Gary says:

    Hi Debra,
    I have used this concept now and amended it to suit the particular application. Instead of ADD, I use “Copy” or “New” record but give the DB a unique tracker number which is used a reference to retrieve multiple rows/columns given certain criteria. One small change I added was to automatically increment the tracker number, this really helps keeping the records unique especially when I have in some cases >60 columns of data.
    Once again Debra, thanx this is a great WS.

  6. Thanks Gary, and your changes sound good. It’s important to keep the records unique if you’re going to update them!

  7. Contextures Blog » How to Customize the Excel Data Entry Form says:

    […] posted a few versions of the Excel Worksheet Data Entry Form, starting with the original version that Dave Peterson created. Thanks for your comments and […]

  8. Phil Birkenhead says:

    I have been using the db for a while now but need the new data to be inserted at the top of the partsdata worksheet rather than the bottom. After many amateur attempts to modify the code I’m still struggling. Im sure that the modification should be minimal but just can’t get it working.

  9. Phil, in the code, after the new data is copied to the partsdata sheet, you could move it to the top:

            .Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
            .Rows(nextRow).Cut
            .Rows(2).Insert Shift:=xlDown
    
  10. Phil says:

    Debra, thanks for the tip, as usual I tried to rewrite the code rather than simply add another step as you have explained. Now I can use the most recent data in a chart and as new data is input the chart will update. Works great in a dashboard.

  11. Peter Smyth says:

    Hi Debra,

    Thanks for your wonderful work. I was trying to accomplish the same in a user form ,as we know the user can add any sort of data with the use of form.Once the data has been updated and the user wants to query a part information or update the part information, then a excel form should pop up.
    In this form the user should enter the part number , and all the data for that part number should be displayed in the form with everything protected.
    This form could also have an option of update some comments.
    Can you suggest something on this please.
    Thanks,
    Pete

  12. 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

  13. Jim Rac says:

    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

  14. Thanks Jim, great tip!

  15. MRNO says:

    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.

  16. Laura says:

    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.

  17. Moe says:

    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 ..

    • Percy says:

      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.

      • Kristen Stone says:

        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?

  18. Kim says:

    Debra
    Thank you for all the great information you provide for Excel.
    How do you decide which format (Forms or Data Entry)to use when creating your spreedsheets? They seem to be similar yet from what little bit I understand I think there are vast differences. Thanks again for the great tips.

  19. Bryan says:

    Great tool, I have customized it for my needs. Two items I need code for if your so generous are first to prevent adding duplicate ID’s and second add delete button for current displayed record.

  20. Richard says:

    how can I add multiple part and location for 1 order ID…

    say for example there is 1 order ID with multiple part that is ordered

  21. Sam says:

    Hi Debra

    I am having the same problem as Moe (see above). I have added additional fields to the form and they feed through perfectly into the worksheet. Also when I used the view records buttons they work fine and pull through the relevant data.

    However, when I used the Select Order ID to select an old entry to update it only pulls through the data for the first few fields (i.e. the ones that were there to start with). I can see what the problem is but I can’t find where the code that is carrying out this function is so that I can attempt to correct it.

    Please can you help as it is the only thing that is preventing me from putting the amended form to really good use.

  22. Stephanie says:

    Same problem as Moe, Percy and Sam….anyone have the solution???

  23. SueB says:

    Hi Debra. Thanks for this great worksheet example. I am trying to adapt it to a different use. We need a data entry form with combo boxes to assist users who will be entering assessment reports with a lot of columns. They may be entering more than one record for a given assessment subject (person number). Can I adapt the form to do without the uniqueness check? We don’t need the ability to select a particular record.

    I also want to change some names, e.g. change PartsData to AssessData. I guess I need to change the VBA in line with the renaming, is that right? I’m not a VBA programmer – I saw your video showing how you can change the data entry simply by editing the form and the column names in the data tab, but I guess it’s not quite that simple when you want more extensive changes.

  24. Ravi GP says:

    Debra, thank you so much for your wonderful work. I’m trying to use your latest version for my requirement. I have 13 cells to capture data from. I know a bit of VBA programming, and I’ve been able to make required changes and make the Form work to my requirement.

    Here are the changes I’ve made.

    a)Module modViewData : Public Const lCellsDE As Long = 13

    b)Sub ViewLogFirst(), ViewLogUp(), ViewLogDown(), ViewLogLast() : Changed lCellsDE to lCellsDE+2
    (historyWks.Range(historyWks.Cells(lRecRow, lColHist), historyWks.Cells(lRecRow, lCellsDE + 2)).Copy)

    c)Object wksPartsDataEntry (Input) : Added 2 to lCellsDE (lCellsDE = rngDE.Cells.Count + 2)

    Though I’ve got it to work, I don’t quite understand the rationale behind (b) and (c) above. If I don’t make these changes, then while retrieving data the last two columns from data sheet do not get copied in to the input form. I’d greatly appreciate your help to clarify this.

    Thanks
    -Ravi GP

  25. Gidon says:

    This Excel data entry form is absolutely fantastic…thank you Debra for sharing it with the world! My challenge is that all of the input cells for the data entry form appear in the same column (i.e. column D) and I want to create a data entry form that can support input cells across multiple columns (i.e. column D, M, & U). I do not know visual basic but was able to modify the code to support adding new records and clearing the form. I can not however, figure out how to modify the code to support updating/retrieving existing records? Any ideas? Thanks in advance for your help, I sincerely appreciate it.

  26. Joakim says:

    Thanks for your great work! Without it I would have difficulties finishing my project in time. I have learned allot from studying your work, thanks!

    A small question to ask.

    I have extended the data entry form with more fields in the Input sheet to input additional data and also have allot of formulas in cells that do calculations based on the users answers. After adding the data and viewing the record, all my formulas are gone. My current understanding is that the formulas are not copied to OrderParts sheet, only the values. How can I add records and preserve the formulas as well?

  27. Joakim says:

    Forgot to add the detail that the formulas on Input sheet refers to other cells on same sheet, so if doing transpose copy to OrderParts and then back to Input sheet, I think there will be an #REF! error?

    • Joakim says:

      Fixed it! Added another line

      .Range(“AssetID”).PasteSpecial Paste:=xlPasteValues, Transpose:=True
      .Range(“AssetID”).PasteSpecial Paste:=xlPasteFormulas, Transpose:=True

      Then used absolute reference on all cells that had formulas. Works great now!

  28. Mike says:

    Debra,

    Can you help me tweak the code a bit to allow for data to be dropped into multiple, specified worksheets instead in one worksheet? The firm I work at has 4 different offices, and I want to have a database for each office. I have been trying to write code to recognize a new entry field for office in the “Input” tab, then based on what is entered in that field, the data will be dropped in the specified worksheet or database for that specific office. If you have any advice or tutorials on how to accommodate multiple database worksheets, I would greatly appreciate it.

    Thanks, Mike

  29. aquaholic says:

    when i protect the input sheet, the delete button will delete records & clear all fields BUT the clear button will only clear a single cell. i would like the clear button to clear all cells. – all other works fine.

  30. Ann says:

    Great. Just what I was looking for!

  31. Jose says:

    Thank you so much for this. I’ve taken cleaning up an excel spreedsheet that was in dire condition and this has been a life saver. The one issue is that we need to add more fields (past 60) I tried to look at the instructions to see how to change it so that it references more than 60 fields, but can’t seem to find it. Any help?

    Thanks again for this super useful form.

  32. John Dalton says:

    Hi Debra, I am looking to use you form to populate a cient database, hw do you alter the form of Id in the Order ID field to allow me t use a name instead of a number, also is it ok to send you the sheet and let you have a look at wht I am trying to do to see if it is viable?

    Cheers
    JD

  33. Wee Lip says:

    Hi Debra,

    I’m facing the same issue as the others above using version 3 of the file and I’ve gone through your guide and the code many many times:

    “there are instructions on my website, on this page, in the Update Navigation Code section:
    http://www.contextures.com/exceldataentryupdateform.html#navigation
    You have to change the Navigation code, and the Worksheet change code on the input sheet.”

    Any help would be appreciated – I’ve basically added up to 19 fields altogether but when I try to look back at added records to amend them, only a few fields are populated.. the rest are populated with wrong data from the few fields ..
    The actual records in the partsdata page were populated correctly though.

  34. Michael Scollay says:

    Hi Debra & Others,
    I’ve succeeded in adding fields up to 18 since I haven’t tested any others yet. Make sure that the Input sheet and the Data have identical row headings and column headings respectively. Also, go to the top of modViewData and change the Constant that defines the DE extent to two more than the number of data fields. You also need to go into the name manager to ensure that the array covering the input fields does indeed cover what you’ve added. If you add in the middle, it will.
    If anyone replies, I’ll paste samples of the code next time.

  35. Niquea says:

    Love your work! I would like to only display the records that are visible in the filter on the Input page. Any suggestions welcomed. For some reason all the records keep showing. How do I enforce the filtered data to be copied?

  36. Michael Scollay says:

    Hi Niquea,
    I don’t fully understand your requirement. With the existing spreadsheet, only the current OrderID record under “Select Order ID” is displayed either by changing its value to one that is valid or clicking on the navigation icons under “View Records”. Make sure that you enable macros otherwise, these will not update and old data will be displayed.

  37. NIquea says:

    Hi Michael

    Thanks for your reply. I have modified the spreadsheet so that the ‘database’ is in rows across capturing participants details.

    The Input form displays the information from the ‘database’ however I would like the ‘database’ to filter. Only the filter information from the database must be displayed in the ‘Input Form’

    Currently all the records are displayed

  38. Neha says:

    This is a great tool and very handy. Thank you so much!

    I have a challenge, when user fills the below info, I want it to be stacked in the Partsdata table and also in individual different worksheets.

    For Example, If user selects, 10 door orders and 15 Blank Cap orders it should be stored in PartsData sheet and also in seperate worksheet for Doors and Blank data. So that I can keep a track of ALL the orders and individual parts order as well. And all of these sheets should be updated on its own as soon as user fills the form and hits add.

    I’m aware of dynamic table, but were not able to use it! Please HELP!

  39. Chris says:

    Hi,

    Really like this tool. Is there any way of enabling duplicate IDs? I’m working on a project where I am using the DuplicateID as a primary record, but splitting it with my own secondary record.\\

    Many thanks

    Chris

  40. Robert says:

    This has saved me tons of time. I’m using it for a fairly long data entry process with lots of fields. Because of the length, I’m afraid my users will not click the update button or do something else where the data they’ve entered on the form would be lost. Is there a way or an Excel method to force the form data to update the spreadsheet whenever they move from one field to another?

  41. Denice Juma says:

    Great template,

    It has helped me reduce hours of design and coding. I like the way it is easily extendable to accommodate more data and the copy, transpose and paste feature, is wow

    Thank you

  42. Theresia says:

    Dear Debra,

    Thanks for sharing, I’m glad to find this article. My problem is, I need 1 form, but the output will be in several sheet.
    So I have Supplier Data Form, in this form, I need additional command button to which sheet this data will fill in. Let say I have 5 department named A, B, C, D, E but this supplier could only be at dept A, C and E. I have found how to create one form to multiple sheet but not to optional sheet. Many thanks before :)

  43. sherry says:

    Hai Debra,

    When I add new data to partsdata sheet the new data had overwrite the existing data. The problem only occur when Filter is active in Partspart sheet but if I off the filter the new data will add to last row .Could you help me ?. I always use Filter on the sheet.

    • @sherry, in the code, you could change any lines where the nextRow variable is calculated.
      For example, instead of the current line:

        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

      Use this line:

        nextRow = .AutoFilter.Range.Rows _
                  (.AutoFilter.Range.Rows.Count).Row + 1
  44. David says:

    Hello Debra,

    A truly fantastic resource you have posted.
    I have modified this to use an equipment database for my Scout group.

    I have added another column to the right of the ‘Total’ column on the data worksheet for a hyperlink called “Link” that will go to a website that has an example of the item listed for that row.
    Along with returning the row values to the ‘Input’ worksheet I would like to be able to copy the hyperlink from the data worksheet and paste this still as a hyperlink below the ‘Total’ cell.
    That way if the item price is unknown I can click on a link from the ‘Input’ worksheet to a web page that has an example of the item for that row.

  45. David says:

    Hello All,

    Are there any readers of this page that can help me out with copying a hyperlink from the EquipData worksheet to the Input worksheet.
    I have successfully added extra rows to the Input worksheet and have made the changes to add this data to the EquipData worksheet.
    I have added hyperlinks to the EquipData worksheet in Column U, which I use with hyperlinks to a products webpage my Scout group can use for replacement costs and suppliers.
    I would like to have this hyperlink copied from column U of the active row on the EquipData worksheet and pasted into cell D23 on the Input worksheet.

    Is there anyone who can help me with this ???

    Regards,
    David

  46. David says:

    Hello Debra,

    I have worked out how to copy a hyperlink value from Column U (to the right of your ‘Total’ column) in the PartsData worksheet to below the ‘Total’ cell in the ‘Input’ sheet.
    What I would like to be able to do now is add a row above the heading of the PartsData worksheet. I like to use this row for SUBTOTAL formulas when filtering the data (I find it easier to apply the filters and see the results above the filters rather than having to scroll to the bottom row).
    When a add a new row above the headings of the PartsData worksheet the ‘View Record’ button no longer show the last record.
    What parts do I need to edit to enable the insertion of a row above the headings?

    Any help appreciated.

    Regards,
    David

  47. David says:

    Hello Debra,

    Thank you very much for posting a new version.
    I have been looking at all of the things you have done through each of the versions. Great learning material.
    As I am the Quartermaster of my local Scout Group, I have been using your work quite extensively and along with the latest version you have created in response to my questions I feel like I should offer you some compensation for your time.
    Do you have a link on your website where people who have used your work can provide you a donation as a means of thanking you for all your great work?

    Regards,
    David

    • Hi David, thanks, and I’m glad that you’re finding the sample files to be helpful.
      There isn’t a donation button on my site, but knowing that you’re using my information to help your group is thanks enough!
      Debra

  1. January 1, 2013

    […] You could pay the small fee for the source code password and save the add-in as an .xls file then modify the code to suit. But it's your call. Just offering one possibility. Here's another source for applicable example code New Improved Excel Data Entry Form | Contextures Blog […]

Leave a Reply to Gary Cancel reply

Your email address will not be published. Required fields are marked *