At the moment columns P:T all have the same data
validation list applied to them.

Users have been told to start in colum P but this does not
always happen. So I have decided to apply the list only
to column P, Q:T are to be applied with a text lenght
validation (text lenght = 0, and error message telling
them to enter in column P).

This all works fine, what I cannot quite get to work is
the sheet code telling Excel to add the validation list to
column Q when an entry has been made in column P (and so
on until column T has the list)

Hope someone can point me in the right direction.

Gareth

Suppose the validation list is in O1:O4. Then it seems something
like the following (in the worksheet's code module) will do the job.
If you want it to apply to more rows, change the IF condition.

HTH,
Merjet

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column 15 And Target.Column < 20 Then
With Target.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$O$1:$O$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub

Hi Gareth:

This should give you some ideas:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("P")) Is Nothing Then
If IsEmpty(Target) Then
With Target.Offset(0, 1).Resize(1, 4).Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:=0
.ErrorMessage = "Please input in column P first!"
.ShowInput = False
.ShowError = True
End With
Else
With Target.Offset(0, 1).Resize(1, 4).Validation
.Delete
'Your regular validation parameters here
End With
End If
End If
End Sub

Regards,

Vasant.

"Gareth" wrote in message
...
At the moment columns P:T all have the same data
validation list applied to them.

Users have been told to start in colum P but this does not
always happen. So I have decided to apply the list only
to column P, Q:T are to be applied with a text lenght
validation (text lenght = 0, and error message telling
them to enter in column P).

This all works fine, what I cannot quite get to work is
the sheet code telling Excel to add the validation list to
column Q when an entry has been made in column P (and so
on until column T has the list)

Hope someone can point me in the right direction.

Gareth

thanks for this but I can't get it to work. if an entry
is made in any cell in column P I want .offset(0,1) to be
given the validaion in column P

For example an entry is made in P26, I want Q26 to be
given the validation list, also if a subsequent entry is
made in Q26 I then want R26 to get the validation.

I hope this makes sense.

Gareth
-----Original Message-----
Suppose the validation list is in O1:O4. Then it seems
something
like the following (in the worksheet's code module) will
do the job.
If you want it to apply to more rows, change the IF
condition.

HTH,
Merjet

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column 15 And
Target.Column < 20 Then
With Target.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$O$1:$O$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub


.

For example an entry is made in P26, I want Q26 to be
given the validation list, also if a subsequent entry is
made in Q26 I then want R26 to get the validation.

That's what the code I posted (if you modified it for more
rows) does, unless we're misunderstanding one another.

Merjet

Thanks for both replies, after a long struggle I found out why it didn't
work.

We only have Excel '97 in work, when I tried it at home on 2000 it worked
fine.

If you manually enter an item from the list it works but if you select it
from the dropdown it doesn't.

Is there a way around this?

Gareth

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Hi Gareth:

This should give you some ideas:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("P")) Is Nothing Then
If IsEmpty(Target) Then
With Target.Offset(0, 1).Resize(1, 4).Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:=0
.ErrorMessage = "Please input in column P first!"
.ShowInput = False
.ShowError = True
End With
Else
With Target.Offset(0, 1).Resize(1, 4).Validation
.Delete
'Your regular validation parameters here
End With
End If
End If
End Sub

Regards,

Vasant.

"Gareth" wrote in message
...
At the moment columns P:T all have the same data
validation list applied to them.

Users have been told to start in colum P but this does not
always happen. So I have decided to apply the list only
to column P, Q:T are to be applied with a text lenght
validation (text lenght = 0, and error message telling
them to enter in column P).

This all works fine, what I cannot quite get to work is
the sheet code telling Excel to add the validation list to
column Q when an entry has been made in column P (and so
on until column T has the list)

Hope someone can point me in the right direction.

Gareth

See one more guess at your first post. (not too encouraging, though.)

Gareth wrote:

Thanks for both replies, after a long struggle I found out why it didn't
work.

We only have Excel '97 in work, when I tried it at home on 2000 it worked
fine.

If you manually enter an item from the list it works but if you select it
from the dropdown it doesn't.

Is there a way around this?

Gareth

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Hi Gareth:

This should give you some ideas:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("P")) Is Nothing Then
If IsEmpty(Target) Then
With Target.Offset(0, 1).Resize(1, 4).Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:=0
.ErrorMessage = "Please input in column P first!"
.ShowInput = False
.ShowError = True
End With
Else
With Target.Offset(0, 1).Resize(1, 4).Validation
.Delete
'Your regular validation parameters here
End With
End If
End If
End Sub

Regards,

Vasant.

"Gareth" wrote in message
...
At the moment columns P:T all have the same data
validation list applied to them.

Users have been told to start in colum P but this does not
always happen. So I have decided to apply the list only
to column P, Q:T are to be applied with a text lenght
validation (text lenght = 0, and error message telling
them to enter in column P).

This all works fine, what I cannot quite get to work is
the sheet code telling Excel to add the validation list to
column Q when an entry has been made in column P (and so
on until column T has the list)

Hope someone can point me in the right direction.

Gareth



--

Dave Peterson