By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

On Format Event in Excel?

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 13 '07 #1
Share this Question
Share on Google+
23 Replies


ADezii
Expert 5K+
P: 8,619
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.
Mar 13 '07 #2

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 13 '07 #3

NeoPa
Expert Mod 15k+
P: 31,347
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...
Mar 14 '07 #4

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 14 '07 #5

ADezii
Expert 5K+
P: 8,619
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.
Expand|Select|Wrap|Line Numbers
  1. The following code will copy all Data and Formatting for the Cells 
  2. contained within the Range("A1:D4") to the Range("A10:D13") on 
  3. Sheet2. The only catch is that any data contained within the 
  4. specified Range on Sheet2 will be overwritten, but it is one step 
  5. closer to a 'Solution'.
  6. Worksheets("Sheet1").Range("A1:D4").Copy destination:=Worksheets("Sheet2").Range("A10:D13")
Mar 14 '07 #6

NeoPa
Expert Mod 15k+
P: 31,347
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. Application.CutCopyMode = False
to lose the Copy selection highlighter for the copied range.
Mar 14 '07 #7

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 14 '07 #8

NeoPa
Expert Mod 15k+
P: 31,347
Last idea : Would the OnClose or OnSave events help at all?
Mar 14 '07 #9

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 14 '07 #10

Expert 5K+
P: 8,434
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?
Mar 14 '07 #11

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 14 '07 #12

NeoPa
Expert Mod 15k+
P: 31,347
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 :(
Mar 15 '07 #13

Expert 5K+
P: 8,434
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?
Mar 15 '07 #14

Expert 5K+
P: 8,434
...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?
Mar 15 '07 #15

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 15 '07 #16

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 15 '07 #17

Expert 5K+
P: 8,434
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.
Mar 15 '07 #18

Expert 5K+
P: 8,434

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 15 '07 #20

NeoPa
Expert Mod 15k+
P: 31,347
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.
Mar 15 '07 #21

Expert 5K+
P: 8,434
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?
Mar 15 '07 #22

Expert 5K+
P: 8,434
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.
Mar 15 '07 #23

Rabbit
Expert Mod 10K+
P: 12,347
Damn! Have you looked into alternatives? These might be worth a glance...
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.
Mar 15 '07 #24

Post your reply

Sign in to post your reply or Sign up for a free account.