Worksheet Data Entry Form in Excel

With a bit of programming, you can make it easy for users to enter data in an Excel workbook, and keep them away from the stored data. Dave Peterson created a sample workbook with a worksheet data entry form.

Form02

There are data validation drop down lists in cells D5 and D7, and quantity is typed in cell D9.

Click the Add to Database button, and the new record is added at the end of the database, which is a list on a different sheet.

View the Stored Records

In Dave’s workbook, you could click the View Database button to go to the database sheet, and review or edit the order records.

FormViewData

In some cases you might prefer to hide the database sheet, to protect the records, but still allow users to view the existing data. I’ve added a few buttons to Dave’s workbook, to allow users to scroll through the existing records.

FormDataScroll

The navigation buttons take you to the first, previous, next or last record, or you can type a record number in the yellow cell, to go to a specific record.

The Go To Database button is still on the worksheet, but you could remove that, and hide the PartsData sheet, so users are less likely to change the data.

Download the Sample File

The zipped sample workbook can be downloaded from the Data Entry Worksheet Form page on my website.

In the Download section, look for Version 2 – Navigation Arrows.

More Articles on the Worksheet Data Entry Forms

New Improved Excel Data Entry Form

How to Customize the Excel Data Entry Form

Delete Orders with Excel Data Entry Form

Check Excel Database Before Adding New Item

___________________________

You may also like...

16 Responses

  1. Tim Mayes says:

    Deb, you and Dave make a great team! While this requires a bit of set up, I would imagine that it is a lot easier for end users than the Data Form dialog box.

  2. Thanks Tim — it does take some work to set this up, and not as many features as the built-in Data Form, but nice and simple for the users.

  3. Bruce Reynolds says:

    I am going to keep this one as a best practice. Very well written integrated Excel VBA.

  4. Shankar Singh says:

    This is a amazing website for people who want to learn in depth excel.

  5. Shumz says:

    Hi Debra ,

    Great Job !

    I need some assistance in the same … I need to maintain small inventory or stationary database .. we have note pads how many we recieve from the warehouse1 or warehouse 2 .. and after recieving from ware house how many we distributed to different locations/stores .. in order to have final updated balance at the end.

  6. shumz says:

    Thanks , its done .

  7. Anju says:

    Hi Debra ,

    Great piece of work… this is simpled our life too much.. Want to ask an small question. In database does the records will be sorted out as we want…??

    Regards,

    Anju

  8. Mal says:

    Gday Your example and solution are great…..I have a question though about amending data once it has been entered without the user going to the database (dangerous).

    thanks

    Mal

  9. Brian says:

    I have a question of how to insert more than one set (order). What do I need to change in the code to loop it through 10-15 rows of data.

  10. dadamexi says:

    sir,

    thanks for the useful code. please mail me code for edit/amend the entered using data entry worksheet form.

    thanks

  11. Contextures Blog » Edit Records in Excel Worksheet Data Entry Form says:

    […] 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 […]

  12. Masz says:

    Hi,

    I am using the above database for my working purpose. I actually added a DELETE button(Worksheet not cmd button)and it seems didn’t work. The purpose of the DELETE is to delete the unwanted data when it is displayed in the entry form and in the database as well. My command only works for the entry form. The unwanted data is still remain in the database. Please help me on this..

    – Is that possible to include the password if the person wants to delete the data? and the delete record will be stored in another worksheet.
    This is to allow the authorized person only. Since it involves costing, that is why it is needed to store the deleted data somewhere.

    Thank U very Much.

    Masz

  13. Paul Allsop says:

    Hello! Love this form it has really helped me out, I thank you for that. One question, is it possible to change the target worksheet beased on the value of one of the cells? For example I would like to be able to hit “add to database” and the record be sent to a worksheet called “week16” if week16 is the value of one of the completed cells on the form, would be fantastic if the record could be sent to the original sheet and also the sheet based on the value so i would have a worksheet of all records and also seperate sheets (week16, week17, week18). Is this possible? Your response would be very much appreciated :-)

  14. Saida says:

    Hi there,

    I’m trying to modofy the code to enter the same information in different spreadsheets but I just can’t find the part that I have to modify..im totally new with macros…

    I’d appreciate your help.

  15. Cecilia Tran says:

    Hi,
    I really like the the option of viewing records and update records. I would like to get some more understanding behind the Input range and the rearrangement of input field which would tied to the database worksheet.

  16. Donnie Turner says:

    Hello,
    I have a question. I am extremely inexperienced in creating databases. I am a children’s pastor and I need to simplify my recording process of my children’s weekly accomplishments in order to reward them quarterly. I currently record their last name, first name, grade level, phone number, Date, Bible, Offering, Lesson Home Wk, Memory verse, Mission $ and Visitors. Each except for name, grade, date and phone is currently recorded with a numerical value of 1 being completed and 0 being not completed and then added to the right in a column.

    I am wanting to create a data entry page that will allow me to enter/search current names and enter their weekly service totals that add it to the spread sheet and keep a running total that I can view to see how each child is progressing.

    If you could email me and point me in the right direction or if you have further questions I would greatly appreciate any help given. Thanks and God Bless,

    Donnie

Leave a Reply to shumz Cancel reply

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