Data Validation - Code Not Recognizing Something

[Guest]

I have the following data validation code; the intent is to have data
validation in a cell with the range name of PARTS1_PC1_1 at all times (which
the code does do-see the first section of code below), but data validation in
the other cells ONLY if PARTS1_PC1_1 is not blank (see the second section of
code below). The problem I have is that if PARTS1_PC1_1 is filled in and
then deleted, the data validation remains in the other cells - it doesn't
'reset' (i.e., clear out). Can you advise what I am doing wrong here? Any
help would be greatly appreciated! Thanks....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("PARTS1_PC1_1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PartsCategories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please use the drop-down menu to select your entry."
.ShowInput = True
.ShowError = True
End With

If Range("PARTS1_PC1_1").Value <> "" Then
With Range("PARTS1_PC7_2ARTS1_PC7_4").Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter a whole number."
.ShowInput = True
.ShowError = True
End With
End If
End Sub

 

[Tom Ogilvy]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("PARTS1_PC1_1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PartsCategories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please use the drop-down menu to select your entry."
.ShowInput = True
.ShowError = True
End With


With Range("PARTS1_PC7_2ARTS1_PC7_4").Validation
.Delete
if Application.CountA(Range("Parts1_PC1_1")) <> 0 then
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter a whole number."
.ShowInput = True
.ShowError = True
End if
End With

End Sub

If PARTS1_PC1_1 is actually a one cell range, then you can just move your IF
statement


With Range("PARTS1_PC7_2ARTS1_PC7_4").Validation
.Delete
If Range("PARTS1_PC1_1").Value <> "" Then
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter a whole number."
.ShowInput = True
.ShowError = True
End If
End With

 

[Guest]

Thanks, Tom. In my zeal to simplify things for the post, I misled you a bit.
There are multiple ranges that depend upon PARTS1_PC1_1 being blank or not.
Here's a little more expanded version:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("PARTS1_PC1_1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PartsCategories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please use the drop-down menu to select your entry."
.ShowInput = True
.ShowError = True
End With

If Range("PARTS1_PC1_1").Value <> "" Then
With Range("PARTS1_PC7_2ARTS1_PC7_4").Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter a whole number."
.ShowInput = True
.ShowError = True
End With
With Range("PARTS8_PC4_1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=WhoProvidesHandling"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Invalid entry; please use the drop-down menu."
.ShowInput = True
.ShowError = True
End With
With Range("PARTS8_PC25_1").Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, _
Operator:=xlGreater, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter" & _
Chr(13) & "a number greater than 0."
.ShowInput = True
.ShowError = True
End With

End If
End Sub

 

[Tom Ogilvy]

I think the suggestion would be similar - just requiring adjustment for each
range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("PARTS1_PC1_1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PartsCategories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please use the drop-down menu to select your entry."
.ShowInput = True
.ShowError = True
End With

With Range("PARTS1_PC7_2ARTS1_PC7_4").Validation
.Delete
If Range("PARTS1_PC1_1").Value <> "" Then
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter a whole number."
.ShowInput = True
.ShowError = True
End if
End With
With Range("PARTS8_PC4_1").Validation
.Delete
If Range("PARTS1_PC1_1").Value <> "" Then
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=WhoProvidesHandling"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Invalid entry; please use the drop-down menu."
.ShowInput = True
.ShowError = True
End If
End With
With Range("PARTS8_PC25_1").Validation
.Delete
If Range("PARTS1_PC1_1").Value <> "" Then
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, _
Operator:=xlGreater, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter" & _
Chr(13) & "a number greater than 0."
.ShowInput = True
.ShowError = True
End if
End With

End Sub

 

[Guest]

You are the MASTER!!!! Works great - thanks so much Tom!