Simulating an 'OnCellFormat' event !

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
Can anyone think of way to simulate a 'OnCellFormat' event ... ie: An event that would fire when the cell formatting of a Cell changes .

I am trying to find something but maybe someone knows if this has been done before or if there is an easier alternative.

Regards.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
Jaafar

I can't think of any event that fires when a cell format is changed.:)
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
You can detect Conditional Formatting Changes, Cell color, Text Color, Number Format, Font and others, but not all at once or directly.

You need to Start with the "Selection Event" and build a table of all the Format types you want to test for a change in [if there are VBA ways of changing these]. You Set Public Variables to these and record their values.

Then Call a Sub that tests these values, Like This:


Public myFColor1%, myFColor2%
Public myFCFlag As Boolean
Public myFCAddr$

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Address <> "$B$3" Then Exit Sub

myDetect
End Sub

Sub myDetect()

myFColor2 = Range("B3").Font.ColorIndex

If myFColor2 <> myFColor1 Then
MsgBox "Font Color Has Changed!" & vbLf & vbLf & _
"myFColorFrom=" & myFColor1 & vbLf & _
"myFColorTo=" & myFColor2
End If

myFColor1 = Range("B3").Font.ColorIndex
End Sub
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
Thanks everyone for the feedback.

Tom. Nice work

I already had a similar code that also uses a timer but like with your code, it can't be applied to larger ranges let alone the whole worksheet .For ex, extending the 'Watch Range" to just 50x20 Cells slows my whole machine down so much thus making this not workable.

Also, watching for other Cell Formats can have a similar slowing effect .

I am experimenting with another approach to avoid iterating the cells in the "Watch Range". The idea is to take a snapshot of the UsedRange everytime a cell is selected , store this somewhere and compare it with the next sanpshot. If the snapshots are not identical then raise the event.

So far,although it works, this method is proving too slow as well.

If anything interesting comes up , I'll post it here.

Regards.
 
L

Legacy 98055

Guest
Well... Unless you need to watch formats changed by way of conditional formatting, there is not really a need to watch large ranges. Is there? The user would need to select the range to change the format and this would only lead us to watch the selection.

I am experimenting with another approach to avoid iterating the cells in the "Watch Range". The idea is to take a snapshot of the UsedRange everytime a cell is selected , store this somewhere and compare it with the next sanpshot. If the snapshots are not identical then raise the event.

I cannot conceive of determining the cell that changed without some type of looping mechanism. What other comparison method is available within VBA?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,332
Members
410,603
Latest member
rseckler
Top