Worksheet Data Entry Form Alerts

Worksheet Data Entry Form Alerts

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?

Data Entry Worksheet 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!"

Data Entry Alert

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.

dataentryupdatealert03

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

dataentryupdatealert04

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

dataentryupdatealert05

So, for Order ID 10102, the Part is "Spring"

dataentryupdatealert06

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

dataentryupdatealert07

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)

dataentryupdatealert08

And there's a simple rule for Orange (updates) – the Difference cell for that row is not Zero:

=R5<>0

dataentryupdatealert09

Reminder Message

Finally, a message appears below the buttons, with a bright yellow background, if there's a new or changed record.

dataentryupdatealert10

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!

dataentryupdatealert11

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

Worksheet Data Entry Form Alerts

_______________________

Leave a Reply

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

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