• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: Watching a cell for format changes

dan_l

dan_l

Active Member
There's a totally practical reason for me wanting to do this:


How would I code VBA to watch for cell formatting changes and take action once a specific change has occurred?


I know how I would write a macro to check the formatting of a cell, but I'm trying to get one that's actually looking for the change.
 
You can store the current format somewhere on your sheet. With the Worksheet "on change"-event you can check if the two are still the same.
 
Dan_L


There is no event that occurs when a user changes the format of a cell


What does the user do after changing the cells format?

Do they move to another cell immediately ?


If so you could use a selection change event, but if they don't move cells for a while there would be a delay in the event happening?
 
Hui,


That's a good question. This is actually going to be a rather well scripted companion to a little introduction to excel thing I have to do for some coworkers.


A fun project, to say the least. But you might be right with having them change the selection anyhow.
 
Sorry, didn't know that.


The danger with events like Change and SelectionChange is that they occur very often. I always try to run the check as soon as possible in the event, use a button or set the macro to run every few minutes to keep the workbook running smooth.


I'm not sure the third option is the way to go, but here's the how-to:

http://www.ozgrid.com/Excel/run-macro-on-time.htm
 
Hmmm. Maybe I could have it run the check every 30 seconds when the sheet is active. It's not a pretty solution, but it might achieve my desired result.


Thanks guys.
 
You must log in or register to reply here.
Back
Top