Trigger macro when specific cell doesn't change

[Gail Schweitzer]

I have a simple database with macros that filter the data according to criteria selected by end users in another workbook. The matching results are copied from the main database and pasted into the end user "Report" workbook.

The database itself will reside on our network and be protected so only administrators will be allowed access it and make changes.

It all works great...except when there are no results that match the criteria. That is, if the pasted cells are empty. I have tried Worksheet_Change events, formulas in Excel, formulas copied into cells through VBA, macros in regular modules called by Worksheet events, conditional formatting....and everything else I could find &/or think of. I've ended up with either: continuously looping Message Boxes, nothing or Excel hanging up or crashing.

The macro/formula must evaluate two cells in order to trigger the Message Box. While I'm new to VBA, I thought I would be able to find something on some post, somewhere, but have given up after 8 hours of coding failures. Previous attempts with this project in Access were miserable failures due to my inexperience in Access and tight deadline.

Please help...Management is now frustrated with the time it has taken me to develop this and is breathing down my neck (literally!).

Thanks so much in advance.
Gail

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Don Guillett]

Surely this won't do what is desired?

Sub domsgboxtwocells()
If Range("h8") = "de" And Range("i8") = 1 Then MsgBox "hi"
End Sub

 

[Gary''s Student]

Here's an approach:

1. Schedule a periodic examination of an update flag (some cell) using an
OnTIme Event.

If the exam routine discovers the update flag is set, it means that an
update occured since the last exam.

2. the update flag is cleared and the exam routine next looks at the value
in question (another cell)

3. If the v.i.q. is the same as it was at the last update, the cell has not
changed and the routine takes the appropriate action.