How to get rid of error code/window popping up

[JasonRafton]

Below is a code I have on my XL sheet.
I need to be able to put a NAME, that is not on any of the LISTS, in column 8 from time to time.
At the moment an ERROR CODE comes up and doesn't allow me.
Normally I would go into the data validation and uncheck the box. But that doesn't seem to work because of the CODE.
How can I get around this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Target.Value = "SUB" Then
' Adjust with your Own choices in Formula1:
With Target.Offset(0, 8).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="KRUEGER,ROYANS,T/SALES,VAWDREY,VOLVO,BDS,HAMMAR"
End With
ElseIf Target.Value = "WIP" Then
With Target.Offset(0, 8).Validation
' Adjust with your Own choices in Formula1:
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="ALEX B,JYE P,LUKE K,BDS-ETHAN,BDS LACHLAN,OP-MATT,STT-TRAVIS,STT-ALEX,STT-ALF,STT-NICK S,STT-TIM,TSS-GERRY,TSS-TRENTE"
End With
ElseIf Target.Value = "OS" Then
With Target.Offset(0, 8).Validation
' Adjust with your Own choices in Formula1:
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="ALEX B,JYE P,LUKE K,BDS-ETHAN,BDS LACHLAN,OP-MATT,STT-TRAVIS,STT-ALEX,STT-ALF,STT-NICK S,STT-TIM,TSS-GERRY,TSS-TRENTE"
End With
End If
End Sub

 

[rollis13]

In your macro change in the three lines of code the parameter:
AlertStyle:=xlValidAlertStop to (whichever you prefer):
AlertStyle:=xlValidAlertInformation or:
AlertStyle:=xlValidAlertWarning

 

[James006]

Hi,

You can use : AlertStyle:=xlValidAlertInformation

 

[JasonRafton]

Is there a way that I can have no alert at all?

 

[rollis13]

Add:
.ShowInput = False
or:
.ShowError = False

 

[JasonRafton]

Where exactly do I enter this?

 

[rollis13]

You can insert the ShowError just after the three lines with: .Add Type:=xlValidateList, ...