Delete Orders with Excel Data Entry Form

Long ago, Dave Peterson created an Excel worksheet data entry form, so you could enter records on one sheet, and store the data on another sheet. Then, you can hide the data entry sheet, so users don’t accidentally change any of the old records.

Form02

There have been a few version of the data entry form file, including the the previous version, in which you could also update the selected record. This way, the data sheet can still be hidden, but users can make changes to the existing records.

dataentry04

Delete the Current Record

In a comment, Bryan asked for a Delete button too. In this new version, that feature is added. (Thanks, Bryan, for the suggestion!) Use this version if you really trust your workbook users – and keep good backup files!

dataentry12

When you click the Delete button, a message appears, asking you to confirm that you want to delete the record.

dataentry13

If you click No, the deletion is cancelled.

dataentry14

If you click Yes, the record is deleted from the database worksheet, and the data entry cells are cleared.

dataentry15

Download the Worksheet Data Entry Form

To see how the data entry form works, you can go to my Cotextures website, and download the Worksheet Data Entry Form sample file.

The file is in Excel 2003 format, and is zipped. After you unzip the file and open it, enable macros, so you can use the worksheet buttons.
_____________

10 thoughts on “Delete Orders with Excel Data Entry Form”

  1. I used to maintain something similar a few years ago.
    And had also added a DELETE record button.
    But knowing my users, I took the extra (hidden) step in my code, of moving the deleted record to a hidden workbook. Run-time code would take care of opening the workbook, un-hiding it, moving the deleted record there, hiding it again, saving and closing it.
    And with Application.Screenupdating = False, with a status bar message being displayed, the users never knew that the record was not actually deleted, and still retrievable from somewhere !

    Of course, there were times when a user would come back with an “un-delete” request, which was miraculously managed through an “old, backup version” of the file, with a ‘great deal of effort’ on my part ;-p

  2. another technique i have employed in the past is to have have a status column, and when a user ‘deletes’ a record, just set the status column to DELETED for that record. I usually add a username & data stamp for audit purposes in 2 additional columns.

    you then need to filter the records so you only show the user the records that are not labelled as DELETED.

    I find it quicker to code than using an extra hidden sheet or workbook.

  3. That’s also a good idea, Mark.
    Only, I was wondering about the processing overhead due to filtering the data every time it needs to be displayed.
    Or, would it still be worth the extra coding effort to get the DELETED records off the main database, to speed things up ?

  4. sir when i used this all above instruction
    but when i protect my sheet it no working
    i mean this macros not workin
    please guide me for that

  5. Hello,
    I am extremely saddened by all of these ‘Excel Data Entry worksheets’ on all the contexture.
    I am tearing my hair out a little as none of them actually add the new data to the next blank row in order. they seem to add the new data anywhere.
    for example if enter in the data entry worksheet 1, then save
    then I enter 2, then save
    then I enter 3, then save
    It seems to added them to PartsData sheet in any order.
    Not one after another. Can anyone please help

  6. Question about the Navigate Through Stored Records and Select a Specific Record Features.
    Not sure if this sounds right or not but
    When you use the Navigate Throught Stored Records shouldn’t it also change the Specific Record?
    Right now if you go to recond 1 and the Specific Record is on Rec 2, Rec 1 will be deleted but it will say when you delete Delete Rec 2? for the message box.
    Is there a way to have to so if you change either one they both update to the correct record and displays the Order ID?

  7. Hi Debra,
    This has helped me so much! It works perfectly!
    Now, what I am trying to do is write up a code that takes a screenshot of the OrderEntry range, paste it on as a new email and send. Currently, I have the code to copy, paste and send email and yours but i’m having difficulty combining the two codes.
    Is it possible to do it?

Leave a Reply to Khushnood Viccaji Cancel reply

Your email address will not be published.

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