Data Validation - Code Not Recognizing Something

[Paige]

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_2:PARTS1_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_2:PARTS1_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_2:PARTS1_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



--
Regards,
Tom Ogilvy

"Paige" <[email protected]> wrote in message
news:[email protected]...
> 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_2:PARTS1_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

[Paige]

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_2:PARTS1_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" wrote:

> 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_2:PARTS1_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_2:PARTS1_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
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Paige" <[email protected]> wrote in message
> news:[email protected]...
> > 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_2:PARTS1_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]

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_2:PARTS1_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

--
Regards,
Tom Ogilvy

"Paige" <[email protected]> wrote in message
news:[email protected]...
> 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_2:PARTS1_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" wrote:
>
> > 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_2:PARTS1_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_2:PARTS1_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
> >
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Paige" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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_2:PARTS1_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
> >
> >
> >

[Paige]

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

"Tom Ogilvy" wrote:

> 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_2:PARTS1_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
>
> --
> Regards,
> Tom Ogilvy
>
> "Paige" <[email protected]> wrote in message
> news:[email protected]...
> > 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_2:PARTS1_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" wrote:
> >
> > > 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_2:PARTS1_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_2:PARTS1_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
> > >
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Paige" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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_2:PARTS1_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
> > >
> > >
> > >
>
>
>