Forum Discussion

Brass Contributor

Aug 27, 2023

Solved

My User Defined Function in Worksheet_Change event doesn't work if I select entire table and clear

I have this Table1 with fields DateTested, Initial, Sign. The idea/goal is if user enters an x (any character for that matter) it will populate initial and date tested auto magically. It seems l...

Formulas and Functions

Macros and VBA

HansVogelaar

Aug 27, 2023

rodsan724

Loop through the affected cells:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("Table1[Sign]"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("Table1[Sign]"), Target)
            If rng.Value = "" Then
                rng.Offset(0, -1).ClearContents
                If Not rng.Offset(0, -1).Comment Is Nothing Then
                    rng.Offset(0, -1).Comment.Delete
                End If
                rng.Offset(0, -2).ClearContents
            Else
                rng.Offset(0, -1).Value = "RS"
                If rng.Offset(0, -2) = "" Then
                    rng.Offset(0, -1).AddComment Text:="hello world"
                End If
                Target.Offset(0, -2).Value = Date
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

HansVogelaar

MVP

Aug 27, 2023

rodsan724

Loop through the affected cells:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("Table1[Sign]"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("Table1[Sign]"), Target)
            If rng.Value = "" Then
                rng.Offset(0, -1).ClearContents
                If Not rng.Offset(0, -1).Comment Is Nothing Then
                    rng.Offset(0, -1).Comment.Delete
                End If
                rng.Offset(0, -2).ClearContents
            Else
                rng.Offset(0, -1).Value = "RS"
                If rng.Offset(0, -2) = "" Then
                    rng.Offset(0, -1).AddComment Text:="hello world"
                End If
                Target.Offset(0, -2).Value = Date
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

rodsan724

Brass Contributor

Aug 27, 2023

Thank you. If you don't mind, can you please help me understand what was happening or some essential concept I was missing.

HansVogelaar
MVP
Aug 27, 2023
rodsan724

If you change the value of more than one cell at once, by filling down or copy/paste or by clearing a range, the Value property of Target becomes an array of values instead of a single value. A line such

If Target = "" Then

will cause an error since you're trying to compare an array to a single value.

Hence the need to loop through the cells.