Excel VBA worksheet event stuck in constant loop

Question

I've combined two SO questions/answers to create some VBA to automatically replace special characters in a cell (E6 in this example).

It works great for replacing special characters, but when I clear the cell (Select E6 and press Delete key), it gets stuck in a constant loop.

Code below:

Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],}"  'modify as needed

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Cells(6, 5)) Is Nothing Then

        Dim myString As String
        Dim newString As String
        Dim char As Variant

        myString = Cells(6, 5).Value
        newString = myString

        For Each char In Split(SpecialCharacters, ",")

            newString = Replace(newString, char, "")

        Next

        Cells(6, 5).Value = newString

    End If

End Sub

I tried adding an Else statement to block the loop from happening but it did not work:

Else

    Cells(6, 5).Select

SO Question/Answer references:

excel VBA run macro automatically whenever a cell is changed

Removing special characters VBA Excel

Can anyone provide insight on what I'm doing wrong?

Thanks!

I guess I thought that's what `If Not Intersect(Target, Cells(6, 5)) Is Nothing Then` was handling. So you mean add in an `ElseIf Cells(6, 5).Value = "" Then` type of thing? — TheGuyThatDoesn'tKnowMuch, Jun 30 '16 at 16:17
No, that line is testing whether the target cell intersects with cells(6,5). But look like @ScottHoltzman, has your answer. — Scott Craner, Jun 30 '16 at 16:21

score 1 · Accepted Answer · answered Jun 30 '16 at 16:18

1

Turn off the Event Handler before you write back to the cell. Otherwise, writing back to the cell fires the Event Handler again, and will never stop it.

Application.EnableEvents = False
Cells(6, 5).Value = newString
Application.EnableEvents = True

answered Jun 30 '16 at 16:18

Scott Holtzman

27,099
5
37
72

Excel VBA worksheet event stuck in constant loop

1 Answers1