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(f 3,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
...
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(f 3,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 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

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