Hi,

When the range is on another sheet you must refer to the sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "$D$1" Then
If IsEmpty(Target) Then Exit Sub
If
WorksheetFunction.CountIf(Worksheets("Sheet2").Ran ge("MyNames"), Target) = 0
Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo +
vbQuestion)
If lReply = vbYes Then

Worksheets("Sheet2").Range("MyNames").Cells(Worksh eets("Sheet2").Range("MyNames").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub


Mike

" wrote:

Hello,

I need some help with setting up a data validation list that
automatically updates the data lsit. I am using the instructions and
VB code from this website: http://www.ozgrid.com/Excel/excel-va...ist-update.htm

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "$D$1" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("MyNames"), Target)
= 0 Then
lReply = MsgBox("Add " & Target & " to list",
vbYesNo + vbQuestion)
If lReply = vbYes Then

Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub

However, this code only works when the data list and the data
validation list are on the same worksheet. Can you help me adjust this
code so that the data list is on a seperate worksheet to the data
validation list.

Thanks for your help. My understanding of VB is very basic.