Data Validation list value update after data list edit.

How to get my drop down data value to update if the value on the data validation list got edited ? kindly refer to image for clarification. Thank you.

excel

Formulas and Functions

office 365

HansVogelaar

Feb 18, 2023

HanyBaghdady

This requires VBA code, with some side effects:

Users will have to allow macros.
It won't work in Excel Online, nor on iOS or Android.
If you change a cell in the list, Undo will be disabled.
After changing a cell in the list, Excel will jump back to that cell.

In the attached demo workbook, I have named the source range List, and I have selected all cells that have data validation based on this range, and assigned that range the name DVCells.

I right-clicked the sheet tab and selected 'View Code'.

I copied the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Src As Range
    Dim OldVal, NewVal
    If Intersect(Range("List"), Target) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Intersect(Range("List"), Target).Count > 1 Then
        MsgBox "Do not change multiple cells in the list at once!", vbCritical
        Application.Undo
        GoTo ExitHere
    End If
    Set src=Intersect(Range("List"), Target)
    NewVal = Src.Value
    Application.Undo
    OldVal = Src.Value
    Src.Value = NewVal
    If OldVal <> "" Then
        Range("DVCells").Replace What:=OldVal, Replacement:=NewVal, LookAt:=xlWhole
    End If
ExitHere:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I switched back to Excel and saved the workbook as a macro-enabled workbook (*.xlsm).

Demo.xlsm17 KB

Gtuggle

Copper Contributor

Feb 08, 2024

HansVogelaar

This works great and what I was looking for also.

One question how would I do this with other lists in the same worksheet?

I have multiple that I would like to use this same solution.

Any help would be greatly appreciated.

HansVogelaar

MVP

Feb 08, 2024

Gtuggle

Using the same setup as in the earlier posts in thus discussion, create named ranges List1, List2, ... for the different sources of data validation drop-downs.

And on the target sheet ("EMPTY" in this discussion), create named ranges DVCells1, DVCells2, ... for the ranges using these different list sources.

The code can then look like this (please text carefully):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Src As Range
    Dim OldVal, NewVal
    Dim arrLists, arrCells
    Dim i As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    arrLists = Array("List1", "List2", "List3")
    arrCells = Array("DVCell1", "DVCell2", "DVCells3")
    For i = 0 To UBound(arrLists)
        If Not Intersect(Range(arrLists(i)), Target) Is Nothing Then
            If Intersect(Range(arrLists(i)), Target).Count > 1 Then
                MsgBox "Do not change multiple cells in the list at once!", vbCritical
                Application.Undo
            Else
                Set src=Intersect(Range(arrLists(i)), Target)
                NewVal = Src.Value
                Application.Undo
                OldVal = Src.Value
                Src.Value = NewVal
                If OldVal <> "" Then
                    Worksheets("EMPTY").Range(arrCells(i)).Replace _
                        What:=OldVal, Replacement:=NewVal, LookAt:=xlWhole
                End If
            End If
        End If
    Next i
ExitHere:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Gtuggle
Copper Contributor
Feb 09, 2024
HansVogelaar This worked great, thanks for the help

Forum Discussion

Data Validation list value update after data list edit.

Resources