Forum Discussion
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.
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 SubI switched back to Excel and saved the workbook as a macro-enabled workbook (*.xlsm).
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 SubI switched back to Excel and saved the workbook as a macro-enabled workbook (*.xlsm).
I applied the source code to the attached excel file, but it does not seem to work.
You will find the "List" on a sheet named "Settings" and the "DVCells" on the sheet named "Empty".
HansVogelaar kindly see where I went wrong. I suspect it has to do with the range being located on a different sheet. Thank you.
Why are there empty cells in the Group and Type columns on the Setting sheet?
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.
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
Hi , your suggestions are great.
I'm new to VBA .
I can get your demo working if all the lists are on one worksheet.
How does one adopt it to cover the situation where:
- the list is on one sheet
- the DV values are on multiple other sheets?
I have tried placing your code on:
- the worksheet containg the list
- all worksheets
- the workbook
I'm sure it's a newbie question but your help would be appreciated.
Regards
The code should now be in the worksheet module of each of the sheets containing the lists, NOT in the module of the sheet containing the data validation.
And you have to refer to the data validation ranges in such a way that the code knows where to find them.
In the attached workbook, I created a common procedure named UpdateDV in a standard module. This is called from the Worksheet_Change event procedures of each of the list sheets, to avoid having to write almost identical code for each sheet.
See Formulas > Name Manager for the definition of the named ranges I used, and see the Visual Basic Editor for the code.
