One of my Excel sample files has a data entry sheet, with buttons to add/update database records that are stored on a different sheet. I’ve just added a new version of the file, that helps you remember to save your changes.
How Would You Do This?
Someone uses my data entry form in their workplace, and asked how to remind people to save their changes.
Apparently, people enter new records, or change existing records, and then wander off (to a different sheet or record). They don’t remember to click the Update or Add buttons!
That’s an interesting problem. How would you get people to save their changes in this data entry form?
Use a Macro?
My first thought was to use a macro. Excel can run code when you do specific things (events), such as moving to a different sheet, or closing a file.
Unfortunately, people are unpredictable, and we’d have to set up lots of event code, to catch all the ways they might wander away.
All that code could slow down the workbook, and that would annoy everyone who uses the data entry form.
Alerts Without Macros
Instead of using macros, I came up with a solution that uses a few formulas and a bit of conditional formatting. It won’t have much impact on the workbook performance, and will help the people who need reminders.
For example, if someone makes changes to an existing record, the label turns orange, for each updated field. That’s done with conditional formatting.
Also, below the form’s buttons, a formula shows a reminder message, with a bright yellow background – “Click Update, to save your changes!”
How It Works
First, at the top of the Input sheet, 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 that cell is named CheckID – we’ll refer to it in other formulas.
Get the Existing Data
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)))
The INDEX/MATCH Formula
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”.
=IF(CheckID=FALSE,”NEW”,
Otherwise, the INDEX function returns a value from Table1, which stores the records on the PartsData sheet
INDEX(Table1,
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.
MATCH(Input!P6, Table1[#Headers],0)))
So, for Order ID 10102, the Part is “Spring”
Check for Differences
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)
=IF(CheckID=FALSE,0,–(Q5<>D5))
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.
=IF(CheckID=FALSE,0,
Otherwise, it compares the values in columns D and Q, to see if they are NOT equal “<>”.
–(Q5<>D5)
The result is TRUE or FALSE, and the two minus signs (double unary) change that result to a number (1 or 0):
- TRUE = 1
- FALSE = 0
Colour the Labels
The labels have conditional formatting to change the fill colour:
- Green for new records
- Orange if the value in column D is different from the stored value
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 Zero:
=R5<>0
Reminder Message
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)))
- No Order ID – empty string
- Not in database – MsgAdd
- In database, no differences – empty string
- Anything else – MsgUp
The MsgAdd and MsgUp named ranges contain the messages, and you can change those, to whatever text you’d like. Maybe your co-workers need a little more encouragement to save or update!
Get the Data Entry Form Alerts File
To get a copy of the workbook, go to the Data Entry and Update Form page on my Contextures site, and click on Download #6 (With Alerts). There are notes in the file, to explain how the alerts work.
_______________________
Worksheet Data Entry Form Alerts
_______________________
Alert users to duplicates for certain ranges. I have 3 rotas in one worksheet. I have multiple data validation from different named ranges. For each selection, I want the vba to search three different cell ranges for a match to that name. If there is a match, a msg box appears saying this person has already been assigned for…(and list the job that appears in the cell to the left of the matched name) I don’t want to delete the newly selected person or the match as doubling up sometimes is okay, I just want to alert the user that there is a double up.