☰

一行中多个单元格发生更改时的时间戳

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba
一行中多个单元格发生更改时的时间戳

我一直在尝试使用宏来为某一列中的单元格添加时间戳，当该行中的另一个单元格发生变化时，我从此处的另一个线程中获得的下面的方法可以完美运行。

但是我想做同样的事情，但这次如果行中的任何单元格发生变化。我添加了一个额外的列，它通过一个简单的“&”公式将行中的所有其他条目组合在一起，但当公式单元格更新时，下面的方法似乎不起作用。

有人知道我该怎么做吗？

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wk As Workbook
    Set wk = ThisWorkbook
    Dim ws As Worksheet
    Set ws = ActiveSheet

    WatchedColumn = 2
    BlockedRow = 1
    TimestampColumn = 4
    Crow = Target.Row
    CColumn = Target.Column
    If CColumn = WatchedColumn And Crow > BlockedRow Then
            Cells(Crow, TimestampColumn) = Now()
    End If
End Sub

答案1

根据提供的附加信息，我认为我可能有一个可行的解决方案。我考虑过编辑我原来的答案，但它的变化相当大，所以我认为提供一个新答案可能会更有帮助。乍一看，它可能看起来很长，但删除注释，它就不会那么“麻烦”。我对其进行了相当广泛的评论，只是为了提供更好的清晰度。

'global variable for the original value
Dim old_Value As Variant

'on select change event is used to trap original value of the cell being changed
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'check to see if it is a single cell or multiple cells
    If Selection.Cells.Count = 1 Then

        'check if cell is in appropriate range
        If Target.Row < 6 And Target.Column = 1 Then

            'set original value of the cell to the global "old_Value" variable
            old_Value = Target.Value
        End If

    'if more than one cell is being updated (dragging, ctrl-enter, etc.)
    Else

        'set value of old value to concatenation of original values
        For i = 1 To 5
            old_Value = old_Value & Cells(i, 1)
        Next
    End If
End Sub


'on change event is used to compare the values of the old cell vs the new cell
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Integer            'variable for for loop (if needed)
    Dim new_Value As String     'variable for storing new values concatenation (if needed)

    'check to see if it is a single cell or multiple cells
    If Selection.Cells.Count = 1 Then

        'make sure cell is in appropriate row and column range and compare old value to new value
        If Target.Row < 6 And Target.Column = 1 And old_Value <> Target.Value Then

            'if change happened set timestamp
            Cells(6, 1) = Now()
        End If

    'if more than one cell is being updated (dragging, ctrl-enter, etc.)
    Else

        'concatenate new values into one variable
        For i = 1 To 5
            new_Value = new_Value & Cells(i, 1)
        Next

        'compare new with old and set timestamp if appropriate
        If new_Value <> old_Value Then
            Cells(6, 1) = Now()
        End If
    End If
End Sub

我不能肯定地说这是最好的方法，但它对你所描述的情况有效。希望你觉得它有用。

答案2

我可能没有正确理解您想要做的事情；但是，如果我理解了，有一种方法可以使用全局变量来存储原始单元格值。在 SelectionChange 事件中设置全局值 (old_Value) = 目标值，然后您可以使用它来比较单元格值并在 Worksheet_Change 事件中根据需要更新时间戳。

Dim old_Value As String

Private Sub Worksheet_Change(ByVal Target As Range)

   TimestampColumn = 4
   Ccolumn = Target.Column
   Crow = Target.Row

   If old_Value <> Cells(Crow, Ccolumn) Then
      Cells(Crow, TimestampColumn) = Now()
   End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   old_Value = Target.Value
End Sub

我可能应该补充一点，如果您同时选择一个范围或将一个单元格值拖到多个单元格（行或列）中，这将引发错误。

相关内容