Steve,

MyFormula is the formula string to be inserted in the custom validation. I
always use a variable to build formula strings, then you can use Debug.Print
MyFormula to print the formula string to the Immediate window. Now I can
verify that my formula is build as I expected...

See my example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyFormula As String
MyFormula = _
"=And(Len(F" & Target.Row & ")=8,IsNumber(Value(Left(F" & Target.Row _
& ",6))),Right(F" & Target.Row & ",2)=""" & "P6""" & ")"
Debug.Print MyFormula 'just for illustration
If Target.Column = 2 Then
Application.EnableEvents = False
Target.Offset(0, 1) = Now()
With Target.Offset(0, 4).Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=MyFormula
End With
Application.EnableEvents = True
End If
End Sub

Hopes this helps.
....
Per


"Steve Roach" skrev i meddelelsen
...
Thanks for taking the time to reply. Could you expand on your solution a
bit more.



Per Jessen wrote:

Hi SteveThis should give you the correct formula:MyFormula= _"=And(Len(F"
&
14-Oct-09

Hi Steve

This should give you the correct formula:

MyFormula= _
"=And(Len(F" & Target.Row & ")=8,IsNumber(Value(Left(F" & Target.Row &
",6))),Right(F" & Target.Row & ",2)=""" & "P6""" & ")"

Regards,
Per

"Steve Roach" skrev i meddelelsen

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET Distributed Data Applications
http://www.eggheadcafe.com/tutorials...uted-data.aspx