检测细胞值是否实际上通过编辑改变 [英] Detect whether cell value was actually changed by editing

问题描述

Worksheet_Change 当单元格值更改时触发（这是我想要的），但是当您输入单元格时也会触发，就像编辑它一样，但不要实际上改变了单元格的价值（这是我不想发生的）。假设我想为改变值改变的单元格添加阴影。

Worksheet_Change triggers when a cell value is changed (which is what I want), but it also triggers when you enter a cell as if to edit it but don't actually change the cell's value (and this is what I don't want to happen).

所以我编写这个：

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Interior.ColorIndex = 36
End Sub

现在来测试我的工作：更改单元格A1，单元格被突出显示。这是所需的行为。到现在为止还挺好。然后双击B1，但不要更改那里的值，然后单击C1。你会注意到B1被突出显示！这不是所期望的行为。

Now to test my work: Change cell A1 and the cell gets highlighted. That's the desired behaviour. So far so good. Then, double click B1 but don't change the value there and then click C1. You'll notice B1 gets highlighted! And this is not the desired behaviour.

我必须通过这里讨论的方法来捕获旧值，然后在突出显示单元格之前比较旧到新的？我确实希望有一些我想要的东西。

Do I have to go through the methods discussed here of capturing the old value, then compare old to new before highlighting the cell? I certainly hope there's something I'm missing.

推荐答案

我建议自动维护一张镜像副本用于与更改的单元格值进行比较。

I suggest automatically maintaining a "mirror copy" of your sheet, in another sheet, for comparison with the changed cell's value.

@brettdj和@JohnLBevan本质上提出做同样的事情，但是他们分别将注释或字典存储单元格值（对于这些想法，+1）。然而，我的感觉是，在概念上，在单元格中备份单元格而不是其他对象（特别是您或用户可能希望用于其他目的的注释）更简单。

@brettdj and @JohnLBevan essentially propose doing the same thing, but they store cell values in comments or a dictionary, respectively (and +1 for those ideas indeed). My feeling, though, is that it is conceptually much simpler to back up cells in cells, rather than in other objects (especially comments, which you or the user may want to use for other purposes).

所以说我有 Sheet1 其用户可能更改的单元格。我创建了另一张表格，名为 Sheet1_Mirror （您可以在 Workbook_Open 中创建，并且如果您愿意，可以设置为隐藏 - 由你决定）。首先， Sheet1_Mirror 的内容与 Sheet1 的内容相同（再次，您可以在 Workbook_Open ）。

So, say I have Sheet1 whose cells the user may change. I created this other sheet called Sheet1_Mirror (which you could create at Workbook_Open and could set to be hidden if you so desire -- up to you). To start with, the contents of Sheet1_Mirror would be identical to that of Sheet1 (again, you could enforce this at Workbook_Open).

每次 Sheet1 的 Worksheet_Change 被触发，代码检查 Sheet1 中更改单元格的值是否与 Sheet1_Mirror 中的值实际不同。如果是这样，它会执行所需的操作并更新镜像表。如果没有，那么没有。

Every time Sheet1's Worksheet_Change is triggered, the code checks whether the "changed" cell's value in Sheet1 is actually different from that in Sheet1_Mirror. If so, it does the action you want and updates the mirror sheet. If not, then nothing.

这应该让你走在正确的轨道上：

This should put you on the right track:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    For Each r In Target.Cells
        'Has the value actually changed?
        If r.Value <> Sheet1_Mirror.Range(r.Address).Value Then
            'Yes it has. Do whatever needs to be done.
            MsgBox "Value of cell " & r.Address & " was changed. " & vbCrLf _
                & "Was: " & vbTab & Sheet1_Mirror.Range(r.Address).Value & vbCrLf _
                & "Is now: " & vbTab & r.Value
            'Mirror this new value.
            Sheet1_Mirror.Range(r.Address).Value = r.Value
        Else
            'It hasn't really changed. Do nothing.
        End If
    Next
End Sub

这篇关于检测细胞值是否实际上通过编辑改变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！