Idea: Conditional Formating activating alert box.

[Nacky]

Hi everyone,


So I had an idea, and for argument's sake I'll use product pricing to elaborate on it.


-Say you got 6 Columns (A -> Product Name, B-> Date/Time, C-> Price, D-> 1st Formula, E-> 2nd Formula, F -> Check Formula)

- Columns A,B,C are just standard data, no duplicates (Sorted by Product and Date/Time, Ascending)

- Column D contains a formula to check the price of a product and its date/time to make sure its the first price received for that given date.

-- INDEX($A$2:$C$100,MATCH($A2,$A$2:$A$100,0),3)


-Column E does the opposite of Colunmn D, and checks for the last price received for a product on a given date/time


-- LOOKUP(2,1/($A$2:$A$100=$A2),$C$2:$C$100)


- Column F : A simple check to pin point price increases for a product:

-- IF($E2 > $D2,1,0)


The Dilemna:


Anyone got any ideas how to make an alert box come up to notify the user that a price increase has been detected by checking for "1"s in column F.

--- OR ---

Mayhaps pop up a form with a Listview displaying all detected increases with their Row information (Name,Date,Price,etc)...


I haven't worked on it yet, just wanted to put it out there if anyone would like to give it a try with me.


For arguments sake, say the database linking the data is on a 30min refresh timer (to help activate the VBA code checks to pop up alerts).


Should be fun, I'm gona try it tomorrow.


Regards,

Nacky

 

[Hui]

Why not just use Conditional Formatting and highlight the cell Red?

 

[SirJB7]

Hi, Nacky!


Additionally to Hui's simple and effective suggestion, CF lets you see at a glance every alert case. With a message box you will get a list of alerts, you'll need to write them down if many, and then close the box and filter column F or go to to each row. CF looks to have more advantages than dis.


Regards!

 

[Nacky]

Using conditional formating to highlight a cell is what I currently use. I wanted a way for a user to work in a spreadsheet while the Data source Sheet is quietly updating in the background and informing the user if a trigger goes off.


The dilemna of having multiple alerts spam the user can be remedied by using a pop-up form that gets called when a condition is "true" and shows all triggers in a listview.


Conditional formatting requires the user to be actively looking at the sheet, scrolling or filtering through data and looking for/at the formated cells.


In a multifunctional excel tool that performs different operations related to a certain task, it would not be time efficient to just use conditional formating to color a cell.


Regards,

Nacky

 

[SirJB7]

Hi, Nacky!


Nice exercise building a timer in VBA, so... as the wheel was yet invented, I found this:

http://www.forosdelweb.com/f90/timer-excel-502194/

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=34409&lngWId=1


With that timer implementation (without third parts dll's nor add-ins, just -and little!- VBA code), I wrote this:

http://dl.dropbox.com/u/60558749/Idea_%20Conditional%20Formating%20activating%20alert%20box%20%28for%20Nacky%20at%20chandoo.org%29.xlsm


Check if it works for you. I still don't like the pop-up user form method.


Regards!

 

[Nacky]

Hiya SirJB7,


So, I made my own as well, and like you, I found the pop-up rather annoying!!! Can't download or upload to storage websites unfortunately from work, but what I did was;


Wrote a Worksheet_Calculate event subroutine. So after the DB is refreshed and it calculates the subroutine runs a loop to check for "1"s. As soon as it finds a "1" , it just exits the subroutine (one match is all I needed to trigger the pop-up event) and calls the form.


On the Form, I wrote a subroutine to loop through the data and .additems as it went along into a Listview and showed a report of all the "1"s that matched my criteria.


Worked great too because when I was using another program (outlook for example), my excel in the taskbar would be flashing yellow to let me know that Excel requires user interaction (i.e. the pop-up form got triggered).


Was far simpler than I had imagined. Also tried it with an worksheet_Change event and that works fine too.


Looking forward to seeing your take on it when I get home.


Regards,

Nacky

 

[SirJB7]

Hi, Nacky!


Glad you could solve it. If you can't download it just advise and I'll email it to you.

About the pop-up form... well, from within Excel you can't do a lot. The last thing I modified was avoiding that users close the user form, with QueryClose event. Just give a look, maybe you can improve yours, or maybe you want to share yours to let me improve mine.

The best of all was getting a clean and quick implementation of a timer, at least for me.


Regards!

 

[Nacky]

Sure you can email me at:


Nackyk_@_live.ca


Remove the underscores, just there so bots don't spam me.

 

[SirJB7]

Hi, Nacky!

Got it. Sent from siriusblackberry_@_yahoo.com.ar

Regards!