Simulating an 'OnCellFormat' event !

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,306
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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
8,306
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?
 

Forum statistics

Threads
1,141,011
Messages
5,703,728
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top