How to detect changes in cell format?

Question

I want to embed a procedure in an Excel worksheet that will determine when the cell format changes, for example. from text to number.

But I can not figure out how to get the cell format type. I tried using the Worksheet_Change event handler to check the data type as follows:

 Private Sub worksheet_change(ByVal Target As Range) If Target.Address = "a1" Then If VarType(Target) <> 5 Then MsgBox "cell format has been changed" End If End If End Sub 

But with this code in place, if I change the data type of cell A1 from Number to Text, Worksheet_Change does not start; an event handler is only called if I modify the contents of the cell.

In addition, this procedure can determine if the content changes from a number to an alphabetical string, for example. from "35.12" to "abcd", but not a number number to a text number; if I set cell B1 to text, then enter “40”, then paste the contents of cell B1 into cell A1, vartype() still returns “5”, so the warning does not fire.

How can I determine if the format has changed, regardless of whether the type of content has changed?

Answer 1

Great question!

If you only want to trigger an event in a change in NumberFormat (you seem to incorrectly call it a data format, NumberFormat is the attribute you want), then this is a good example.

I intercept all selection change events and check if any NumberFormat has changed.

 Option Explicit 'keep track of the previous Public m_numberFormatDictionary As New dictionary Public m_previousRange As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'requires reference to Microsoft Scripting Runtime Dim c As Variant Dim wasChange As Boolean Debug.Print "***********************" 'make sure you had a previous selection and it was initialized If m_numberFormatDictionary.Count > 0 And Not m_previousRange Is Nothing Then 'Iterate through all your previous formattings and see if they are the same For Each c In m_previousRange Debug.Print "Found " & c.NumberFormat & " in " & c.Address Debug.Print "Stored value is " & m_numberFormatDictionary(c.Address) & " in " & c.Address 'print out when they are different If c.NumberFormat <> m_numberFormatDictionary(c.Address) Then Debug.Print "~~~~~~ Different ~~~~~~" wasChange = True End If Next c End If 'clear previous values m_numberFormatDictionary.RemoveAll 'Make sure you don't error out Excel by checking a million things If Target.Cells.Count < 1000 Then 'Add each cell format back into the previous formatting For Each c In Target Debug.Print "Adding " & c.NumberFormat & " to " & c.Address m_numberFormatDictionary.Add c.Address, c.NumberFormat Next c 'reset the range to what you just selected Set m_previousRange = Target End If 'simple prompt now, not sure what your use case is If wasChange Then MsgBox "There was at least one change!" End If End Sub 

I'm not quite sure what you are looking for, you need to modify the print / msgbox instructions accordingly. Depending on your use case, you may need to slightly change this value, but it works in all my test cases.

Answer 2

an event does not occur that fires when the type of the cell format changes.

however, I received this information from another forum site.

To change the format of cells without using a macro, you must select the cells (by left-clicking the icon or right-clicking). therefore, using SelectionChanged worksheets, whenever a cell is selected, you get the format and address of that cell, and also check the address and format of the previous VS cell, what is the format of the previous cell. Thus, if the format of the previous cell is now different from the previous one, it has been changed.

You can use this in conjunction with the change event to see if the format was changed between them (after changing the contents of the cells) and when the cell was selected.

here is a link to another forum, because I cannot claim this as my own invention: http://www.mrexcel.com/forum/excel-questions/3704-calculate-format-change.html

Answer 3

Based on https://stackoverflow.com/a/3129608/158, there is a code that might work for you, assuming that the change will be generated by the user and the selected range will change after they change ...

 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static LastRange As Range Static LastNumberFormat As String If Not LastRange Is Nothing Then If LastRange.Cells(1).NumberFormat <> LastNumberFormat Then 'Your action or message box notification goes here End If End If Set LastRange = Target LastNumberFormat = Target.NumberFormat End Sub