vba 如何检测单元格格式的变化？

How to detect changes in cell format?

提问by sigil

I want to embed a procedure in an Excel sheet that will detect when a cell's format changes, e.g. from Text to Number.

我想在 Excel 工作表中嵌入一个过程，该过程将检测单元格的格式何时更改，例如从文本更改为数字。

But I can't figure out how to get the cell's format type. I tried using the Worksheet_Changeevent handler to check the data type, as follows:

但我不知道如何获取单元格的格式类型。我尝试使用Worksheet_Change事件处理程序检查数据类型，如下所示：

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 cell A1's data type from Number to Text, Worksheet_Changeis not triggered; the event handler is only called if I change the contents of the cell.

但是有了这段代码，如果我将单元格 A1 的数据类型从数字更改为文本，Worksheet_Change则不会触发；仅当我更改单元格的内容时才会调用事件处理程序。

Also, this procedure can detect if the contents are changed from a number to an alphabetical string, e.g. from "35.12" to "abcd", but not Number-type number to Text-type 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 alert is not triggered.

此外，该程序可以检测内容是否从数字变为字母字符串，例如从“35.12”变为“abcd”，而不是数字类型数字变为文本类型数字；如果我将单元格B1设置为文本，然后输入“40”，然后将单元格B1的内容粘贴到单元格A1中，vartype()仍然返回“5”，因此不会触发警报。

How can I detect that the format has changed, regardless of whether the content type has changed?

无论内容类型是否已更改，如何检测格式已更改？

采纳答案by enderland

Great question!

好问题！

If you are only looking to trigger an event on the NumberFormat change (you appear to be calling this incorrectly the data format, NumberFormatis the attribute you want), the following is a good example.

如果您只想在 NumberFormat 更改时触发事件（您似乎错误地将其称为数据格式，NumberFormat是您想要的属性），以下是一个很好的示例。

I'm intercepting all selection change events and checking if any NumberFormat changed.

我正在拦截所有选择更改事件并检查是否有任何 NumberFormat 更改。

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 exactly sure what you are looking for, you'll have to modify the print/msgbox statements appropriately. Depending on your use case you may have to modify this slightly but it works in all my test examples.

我不太确定你在找什么，你必须适当地修改 print/msgbox 语句。根据您的用例，您可能需要稍微修改它，但它适用于我所有的测试示例。

回答by user1759942

there does not appear to be an event that fires when a cells format type changes.

当单元格格式类型更改时，似乎没有触发事件。

however, I got this info from another forum site.

但是，我从另一个论坛站点获得了此信息。

To edit a cells format without the use of a macro the cells must be selected (via left click then an icon, or right click). so, using the worksheets SelectionChanged, whenever a cell is selected you grab the format and address of that cell as well as check the address and format of the previous cell VS what the format of the previous cell is now. thus, if the format of the previous cell is different now than it was when last captured, it has been changed.

要在不使用宏的情况下编辑单元格格式，必须选择单元格（通过左键单击图标，或右键单击）。因此，使用工作表 SelectionChanged，无论何时选择一个单元格，您都可以获取该单元格的格式和地址，并检查前一个单元格的地址和格式 VS 前一个单元格的格式现在是什么。因此，如果前一个单元格的格式现在与上次捕获时的格式不同，则它已被更改。

You can use this in conjuction with the change event, to see if the format has been changed between now (after the cells contents have changed) and when the cell was selected.

您可以将其与更改事件结合使用，以查看从现在（单元格内容更改后）到单元格被选中时格式是否已更改。

here's the link to the other forum as I cant claim this as my own invention: http://www.mrexcel.com/forum/excel-questions/3704-calculate-format-change.html

这是另一个论坛的链接，因为我不能声称这是我自己的发明：http: //www.mrexcel.com/forum/excel-questions/3704-calculate-format-change.html

回答by Instant Breakfast

Based on this response on StackOverflowhere is some code that might work for you, assuming the change will be user generated and the selected range will change after they make the change...

基于StackOverflow上的这个响应，这里有一些代码可能对您有用，假设更改将由用户生成，并且所选范围将在他们进行更改后更改...

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