Automatically update data validation list
 

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.

Automatically update data validation list
 

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.

Automatically update data validation list
 

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

" 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.

Automatically update data validation list
 

On Jul 5, 8:23*pm, Mike H 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").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("MyN*ames").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.- 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!

Automatically update data validation list
 

Hi,

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

Mike


" wrote:

On Jul 5, 8:23 pm, Mike H 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").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("MyNÂ*ames").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.- 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!

Automatically update data validation list
 

On Jul 5, 10:45*pm, Mike H wrote:
Hi,

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

Mike



" wrote:
On Jul 5, 8:23 pm, Mike H 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").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("MyN**ames").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.- 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!