| Home |
| Search |
| Today's Posts |
|
#5
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! |
| 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) | |||
Threaded Mode
