How To Run Macro (VBA) When The Cell Value Changes

In this post, you are going to find out how to detect when a cell value on your worksheet changes.

So when my F7 value changes from “george” or blank to something else, a message box appears.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Static blnDoneThis As Boolean
    Dim varOldValue As Variant
    Dim varNewValue As Variant
    
    'initialize a variable to regulate the Worksheet_Change event
    If blnDoneThis Then
        blnDoneThis = False
        Exit Sub
    End If
    
    'get the new value
    varNewValue = Target.Value
    
    'set our regulator to true
    blnDoneThis = True
    
    'time to get the old value and store it in a variable
    Application.Undo

    varOldValue = Target.Value
    
    'tell the regulator, it's ok
    blnDoneThis = True
    
    'check if the month has changed from the old value
    If varNewValue <> varOldValue Then
        MsgBox " value has changed"
    End If
    
    'set the cell equal to the new value
    Target.Value = varNewValue
    
    
End Sub

Let me know if you have questions.


Posted

in

by

Tags: