469,312 Members | 2,493 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,312 developers. It's quick & easy.

On Format Event in Excel?

Rabbit
12,516 Expert Mod 8TB
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
23 4208
ADezii
8,800 Expert 8TB
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
12,516 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
12,516 Expert Mod 8TB
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
8,800 Expert 8TB
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
32,173 Expert Mod 16PB
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
12,516 Expert Mod 8TB
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
32,173 Expert Mod 16PB
Last idea : Would the OnClose or OnSave events help at all?
Mar 14 '07 #9
Rabbit
12,516 Expert Mod 8TB
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
Killer42
8,435 Expert 8TB
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
12,516 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
...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
12,516 Expert Mod 8TB
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
12,516 Expert Mod 8TB
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
No, there's no timer control. I looked for that too.
Damn! Have you looked into alternatives? These might be worth a glance...
Mar 15 '07 #19
Rabbit
12,516 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
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
12,516 Expert Mod 8TB
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.

Similar topics

6 posts views Thread by Sam Johnson | last post: by
3 posts views Thread by ggupta78 | last post: by
4 posts views Thread by toffee | last post: by
18 posts views Thread by Dirk Hagemann | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.