VBA alert user to have space and clear value if not

[hajiali]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once
        If Not Application.Intersect(Target, Me.Range("D2:D2002")) Is Nothing Then    ' indicates the Target range
        If Left(Target, 4) = "STW " Or Left(Target, 5) = "PSTW " Or Left(Target, 4) = "TTW " Then
        Exit Sub
        Else
        MsgBox ("ALL SHIFT MUST HAVE A SPACE BETWEEN STW,PSTW,TTW AND THE HOURS")
        Target = ""
    End If
    End If
End Sub

Using the code above I able to alert the user if STW, PSTW, and TTW are not followed by Space however when I add to Clear the Target value

        Target = ""

it seems to give me the msgbox over and over again and then crashes. any suggestion to clear the target value is greatly appreciated

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once
        If Not Application.Intersect(Target, Me.Range("D2:D2002")) Is Nothing Then    ' indicates the Target range
        If Left(Target, 4) = "STW " Or Left(Target, 5) = "PSTW " Or Left(Target, 4) = "TTW " Then
        Exit Sub
        Else
        MsgBox ("ALL SHIFT MUST HAVE A SPACE BETWEEN STW,PSTW,TTW AND THE HOURS")
        Application.EnableEvents = False
        Target = ""
        Application.EnableEvents = True
    End If
    End If
End Sub

With your code, when you clear the cell it triggers the event again, putting you in a permanent loop