How do I execute instructions only if the cell value change?

Question

I have a ByVal code to clear the contents of a specific range inside a table, it works. But I need to add a condition for the instructions execute if the RANGE VALUE (content) change, not if I only place the cursor on it.

Also, someone knows how to reference a table column in VBA? Now I'm using an estimated range "C1:C999" but I'll like to use his name "A_[OPERATION]".

This is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Not Application.Intersect(Range("C1:C999"), Range(Target.Address)) Is Nothing Then
    
       Range(Selection, Selection.End(xlToRight)).ClearContents

   End If

End Sub

Answer 1

A Worksheet Change

• Adjust the table name (tName) and the header (column) name (hName).
• I have adjusted it to clear contents in the cells after the column.
• If you really need to clear the contents of the column too, then replace cel.Offset(, 1) with cel.
• In a table, the current setup will automatically clear the contents of all the cells to the right of the specified column, if a value in the column is manually or via VBA changed. This will not work if the column contains formulas. Non-contiguous deletions are also supported.

The code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const ProcName As String = "Worksheet_Change"
    On Error GoTo clearError
    
    Const tName As String = "A_"
    Const hName As String = "OPERATION"
    
    Dim rng As Range
    Set rng = Range(tName & "[" & hName & "]")
    Set rng = Intersect(rng, Target)
    If rng Is Nothing Then GoTo ProcExit
    
    Application.EnableEvents = False
    
    With ListObjects(tName).HeaderRowRange
        Dim LastColumn As Long
        LastColumn = .Columns(.Columns.Count).Column
    End With
    Dim cel As Range
    For Each rng In rng.Areas
        For Each cel In rng.Cells
            With cel.Offset(, 1)
                .Resize(, LastColumn - .Column + 1).ClearContents
            End With
        Next cel
    Next rng

CleanExit:
    Application.EnableEvents = True
    GoTo ProcExit

clearError:
    Debug.Print "'" & ProcName & "': " & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    On Error GoTo 0
    GoTo CleanExit

ProcExit:

End Sub

Answer 2

You could use the change event instead.

Here's a link to the documentation: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.change

Alternatively, you could save the value of your target cell in a variable and check if the value changed before executing your clear contents.

For your second question, you should probably ask about it in a separate post.

Answer 3

A change in cell value is captured by the Worksheet_Change event-handle.

However, this handle will trigger even if it is a false change. For example, if the cell value before the change is "A", and user just entered "A" again in the cell, the Change event procedure will be triggered anyhow.

To avoid this, we can use Worksheet_Change and Worksheet_SelectionChange together.

Using Worksheet_SelectionChange, we record the old value somewhere, say a Name. Then using Worksheet_Change, we can compare what the user has entered against the Name to see if a true change is made.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Nm As Name: Set Nm = ActiveWorkbook.Names("OldValue")
    If Target.Count = 1 Then
        'This only record one old cell value.
        'To record multiple cells old value, use a hidden Worksheet to do so instead of a Name.
        Nm.Comment = Target.Value2
    else
        Nm.Comment = Target.Value2(1, 1)
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Nm As Name: Set Nm = ActiveWorkbook.Names("OldValue")
    If Target.Value2 <> Nm.Comment Then
        Debug.Print "True change"
    Else
        Debug.Print "False change"
    End If
End Sub

You can access a table's methods and properties through the listobject object. Below is an example how to do so.

Sub Example()
    Dim lo As ListObject
    Dim lc As ListColumn
    Set lo = Range("Table1").ListObject
    Set lc = lo.ListColumns("Column2")
End Sub

That said, for your case, it would be Range("A_").ListObject.ListColumns("OPERATION").DataBodyRange.