Sheet Event?

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

Gareth

I have 5 columns on a sheet, the first has a data validation list. I want
the same list to be added to the second column when an entry has been made
in the first, etc, etc. The columns are P to T.

Also, if the user enters something in the first column and the data
validation is added to the second, the user then deletes what he entered in
the first column I want the validation in the second column to be deleted.

Thanks in advance.

Gareth
 
This worked ok for me in xl2002.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'enter something in P:S to be copied to Q:T????
If Intersect(Target, Range("P:S")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo errHandler:
If IsEmpty(Target) Then
Target.Offset(0, 1).Validation.Delete
Else
Application.EnableEvents = False
Target.Copy
Target.Offset(0, 1).PasteSpecial Paste:=xlPasteValidation
Application.CutCopyMode = False
Target.Select
End If

errHandler:
Application.EnableEvents = True

End Sub

But before you try it, try copying a cell and then Edit|Paste Special. Look to
see if there's an option for Validation. I don't recall when this was added
(xl2k????).
 
work only have '97 and it doesn't appear to work. 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