Edit Records in Excel Worksheet Data Entry Form

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.

original Excel data entry form
original Excel data entry form

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.

Excel data entry form scroll
Excel data entry form scroll

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.

Excel data entry form Update Existing Records
Excel data entry form Update Existing Records

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.

Excel data entry message

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
___________

31 thoughts on “Edit Records in Excel Worksheet Data Entry Form”

  1. Chris, in the UpdateLogWorksheet macro, in the second ‘With historyWks’ section, delete the first 5 rows:
    With .Cells(nextRow, “A”)
    .Value = Now
    .NumberFormat = “mm/dd/yyyy hh:mm:ss”
    End With
    .Cells(nextRow, “B”).Value = Application.UserName

    and change the next line to:
    oCol = 1

  2. O.K. I have another question.
    How would I save the information to another workbook on a network rather then the inputdata sheet?
    Thanks!
    Chris

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

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

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

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

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

  8. In the code, you define a variable named myCopy, with all the data entry cells.
    When you update the log, all the cells in that myCopy range are cleared, if they contain constants. Formulas should be left as is.

  9. Debra,

    That worked, many thanx as this is exactly what I have been looking for ! Works brilliant for my particular application.

    BR

    Gary

  10. Debra,
    Sorry for bothering but i have a problem i can’t solve. I have a sheet with toys i lend to members of our club. All toys have a partnumber and a description. What i would like to do is everytime i lend toys to the members to change there name, adress and phonenumber in the related cells. So i want to use the partnumber of the toy (column A) as a lookup and change the name, adress and phonenumber that are in column F, G and H. I hope you can get me on the way.

    Regards Stargazer

  11. I agree with the above comment. I too would like to specify which cells have required data and which cells could have optional data. Also, I have modified the worksheet form to have a little over 70 fields. Is there any way to speed up the save and edit coding to make it work faster?

    Thanks

  12. Hi,
    I was wondering if Update Existing Records is possible to search via Name/Partname and not just via Log Number?

    thanks

    PS: really best data entry form on the web!

  13. Stargazer, RMoreno, MRNO and Cumar, thanks for your suggestions for enhancements to the data entry form.
    I’m working on the changes, and will post on the blog when the new version is ready.

  14. Hi,

    I have created the excel form with the code you have provided.
    If updating existing record is possible to search via product name .please let me know?

  15. its very nice thing – excel data entry form

    but how can we add a new coloumn means a new head for example i want to add REMARK below TOTAL as a extra record in a new cell then how the macro will work in this cas

  16. HI,

    Love this form and database. I have changed it around to use as a jobcard for work carried out, Instead of Qty i’ve used this box as Hours. However when I enter 3.5 it says I can’t use fractions ‘The value you have entered is not valid’. How can I change this in the code please. as we work on a 7 hour day so if some one only works 3.5 hours I can’t enter it at the moment.

    Keep up the Great work 😉

  17. @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/

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

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

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

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

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

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

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.