ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Data Validation - Code Not Recognizing Something (https://www.excelbanter.com/excel-programming/326909-data-validation-code-not-recognizing-something.html)

Paige
Data Validation - Code Not Recognizing Something
 
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
Data Validation - Code Not Recognizing Something
 
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" wrote in message
...
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
Data Validation - Code Not Recognizing Something
 
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" wrote in message
...
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
Data Validation - Code Not Recognizing Something
 
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" wrote in message
...
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" wrote in message
...
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
Data Validation - Code Not Recognizing Something
 
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" wrote in message
...
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" wrote in message
...
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


All times are GMT +1. The time now is 01:35 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com