VBA alert user to have space and clear value if not

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
623
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
VBA Code:
        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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
How about
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top