Stop event running...

[Rudi]

Hi,

The code below creates an overflow error on this line:

Code: Select all

If Not Application.CountIf(Target, "=") = Target.Cells.Count Then

when I delete rows A100:XFD1048000, below the data.

What other test can I use to avoid the event running if the target cells are not selected?
TX

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Application.CountIf(Target, "=") = Target.Cells.Count Then
        If Not Intersect(Union([B18], [B19], [B20], [B25], [B26], [B27], [B32], [B33], [B34]), Target) Is Nothing Then
            Target.Value = CleanPhoneNumber(CStr(Target.Value))
        End If
    End If
    Application.EnableEvents = True
End Sub

[HansV]

Try this. It first checks whether the target intersects the range of cells you want to inspect.
This version also allows multiple cells to be edited simultaneously (for example by pressing Ctrl+Enter).

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    If Not Intersect(Range("B18:B20,B25:B27,B32:B34"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each cel In Intersect(Range("B18:B20,B25:B27,B32:B34"), Target)
            If cel.Value <> "" Then
                cel.Value = CleanPhoneNumber(CStr(cel.Value))
            End If
        Next cel
        Application.EnableEvents = True
    End If
End Sub

[Rudi]

Works beautifully.
Many TX