I have a worksheet_change macro to place the date and time in adjacent cells of the target column(which is the "B" column). I now want to put a custom validation in the "F" column of the same row. How can I work off the target to make the formula dynamic?
The custom validation formula is: =and(len(F3)=8,isnumber(value(left(f3,6))),right(f3,2)="P6"
I've tried to replace the "F" address's with something like:
len(target.address.offset(0,4)=8
and
I've tried making the "f" cell the active cell and using:
len(activeCell)=8
Both failures
Any help out there?
Thanks
Steve

EggHeadCafe - Software Developer Portal of Choice
Slipstreaming and Unattended Windows Media Creation
http://www.eggheadcafe.com/tutorials...d-unatten.aspx

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
news:(E-Mail Removed)...
>I have a worksheet_change macro to place the date and time in adjacent
>cells of the target column(which is the "B" column). I now want to put a
>custom validation in the "F" column of the same row. How can I work off the
>target to make the formula dynamic?
> The custom validation formula is:
> =and(len(F3)=8,isnumber(value(left(f3,6))),right(f3,2)="P6"
> I've tried to replace the "F" address's with something like:
> len(target.address.offset(0,4)=8
> and
> I've tried making the "f" cell the active cell and using:
> len(activeCell)=8
> Both failures
> Any help out there?
> Thanks
> Steve
>
> EggHeadCafe - Software Developer Portal of Choice
> Slipstreaming and Unattended Windows Media Creation
> http://www.eggheadcafe.com/tutorials...d-unatten.aspx

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 Stev

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
Pe

"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

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
news:(E-Mail Removed)...
> 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