Automatically update data validation list

[roniaelm]

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-validation-list-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.

 

[Mike H]

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").Range("MyNames"), Target) = 0
Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo +
vbQuestion)
If lReply = vbYes Then

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


Mike

 

[Mike H]

Hi,

You may also want to consider adding these 2 lines as the last 2 of your
subroutine. As data are added to the validation list these lines will re-size
the named range to ensure new data are added to the bottom each time

Set rng = Worksheets("Sheet2").Range("MyNames")
rng.Resize(rng.Rows.Count + 1).Name = "MyNames"

Mike

 

[roniaelm]

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").Range("MyNames"), Target) = 0
Then
                    lReply = MsgBox("Add " & Target& " to list", vbYesNo +
vbQuestion)
                        If lReply = vbYes Then

Worksheets("Sheet2").Range("MyNames").Cells(Worksheets("Sheet2").Range("MyN­ames").Rows.Count + 1, 1) = Target
                        End If
                End If
        End If
End Sub

Mike












- Show quoted text -

Hi Mike,

Thanks for your help it worked!!
Just one more thing, do you know how I can extend the target cells to
an entire column as I have data validation set for the whole column
and not just the single cell.

Thanks again!

 

[Mike H]

Hi,

Instead of
If Target.Address = "$D$1" Then
use
If Not Intersect(Target, Range("D")) Is Nothing Then

Mike

 

[roniaelm]

Hi,

Instead of
If Target.Address = "$D$1" Then
use
If Not Intersect(Target, Range("D")) Is Nothing Then

Mike








- Show quoted text -

Hi Mike,
Thanks again! That worked!