Data validation list - automatically update cells where used

[raeganhill]

I used the exact code mentioned in comment #1 above this thread, but it won't run. I had it working once, then made too many changes so I deleted everything and started from scratch. It's under the Worksheet as instructed. Here is my worksheet: Book1.xlsm

What am I missing?

Raegan

[djdjdj]

Hi,

I think I've managed to figure out a solution. I'm sure that it's not the cleanest way of solving it, but it seems to work nevertheless:

Option Explicit

Private oldValue As String
Private Const DVCell As String = "A1"
Private dataList As String
Private lrow As Long

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
lrow = Cells(Rows.Count, "b").End(xlUp).Row
dataList = "b1:b" & lrow

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Count = 1 Then
If Not Intersect(Target, Range(dataList)) Is Nothing Then
With Target
If Range(DVCell).Value = oldValue Then
Range(DVCell).Value = .Value
End If
End With
End If
End If

    ActiveWorkbook.Names.Add Name:="list", RefersToR1C1:="=Sheet1!R1C2:R" & Cells(Rows.Count, "b").End(xlUp).Row & "C2"
    Range("A1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=list"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    For Each cell In Range(dataList)
        If cell.Value = Range(DVCell).Value Then
            GoTo ws_exit
        Else
        End If
    Next cell
    
    Range(DVCell) = Range("b" & lrow).Value

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

lrow = Cells(Rows.Count, "b").End(xlUp).Row
dataList = "b1:b" & lrow

If Target.Count = 1 Then
If Not Intersect(Target, Range(dataList)) Is Nothing Then
If Not IsEmpty(Target) Then
oldValue = Target.Value
End If
End If
End If
End Sub