Simulating an 'OnCellFormat' event !

[Jaafar Tribak]

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.

 

[Norie]

Jaafar

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

 

[Joe Was]

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

 

[Legacy 98055]

Example I posted some time ago...

Looking for code to detect if a cell's format has changed

 

[Jaafar Tribak]

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.

 

[Legacy 98055]

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?