JasonRafton
New Member
- Joined
- Feb 1, 2023
- Messages
- 11
- Office Version
- 365
- Platform
- Windows
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
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