Sheet Event?

[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

 

[Dave Peterson]

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????).

 

[Gareth]

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

 

[Dave Peterson]

Oh, cripe. I forgot about this (take from Debra Dalgleish's site:
http://www.contextures.com/xlDataVal08.html#Change)

In Excel 97, selecting an item from a Data Validation dropdown list
does not trigger a change event, unless the list items have been typed in
the Data Validation dialog box. In these versions, you can add a button
to the worksheet, and assign a macro to the button.


Can you type your list in the dialog box?

Debra has a workaround that involves clicking a button to run the code after
you've selected a value from the cell.

(Maybe it's time to upgrade or to rethink the data|validation and put it all in
code and use comboboxes????)