使用VBA如何检测工作表中的任何值何时发生变化？ [英] Using VBA how do I detect when any value in a worksheet changes?

问题描述

我有一个包含数百个值的列的工作表。一旦工作表中的任何值发生变化，我想要单元格A1说更改值。我试图制作一些像下面看到的代码，但是我无法想象如何捕获OriginalValue变量中的原始值。如何检测任何单元格中的值是否与目标值不同的值更改？

  Private Sub Worksheet_Change（ByVal目标为范围）
 
如果Target.Value<>然后
 Range（A1）Value =Value Change
 End If 
 
 End Sub 
  

解决方案

继续阅读我的意见。我已经评论过代码，所以你不会有一个问题了解它。但是如果你这样做只是问一下。 ：

  Dim PrevValue As Variant 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
 
'~~>检查是否有1个以上的单元格更改
如果Target.Cells.CountLarge> 1然后退出Sub 
 
'~~>检查是否在A1 
中没有发生更改如果不相交（目标，范围（A1））没有，然后退出子
 
错误GoTo Whoa 
 
 Application.EnableEvents = False 
 
'~~>比较
如果Target.Value<> PrevValue Then 
 Range（A1）。Value =Value of& Target.Address& 从& _ 
 PrevValue& 到& Target.Value 
 
'~~>将新值存储到以前的值
 PrevValue = Target.Value 
 End If 
 
 Letscontinue：
 Application.EnableEvents = True 
 Exit Sub 
哇：
 MsgBox Err.Description 
 Resume Letscontinue 
 End Sub 
 
 Private Sub Worksheet_SelectionChange（ByVal Target As Range）
 PrevValue = Target.Value 
 End Sub 
  

I have a worksheet that contains a few columns with hundreds of values. I want cell A1 to say "Value Changed" as soon as any value changes in the worksheet. I tried to make some code like what you see below, but I couldn't think of how to capture the original value in the OriginalValue variable. How can I detect when the value in any cell changes from something that is different than the Target value?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value <> OriginalValue Then
    Range("A1").Value = "Value Change"
End If

End Sub

解决方案

Further to my comments see this. I have commented the code so you will not have a problem understanding it. But if you do then simply ask. :)

Dim PrevValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

    '~~> Check if more than 1 cell is changed
    If Target.Cells.CountLarge > 1 Then Exit Sub

    '~~> Check if the change didn't happen in A1
    If Not Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

    On Error GoTo Whoa

    Application.EnableEvents = False

    '~~> Compare
    If Target.Value <> PrevValue Then
        Range("A1").Value = "Value of " & Target.Address & " changed from " & _
                            PrevValue & " to " & Target.Value

        '~~> Store new value to previous value
        PrevValue = Target.Value
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

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

这篇关于使用VBA如何检测工作表中的任何值何时发生变化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！