[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

a way to trigger event when cell color changes

Posted on 2009-12-20
13
Medium Priority
?
475 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:Or_A
13 Comments
 
LVL 3

Expert Comment

by:amrens
ID: 26091427
Here is the code you are looking for :

customize as per your requirement.

http://www.mvps.org/dmcritchie/excel/event.htm
0
 

Author Comment

by:Or_A
ID: 26091463
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26091466
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Or_A
ID: 26091483
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
 
LVL 23

Assisted Solution

by:ahammar
ahammar earned 2000 total points
ID: 26092183
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
 
LVL 23

Assisted Solution

by:ahammar
ahammar earned 2000 total points
ID: 26092201
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
 
LVL 23

Accepted Solution

by:
ahammar earned 2000 total points
ID: 26092238
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
 

Author Comment

by:Or_A
ID: 26092243
hmm, interesting, i didnt know of this method.
can you write for me the code for "run every second"?
thanks ;)
0
 
LVL 23

Assisted Solution

by:ahammar
ahammar earned 2000 total points
ID: 26092292
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
 
LVL 23

Assisted Solution

by:ahammar
ahammar earned 2000 total points
ID: 26092308
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
 
LVL 23

Assisted Solution

by:ahammar
ahammar earned 2000 total points
ID: 26092312
Correction
Dim dt as Date..

needs to be

Public dt as Date

0
 

Author Closing Comment

by:Or_A
ID: 31668260
thank you so much for all the help!
i will use your code.
cheers
0
 
LVL 23

Expert Comment

by:ahammar
ID: 26092526
Thanks for the points and the grade!  I'm glad it worked for you..

:-)
Albert
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question