This version of the Excel data entry form reminds you to save, when you add new records or update existing records.
This tutorial shows how the Data Entry form Alert messages and formatting are set up.
For general instructions, go to the Worksheet Data Entry Form page.
Someone asked how they could remind people to click the Update or Add buttons, while use my Worksheet Data Entry Form.
I thought about adding macros, but that could slow down the workbook, which would be an inconvenience to everyone who uses the data entry form.
So, instead of using macros, I build an alert system based on a few formulas and some conditional formatting. This has only a minor impact on the workbook performance, and will help the people who need reminders.
For example, here's the worksheet data entry alert that appears when the quantity has been changed.
Here's how the data engry update alert system works.
First, at the top of the Input sheet, there is a cell named CheckID. In that cell, a formula checks the Order ID, to see if it's already in the database (stored on the PartsData sheet).
=AND(OrderID<>"", COUNTIF(PartsData!C:C, OrderID)>0)
The result will be TRUE or FALSE, and we'll refer to that result in other formulas.
Off to the right on the data entry sheet, there are INDEX/MATCH formulas, to pull the record's current data from the database. Here is the formula in cell G5:
=IF(CheckID=FALSE,"NEW", INDEX(Table1, MATCH(OrderID, Table1[Order ID],0), MATCH(Input!P6, Table1[#Headers],0)))
That formula starts by checking the CheckID cell. If it's FALSE, the record is not in the database, so the result will be "NEW".
Otherwise, the INDEX function returns a value from Table1, which stores the records on the PartsData sheet
To find the correct row in Table1, a MATCH formula looks for the Order ID in the Order ID column.
MATCH(OrderID, Table1[Order ID],0),
To find the correct column, a MATCH formula looks for the label name in the Table1 headers.
So, for Order ID 10102, the Part is "Spring"
In column R on the Input sheet, a formula compares the database value (from column Q), to the value in the Input area (in column D)
That formula also starts by checking the CheckID cell.
If it's FALSE, the record is not in the database, so the result will be zero.
Otherwise, it compares the values in columns D and Q, to see if they are NOT equal "<>".
The result is TRUE or FALSE, and the two minus signs (double unary) change that result to a number (1 or 0):
The conditional formatting rule for Green (new record) looks at the OrderID and the CheckID cells: =AND(OrderID<>"",CheckID=FALSE)
And there's a simple rule for Orange (updates) – the Difference cell for that row is not equal to Zero:
Finally, a message appears below the buttons, with a bright yellow background, if there's a new or changed record.
That's the result of a formula in cell B15:
=IF(OrderID="","", IF(CheckID=FALSE, MsgAdd, IF(AND(CheckID=TRUE, TotalDiff=0),"", MsgUp)))
The MsgAdd and MsgUp named ranges contain the messages, and you can change either message, to whatever text you'd like.
Click here to download the Data Entry Form With Delete and Alert workbook.
The zipped sample file is in xlsm format, and contains macros - the alerts don't use macros, but the worksheet buttons do. After you unzip the file and open it, enable macros, so you can use the worksheet buttons.
More Versions: For more versions of the Worksheet Data Entry form, go to these pages:
Last updated: December 4, 2018 12:47 PM