How To Trigger An Excel VBA Event When Cell Value Is Deleted

This post actually was birthed from a comment made on one on my Youtube videos:

I wanted to know as how to trigger an event when a cell value is deleted?

(a comment to this post’s video: http://www.vbastring.com/blog/how-to-run-a-macro-when-cell-value-changes-in-excel-vba/)

So we are going to trigger an Excel VBA event when a value is deleted from a cell.

In the above screen shot we have a fake list of names in column A of the worksheet.

We are deleting the value from Cell “A4”, and we are adding a little confirmation message to confirm to the user that that’s what they want to do.

If we click “Yes”, the old value (existing value) gets “deleted” (substituted) with the current value (a blank value).

Private Sub Worksheet_Change(ByVal Target As Range)
    'this was modified from an idea from https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba
    
    
    Static blnAlreadyBeenHere As Boolean
    Dim intAnswer As Integer
    
    'This piece avoid to execute Worksheet_Change again
    If blnAlreadyBeenHere Then
        blnAlreadyBeenHere = False
        Exit Sub
    End If
    
    'Now, we will create variant variables store the old and new value
    Dim varOldValue As Variant
    Dim varNewValue As Variant
    
    'Use this to store new value
    varNewValue = Target.Value
    
    'Use the 'undo' functionality to retrieve the old value
    
    'Here we will tell the Worksheet_Change event to avoid calling a new Worksheet_Change execution
    blnAlreadyBeenHere = True
    
    Application.Undo
    
    'now we can store the old value
    varOldValue = Target.Value
    
    'Now rewrite the cell with the new value stored earlier
    
    'Here again we will tell the Worksheet_Change event to avoid calling a new Worksheet_Change execution
    blnAlreadyBeenHere = True
    Target.Value = varNewValue
    
    '***************************************************************
    'Now we have the 2 values stored in varOldValue and varNewValue
    '***************************************************************

    'Check if the cell value was deleted:
    
    If varNewValue = "" Then
        intAnswer = MsgBox("Delete the current cell's value?", vbYesNo, "Confirmation")
        If intAnswer = vbNo Then
            'Here again we will tell the Worksheet_Change event to avoid calling a new Worksheet_Change execution
            blnAlreadyBeenHere = True
            
            'set the deleted cell value to what it was before the deletion. (couldn't use undo)
            Target.FormulaR1C1 = varOldValue
            
            'Or, fire the event you want, like logging the deletion in another file.
            
        End If
    Else
        'Just for reference:
        Debug.Print "oldval: " & varOldValue & ", newval: " & varNewValue
    End If


End Sub

Do you have questions?

Let me know. Also, share this with someone else.