Create cells mirroring each others

[Jaberdino]

Hi Huys,

Long time no see

Hope you are well!

I'm trying to write something some kind of a mirror between multiple cells.

Context: I have a dropdown of countries on five different sheets (the dropdown is the same on each sheet).
I want the user to be able to change the country on each sheet.
Of course, when the user change the country on one sheet, it should be changed on all other sheets.

So, I've been playing with the events, creating endless loops kind of crashing my excel.

For instance, in the basic case of two sheet. When inserting this for the sheet1:

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = WorkSheets(1).Range("A1:A1")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing AND Activesheet.name="Sheet1" Then
        WorkSheets(2).Range("A1:A1").value=WorkSheets(1).Range("A1:A1").value
    End If
End Sub

and an equivalent code for sheet2, then I have "not enough resource to display completely".

I can't find a way to say "if the cell change and then change the cells on the other sheets IIF you are on the active sheet". So it doesn't create endless loop, I guess.

Thanks a lot for your help

Cheers,
Jaberdino

 

[TMShucks]

You're welcome. Whilst I know about "parent(s)", I tend to forget about them (in VBA terms, that is). In the case of the Workbook Sheet Change event handler, you are passed a variable that refers to the sheet where the change took place so you can use that, if you choose, in preference to Target.Parent.

You can demonstrate this in the event handler using:

Debug.Print Target.Parent.Name, Sh.Name