Synchronize Drop Down Lists

[shellp]

Hello

I have 3 worksheets with the same drop down lists but I want them to sync so that if "blue" is selected on worksheet 2 drop down then the other two worksheets also change to "blue" as well. Or the user can change worksheet 3 and then worksheet 1 and 2 change.

Thanks.

 

[Norie]

How did you create these drop down lists?

 

[shellp]

The drop down lists are created by using data validation, list, and a named range of color_names.

In working on this I used the following code:

Private Sub Worksheet_Change(ByVal Target as Range)
Dim tSheet1 as worksheet

If not intersect(Target, Range("D2")) is nothing then
set tSheet1 = activeworkbook.Worksheets("Global_Trending")
tSheet1.Range("D2").value=Target.value
End If
End Sub

This works if I put in one worksheet but if have this code in two worksheets i.e. if I am in the global_trending worksheet and change the drop down it will change the drop down value on FC_Global_Trending worksheet or vice versa I get the error message "not enough system resources to display completely." Why is that? Thanks.

 

[Norie]

Not sure why you get that error but whenever you have event code that changes values you should temporarily disable events, see below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tSheet1 As Worksheet

    If Not Intersect(Target, Range("D2")) Is Nothing Then
        Application.EnableEvents = False
        Set tSheet1 = ActiveWorkbook.Worksheets("Global_Trending")
        tSheet1.Range("D2").Value = Target.Value
        Application.EnableEvents = True
    End If
    
End Sub

 

[shellp]

THANK YOU. The addition of application.enableevents=false/true fixed the problem so all is working now. Spent lots of time on this so greatly, greatly appreciated.