mutiple Validations for cells
 

--------------------------------------------------------------------------------

I have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a
date and Col T is a text(with list validation)
I have validation that if if T = "As is" and P < I or K (which ever one has
a value) then Error.
..Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as
is"",(IF(P2=(IF(K2<"""",K2,I2)),TRUE, FALSE)), TRUE)"

But if the user changes T to some other value then it is ok to change P, But
they could put any value in that field. I Need to validate that P is a DATE
also
I was trying to do a validation Greater than 01/01/2007 but I can not do
multiple validations on the same cell.
How can I validate P is a date also

mutiple Validations for cells
 

VB has an ISDate() function available in the language. Can you not use that
somehow; either in a test before your .Add Type:= statement or create a
Function like
Function IsADate(anyDate As Range)
IsADate = False
If IsDate(anyDate.Value) Then
IsADate = True
End If
End Function

and call it with a
IsADate(Worksheets("sheetname").Range("P2"))
type of statement

So, it takes on the form of
=IF(IsADate(Worksheets("sheetname").Range("P2"))," true action here","false
action here")
in VB. You could use it on a worksheet as simply =IsADate(P2)

Hope this helps some.

"ljhopkins_LOST and Cant find My Way" wrote:

--------------------------------------------------------------------------------

I have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a
date and Col T is a text(with list validation)
I have validation that if if T = "As is" and P < I or K (which ever one has
a value) then Error.
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as
is"",(IF(P2=(IF(K2<"""",K2,I2)),TRUE, FALSE)), TRUE)"

But if the user changes T to some other value then it is ok to change P, But
they could put any value in that field. I Need to validate that P is a DATE
also
I was trying to do a validation Greater than 01/01/2007 but I can not do
multiple validations on the same cell.
How can I validate P is a date also

mutiple Validations for cells
 

excel(vba) does not have a isdate function, (how strange)

"JLatham" wrote:


VB has an ISDate() function available in the language. Can you not use that
somehow; either in a test before your .Add Type:= statement or create a
Function like
Function IsADate(anyDate As Range)
IsADate = False
If IsDate(anyDate.Value) Then
IsADate = True
End If
End Function

and call it with a
IsADate(Worksheets("sheetname").Range("P2"))
type of statement

So, it takes on the form of
=IF(IsADate(Worksheets("sheetname").Range("P2"))," true action here","false
action here")
in VB. You could use it on a worksheet as simply =IsADate(P2)

Hope this helps some.

"ljhopkins_LOST and Cant find My Way" wrote:

--------------------------------------------------------------------------------

I have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a
date and Col T is a text(with list validation)
I have validation that if if T = "As is" and P < I or K (which ever one has
a value) then Error.
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as
is"",(IF(P2=(IF(K2<"""",K2,I2)),TRUE, FALSE)), TRUE)"

But if the user changes T to some other value then it is ok to change P, But
they could put any value in that field. I Need to validate that P is a DATE
also
I was trying to do a validation Greater than 01/01/2007 but I can not do
multiple validations on the same cell.
How can I validate P is a date also

mutiple Validations for cells
 

It does for me.

You may want to look again.

ljhopkins_LOST and Cant find My Way wrote:

excel(vba) does not have a isdate function, (how strange)

"JLatham" wrote:


VB has an ISDate() function available in the language. Can you not use that
somehow; either in a test before your .Add Type:= statement or create a
Function like
Function IsADate(anyDate As Range)
IsADate = False
If IsDate(anyDate.Value) Then
IsADate = True
End If
End Function

and call it with a
IsADate(Worksheets("sheetname").Range("P2"))
type of statement

So, it takes on the form of
=IF(IsADate(Worksheets("sheetname").Range("P2"))," true action here","false
action here")
in VB. You could use it on a worksheet as simply =IsADate(P2)

Hope this helps some.

"ljhopkins_LOST and Cant find My Way" wrote:

--------------------------------------------------------------------------------

I have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a
date and Col T is a text(with list validation)
I have validation that if if T = "As is" and P < I or K (which ever one has
a value) then Error.
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as
is"",(IF(P2=(IF(K2<"""",K2,I2)),TRUE, FALSE)), TRUE)"

But if the user changes T to some other value then it is ok to change P, But
they could put any value in that field. I Need to validate that P is a DATE
also
I was trying to do a validation Greater than 01/01/2007 but I can not do
multiple validations on the same cell.
How can I validate P is a date also

--

Dave Peterson

mutiple Validations for cells
 

I double checked - it is there as part of the basic VB engine - doesn't even
require the Analysis Pack add-in to be set up or anything. Try this code

Sub testIsDate()

Debug.Print IsDate("1/1/2007")

End Sub

Make sure the Immediate window is visible in the VB editor, click on the
line of code and hit [F5]. You should see TRUE show up in the Immediate
window. If you get a run-time error, then the only thing I can say is that
your copy of Excel is busticated.


"ljhopkins_LOST and Cant find My Way" wrote:

excel(vba) does not have a isdate function, (how strange)

"JLatham" wrote:


VB has an ISDate() function available in the language. Can you not use that
somehow; either in a test before your .Add Type:= statement or create a
Function like
Function IsADate(anyDate As Range)
IsADate = False
If IsDate(anyDate.Value) Then
IsADate = True
End If
End Function

and call it with a
IsADate(Worksheets("sheetname").Range("P2"))
type of statement

So, it takes on the form of
=IF(IsADate(Worksheets("sheetname").Range("P2"))," true action here","false
action here")
in VB. You could use it on a worksheet as simply =IsADate(P2)

Hope this helps some.

"ljhopkins_LOST and Cant find My Way" wrote:

--------------------------------------------------------------------------------

I have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a
date and Col T is a text(with list validation)
I have validation that if if T = "As is" and P < I or K (which ever one has
a value) then Error.
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as
is"",(IF(P2=(IF(K2<"""",K2,I2)),TRUE, FALSE)), TRUE)"

But if the user changes T to some other value then it is ok to change P, But
they could put any value in that field. I Need to validate that P is a DATE
also
I was trying to do a validation Greater than 01/01/2007 but I can not do
multiple validations on the same cell.
How can I validate P is a date also