a way to trigger event when cell color changes

Hi,

I'm using Excel 2007,
I terribly need a way to trigger a vba routine anytime a user simply changes a cell color.
neither the Workbook_SheetChange OR the Worksheet_Change events triggers when a color changes.
Is there any other working way? i don't care if the trigger is something much more general, like "each time the left mouse button is pressed", as long as it will recognize pressing the color button, etc..

thank you very very much
Or_AAsked:
Who is Participating?
 
ahammarConnect With a Mentor Commented:
Here is an example of what I mean.  Download this workbook.  Just open it and the code will start running.  It just counts down column A every second.  That's where your code would run instead.

There is a macro in it to stop the counter also...
If you use this method, be sure to keep the code in the workbook close event..it is very important even though it may not seem so.  If you take that out, the workbook will open back up even after you close it, just to run the code again...

:-)
Albert
OnTimeExample.xls
0
 
amrensCommented:
Here is the code you are looking for :

customize as per your requirement.

http://www.mvps.org/dmcritchie/excel/event.htm
0
 
Or_AAuthor Commented:
i'm sorry but i cant find in that article a way to trigger event when a cells color is changed. or maybe i missed something?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
There is no built-in event that will do what you want. The only possibilities I can think of would be either to intercept every possible method of colouring a cell - which would be extremely difficult, not least because in 2007 you would need to create your own customised ribbon I think to intercept the built-in commands - or to protect the sheet and only allow formatting to be applied via your own buttons, either toolbar or on-sheet.
However, I would strongly suggest you rethink whatever methodology causes you to need this.
Regards,
Rory
0
 
Or_AAuthor Commented:
hi rory,
thanks for the comment.
my code allows the user to sum data according to cells color. currently, to refresh the result, the user has to hit a button ("manual refresh"). i wish that every time he changes a cell color, the refreshing routine would start ("automatic refresh").
so i guess there's no way to do that.. :(
0
 
ahammarConnect With a Mentor Commented:
I thin k the only way you would be able to do something like this (which wouldn't be very effecient especially if there are a lot of cells to check) would be to capture the color of every cell within the range you need to check when the workbook opened into a collection or an array.  Then using the OnTime command recheck the cells every second.  If they didn't match what was in the collection (which means at least one changed), run your code, and refill the collection with the new colors, so it would be ready for the next check...
Unless you have a lightning fast computer, you will probably be able to see your Excel screen very slightly flash at every check though which could be annoying.  You may see this even if you have a lightning fast computer...I don't know.
If you want to pursue this method further, and need help with the code, let me know...I won't be able to work on it until later tonight though...

:-)
Albert
0
 
ahammarConnect With a Mentor Commented:
Actually now that I think about it, if your code sums data according to color, you could just use the OnTime command to just run your code every second.  You wouldn't need a collection of anything...The screen flashing may still be an issue though...

:-)
Albert
0
 
Or_AAuthor Commented:
hmm, interesting, i didnt know of this method.
can you write for me the code for "run every second"?
thanks ;)
0
 
ahammarConnect With a Mentor Commented:
The workbook I just uploaded is as close as I can get without having your code.  Did you look at it?  You just need to change the code in the "CodeToRun" macro to your code, and change "CodeToRun" to the name of your macro, and get rid of:

     Range("A1").Select

in the open event.  It's hard to explain quickly, (which is what I have to do right now).  Look at the comments in the code.
Can you upload your workbook and tell me which macro you want to run every second?  I will put the code in it.
0
 
ahammarConnect With a Mentor Commented:
I will try...

Put this in the workbooks open event:
Call CodeToRun

and change "CodeToRun" to the name of your macro

At the beginning of your macro put this:

     dt = Now() + TimeValue("00:00:01")
     Application.OnTime dt, "CodeToRun", , True

 Again, replace "CodeToRun" with the name of your macro (which will be the name of the procedure this code is in)

On top of the module with your code, put this:

  Dim dt as Date

In the workbook close event put this:

     On error resume next
     Application.OnTime dt, "CodeToRun", , False

Again, replace "CodeToRun" with the name of your macro

I think that covers it
0
 
ahammarConnect With a Mentor Commented:
Correction
Dim dt as Date..

needs to be

Public dt as Date

0
 
Or_AAuthor Commented:
thank you so much for all the help!
i will use your code.
cheers
0
 
ahammarCommented:
Thanks for the points and the grade!  I'm glad it worked for you..

:-)
Albert
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.