如何检测单元格格式的变化？ [英] How to detect changes in cell format?

问题描述

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

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.

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

But I can't figure out how to get the cell's 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

但是使用此代码，如果我将单元格A1的数据类型从数字更改为文本， Worksheet_Change 不被触发;事件处理程序仅在我更改单元格的内容时被调用。

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

此外，此过程可以检测内容是否从一个数字改变为一个字母字符串，例如从35.12到abcd，但不是数字类型号到文本型号;如果我将单元格B1设置为文本，则输入40，然后将单元格B1的内容粘贴到单元格A1中， vartype（）仍然返回5，因此提醒不会触发。

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.

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

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

推荐答案

很好的问题！

如果您只是在NumberFormat更改时触发事件（您似乎正在调用这个数据格式不正确， NumberFormat 是你想要的属性），以下是一个很好的例子。

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

I '拦截所有选择更改事件并检查是否有任何NumberFormat发生更改。

I'm intercepting all selection change events and checking if any NumberFormat 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

我不是确定你正在寻找什么，你必须适当地修改print / msgbox语句。根据您的使用情况，您可能需要稍微修改，但在所有测试示例中都可以使用。

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.

这篇关于如何检测单元格格式的变化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！