Как обнаружить изменения в формате ячейки?

Question

Я хочу встроить процедуру в лист Excel, которая будет определять, когда изменяется формат ячейки, например, с текста на номер.

Но я не могу понять, как получить тип формата ячейки. Я пытался использовать 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

Но с этим кодом, если я изменю тип данных ячейки A1 с Number на Text, Worksheet_Change не срабатывает; обработчик события вызывается только если я изменяю содержимое ячейки.

Кроме того, эта процедура может определять, изменяется ли содержимое с номера на алфавитную строку, например, с "35.12" на "abcd", но не с номера типа числа на номер типа текста; если я установлю для ячейки B1 текст, затем введите "40", затем вставьте содержимое ячейки B1 в ячейку A1, vartype() по-прежнему возвращает "5", поэтому предупреждение не сработало.

Как я могу определить, что формат изменился, независимо от того, изменился ли тип контента?

Answer 1

Отличный вопрос!

Если вы только хотите вызвать событие при изменении NumberFormat (вы, кажется, неправильно называете это форматом данных, NumberFormat является атрибутом, который вы хотите), следующий хороший пример.

Я перехватываю все события изменения выбора и проверяю, изменился ли какой-либо 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

Я не совсем уверен, что вы ищете, вам придется соответствующим образом изменить операторы print / msgbox. В зависимости от вашего варианта использования вам, возможно, придется немного изменить это, но это работает во всех моих тестовых примерах.

Answer 2

Похоже, что не происходит событие, которое возникает при изменении типа формата ячейки.

однако, я получил эту информацию с другого сайта форума.

Чтобы редактировать формат ячеек без использования макроса, необходимо выбрать ячейки (щелчком левой кнопкой мыши, затем значком или щелчком правой кнопки мыши). поэтому, используя рабочие листы SelectionChanged, всякий раз, когда выбирается ячейка, вы выбираете формат и адрес этой ячейки, а также проверяете адрес и формат предыдущей ячейки VS, каков формат предыдущей ячейки. таким образом, если формат предыдущей ячейки сейчас отличается от того, который был при последнем захвате, он был изменен.

Вы можете использовать это в сочетании с событием изменения, чтобы увидеть, изменился ли формат с настоящего момента (после изменения содержимого ячеек) и когда ячейка была выбрана.

вот ссылка на другой форум, поскольку я не могу заявить, что это мое собственное изобретение: http://www.mrexcel.com/forum/excel-questions/3704-calculate-format-change.html

Answer 3

На основе этого ответа на Stackru приведен код, который может работать на вас, при условии, что изменение будет сгенерировано пользователем, а выбранный диапазон изменится после того, как они внесут изменение...

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