Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Data Validation - Code Not Recognizing Something
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 270
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 270
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 270
Default 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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
recognizing double digit numbers in code DB Excel Discussion (Misc queries) 3 March 2nd 07 04:43 PM
Recognizing something is being pasted in code. Bob Holmes Excel Programming 3 May 20th 04 03:25 PM
Data validation on print - need code seeker Excel Programming 2 January 15th 04 07:12 PM
Data validation code Gareth[_3_] Excel Programming 0 November 26th 03 06:01 PM
Data validation using code Gareth[_3_] Excel Programming 1 November 26th 03 12:27 AM


All times are GMT +1. The time now is 04:57 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement