Checking If Month Changed – Excel VBA Old Value Cell Target

In response to a comment on my Youtube video:

https://www.youtube.com/watch?v=Z9kt86I_rq0&feature=em-comments

*This gives an example of how to get notified of a change made to one of the worksheet cells.

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 Month(varNewValue) <> Month(varOldValue) Then
        MsgBox " Month has changed"
    End If
    
    'set the cell equal to the new value
    Target.Value = varNewValue
    
    
End Sub

Posted

in

by

Tags: