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.

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

"(E-Mail Removed)" 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.
>
>

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

"(E-Mail Removed)" 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.
>
>

On Jul 5, 8:23*pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "ronia...@hotmail.com" 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.- Hide quoted text -
>
> - 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!

Hi,

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

Mike


"(E-Mail Removed)" wrote:

> On Jul 5, 8:23 pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> > 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
> >
> >
> >
> > "ronia...@hotmail.com" 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.- Hide quoted text -
> >
> > - 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!
>

On Jul 5, 10:45*pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> Hi,
>
> Instead of
> If Target.Address = "$D$1" Then
> use
> If Not Intersect(Target, Range("D")) Is Nothing Then
>
> Mike
>
>
>
> "ronia...@hotmail.com" wrote:
> > On Jul 5, 8:23 pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> > > 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
>
> > > "ronia...@hotmail.com" 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 = vbYesThen
>
> > > > 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.- Hide quoted text -
>
> > > - 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!- Hide quoted text -
>
> - Show quoted text -

Hi Mike,
Thanks again! That worked!