Data validation list - automatically update cells where used

This issue was somewhat addressed in this thread, but I have a slight variation and was hoping someone could help me figure it out.

On Sheet2, data validation is applied to cells D21:D70 to limit data to values to a list, which is a named range on another worksheet. There are ranges on two other worksheets with the same data validation rule. The range for the list is ‘Sheet1’!D12:D26 (which is named “Processes”).

On Sheet1, the user enters a list of their processes. Then on Sheet2 (and the other sheets where the data validation is applied), they select one of the processes from the list. This all works fine until they go back to Sheet1 and modify one of the previously entered values.

For example, "Brush Teeth" is entered as one of the processes on Sheet1. It is then selected on Sheet2. Then, the user goes back to Sheet1 and changes the value to "Brush Hair." When you return to Sheet2, the value is still "Brush Teeth." It can be manually be changed by selecting “Brush Hair” from the drop-down list (“Brush Teeth” is no longer available), but it doesn’t automatically update.

In the other thread, the following code was suggested, but the scenario was a bit simpler. Data validation was applied to a single cell (A3) and the list was on the same worksheet (B1:B3).

Option Explicit

Private oldValue As String
Private Const DVCell As String = "A1"
Private Const dataList As String = "B1:B10"

Private Sub Worksheet_Change(ByVal Target As Range)

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

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
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


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

On Sheet2 (I assume I need to add this code to every applicable sheet), I tried editing the first part of the code as follows (changes in bold), but when I click on any cell on Sheet2 that has the data validation rule applied, VB returns the error “Run-time error ‘1004’: Method ‘Range” of object ‘_Worksheet’ failed.” If I go into the debugger, the second instance of “If Not Intersect(Target, Range(dataList)) Is Nothing Then” is highlighted.

Please Login or Register  to view this content.

Any ideas/suggestions? My VB skills are on the beginner side, so please bear with me!

Thanks!

but it doesn’t automatically update.

But how do you know what to update it to ? For example let's say you "insert" a value which changes each value position and then after the insert you see a typo in the old value so you change that ... Since the old value no longer exists how does the code determine a typo change versus an insert and an overwrite ?

I'm think'n the safer/easier way to go is to draw the attention of the user to the Valdiation cell and have them fix it manaully. For example you could use Conditional Formatting to turn the Validation cell bright yellow when the value displayed no longer exists in the Source List.

JB... in the solution code that you provided, will excel update the range in the code if I inserted new rows between D21:D70? or one would have to update the VB source?

Thank you

Sorry for the delayed response. Thanks for all the suggestions. I haven't yet tried the code JB suggests, but I think it might work. The user cannot insert rows, but nimrod still has a good point as I just discovered another problem. If the user inserts a value in the list, then selects it on one of the other worksheets, then later goes back and deletes it, that's causing a whole new set of problems as on the other sheets, they select the value from the list and then fill out info in the other columns related to their selection. I think I've created a monster with this thing!

Of course right after I spent months creating this and started rolling out, our company implemented SharePoint, which I think is probably a better tool for this project. Unfortunately, I'm somewhat new to that, too and didn't have time to start over. Ugh!

Thank you very much. This post helps me to solve my problem.

But i face one final issue.when i reopen my worksheet the vba code doesn't exists and again i have to paste the code[which you have give above] then compile to make it work again.

Is there any way to make this run every time when i reopen the worksheet? and also i want the vba code to exit

Are you saving the file as macro-enabled (*.xlsm)? If you don't, it will strip out all VB code (and Macros).