On Format Event in Excel?

[Rabbit]

Is there an event that triggers when formatting on a cell changes?

What I need to do is copy the formatting changes in one worksheet to another worksheet whenever the user changes the formatting. I looked but couldn't find any such event. The Change event doesn't trigger if you just change the formatting.

[ADezii]

Is there an event that triggers when formatting on a cell changes?

What I need to do is copy the formatting changes in one worksheet to another worksheet whenever the user changes the formatting. I looked but couldn't find any such event. The Change event doesn't trigger if you just change the formatting.

To be honest with you, Rabbit, I don't think that such an Event exists that will dynamically react to formatting changes in individual Cells. I don't know the extent of your Worksheet but how about using the Workbook BeforeClose() Event. You can loop through Cells in a specified Range, check for specific Formatting patterns (Bold, Font, Font Size, Alignment, etc.) then transfer these to a specific Range within the 2nd Worksheet. This would, of course, still be a hugh undertaking.

[Rabbit]

I figured I would have to do something like this. I was just hoping for a more efficient solution as the spreadsheet I'm working on is a favor for someone from a different unit and it's getting pretty unwieldy.

[NeoPa]

I've checked through the events for both the WorkBook & the WorkSheet and there is nothing to match what you need I'm afraid.
You can stop the operator from changing the format by locking the WorkSheet, but otherwise...

[Rabbit]

Yes, I looked through the events as well. I was hoping the On Selection Change event had a .PreviousSelection or something but I couldn't find anything to that that extent.

[ADezii]

Is there an event that triggers when formatting on a cell changes?

What I need to do is copy the formatting changes in one worksheet to another worksheet whenever the user changes the formatting. I looked but couldn't find any such event. The Change event doesn't trigger if you just change the formatting.

Rabbit:
Just thought that you may be interested.

The following code will copy all Data and Formatting for the Cells 
contained within the Range("A1:D4") to the Range("A10:D13") on 
Sheet2. The only catch is that any data contained within the 
specified Range on Sheet2 will be overwritten, but it is one step 
closer to a 'Solution'.
Worksheets("Sheet1").Range("A1:D4").Copy destination:=Worksheets("Sheet2").Range("A10:D13")

[NeoPa]

Call YourRange.PasteSpecial(Paste:=xlFormats)

will paste in just the formats if you need to know about that. I'm pretty sure there's nothing to trigger when the format changes though. Sorry.

BTW. Don't forget to Copy the other Range first and afterwards, use the :

Application.CutCopyMode = False

to lose the Copy selection highlighter for the copied range.

[Rabbit]

Thanks for that but the problem was never being able to copy the formats but that there was nothing to trigger a format change event. I ended up just doing it through a macro.

[NeoPa]

Last idea : Would the OnClose or OnSave events help at all?

[Rabbit]

That's what I ended up doing, using a macro for when they want to see the changes right away and also in the On Close event in case they end up not using the macro.

[Killer42]

That's what I ended up doing, using a macro for when they want to see the changes right away and also in the On Close event in case they end up not using the macro.

You mean you triggered a macro from SelectionChange, or what?

[Rabbit]

You mean you triggered a macro from SelectionChange, or what?

No, a selection change would be too computationally intensive. I just used a keyboard shortcut macro and called it from the On Close as well.

[NeoPa]

You mean you triggered a macro from SelectionChange, or what?

No, a selection change would be too computationally intensive. I just used a keyboard shortcut macro and called it from the On Close as well.

...And it wouldn't work :(

[Killer42]

No, a selection change would be too computationally intensive. I just used a keyboard shortcut macro and called it from the On Close as well.

Oh, so you mean they have to manually trigger the macro if they want an immediate update? Yuck!

(Note, "yuck" is just about the situation in general - not directed at you. I know you had to work with what was available.)

Does VBA in Excel have a timer control available? Perhaps you could do the scan and copy of formats on a regular basis?

[Killer42]

...And it wouldn't work :(

I can see that it would be less reliable than the hypothetical FormatChange event, but are you saying it wouldn't work at all?

[Rabbit]

Oh, so you mean they have to manually trigger the macro if they want an immediate update? Yuck!

(Note, "yuck" is just about the situation in general - not directed at you. I know you had to work with what was available.)

Does VBA in Excel have a timer control available? Perhaps you could do the scan and copy of formats on a regular basis?

No, there's no timer control. I looked for that too.

[Rabbit]

I can see that it would be less reliable than the hypothetical FormatChange event, but are you saying it wouldn't work at all?

It would work, but then you'd be checking every cell everytime they made a selection change.

[Killer42]

It would work, but then you'd be checking every cell everytime they made a selection change.

Um... seems to me, you'd only be checking the prior range, not every cell. Probably still not a great idea, though.

[Killer42]

No, there's no timer control. I looked for that too.

Damn! Have you looked into alternatives? These might be worth a glance...

• The OnTime method
• ucTimer - Self-subclassed API timer control.
• A timer control for VBScript, I think.
• Scheduling Procedures With OnTime
• Excel Timer - let Excel perform periodical tasks!
• VBA Timer object for developers (beta version)

[Rabbit]

Um... seems to me, you'd only be checking the prior range, not every cell. Probably still not a great idea, though.

Is there a way to call up the prior range? I was looking through the pop-up list for something that looked like that but I didn't see one.

I'll take a look at those links.

[NeoPa]

I can see that it would be less reliable than the hypothetical FormatChange event, but are you saying it wouldn't work at all?

I can't see how it would work at all.
The formatting of a cell or range would not require a change of selection - hence, you could have a change of selection without a format change AND a format change without a change of selection. There may well be some overlap, but I would be surprised if that's what you had in mind Killer.

[Killer42]

I can't see how it would work at all.
The formatting of a cell or range would not require a change of selection - hence, you could have a change of selection without a format change AND a format change without a change of selection. There may well be some overlap, but I would be surprised if that's what you had in mind Killer.

Actually, that's exactly what I had in mind.

That's why I said it would be less reliable. How well it worked would, of course, depend on the typical usage of the spreadsheet in question. If the user moves around a lot, then this technique would at least be better than nothing. It certainly wouldn't "cover all bases" though.

A timer control (equivalent) would be much better. Thanks again, M$! Yet another piece of functionality removed. :( Why is it always the most useful pieces, and the hardest ones to work do without, that they remove?

[Killer42]

Is there a way to call up the prior range? I was looking through the pop-up list for something that looked like that but I didn't see one.

Not that I know of. But what I had in mind was recording the current one, then next time you move that becomes the prior one. And so on.

Ok, so it's ugly - I was just trying to come up with something that might actually work.

I'll take a look at those links.

Good luck. Let us know how it goes. Some of them sounded pretty good, but I don't have the time (:D) to check them out.

[Rabbit]

Damn! Have you looked into alternatives? These might be worth a glance...

• The OnTime method
• ucTimer - Self-subclassed API timer control.
• A timer control for VBScript, I think.
• Scheduling Procedures With OnTime
• Excel Timer - let Excel perform periodical tasks!
• VBA Timer object for developers (beta version)

One doesn't even tell you how to implement it. 2-3 of them talk about using something outside of Access which is no good because I won't be the one using the spreadsheet. The rest talk about using Application.OnTime() but warns that it is not the best solution.

I think for my purposes I may just go with Application.OnTime()
Or I may go with keeping track of the previous selection and using On Selection Change.
Or I may just leave it as it is and tell them they're SOL, use the keyboard shortcut seeing as how I'm only doing this as a favor for someone in a different unit.