| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation lists update orginal cell with list update | Excel Worksheet Functions | |||
| How to automatically show list in a validation list? | Excel Programming | |||
| Help required with VBA program to automatically enter data from a validation list. | Excel Programming | |||
| Update Validation List | Excel Discussion (Misc queries) | |||
| update data validation list with new entries?? | Excel Discussion (Misc queries) | |||
Linear Mode
