Add validation using sheet code

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

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.
 
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,
Click to expand...
AlertStyle:=xlValidAlertStop, _
 
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.
Click to expand...

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
 
Back
Top