Mirror 2 cells

[Berry1904]

Hi all,

I am making a from, but I am strugling with something.
The idea is;
I have 2 cells:
- Cell A1 of Tab1
- Cell B1 of Tab2.
Both values should be the same.
So if I wirte something in Cell A1 it should apear in B1
But if I write somthing in B1, it should be the same in A1

Ofc. I tried some things:
1.) the simple formule:
In Cell A1 =B1
In Cell B1 =A1
But it only works 1x, then the formule is overwritten.

2.) (I found on internet)
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Range("A1"), Target) Is Nothing Then
Range("B1") = Range("A1")
End If
If Not Intersect(Range("B1"), Target) Is Nothing Then
Range("A1") = Range("B1")
End If
End Sub

But when I do thing, Excel closes down.....so something is not correct there

Anybody got an idea?
Thank you

 

[dreid1011]

It may help to add the following lines to the VBA code:
Application.EnableEvents = False
Application.EnableEvents = True

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Not Intersect(Range("A1"), Target) Is Nothing Then
Range("B1") = Range("A1")
End If
If Not Intersect(Range("B1"), Target) Is Nothing Then
Range("A1") = Range("B1")
End If
Application.EnableEvents = True
End Sub

Without turning off events, the code essentially creates and endless loop and Excel was shutting down because it got stuck looping back and forth constantly updating the other cell when one updated, and the code updating each one is sufficient to trigger the code again, and again, and again...

And as you learned with the formula approach, a cell can contain only a value or a formula, not both. As soon as you manually enter a value into a cell, if there was previously a formula, it would be erased.

 

[Berry1904]

What an incredible smart solution!
My knowledge is not great enough to thought about this.
It works, thank you!

 

[dreid1011]

You're welcome, and happy to help.

 

[Berry1904]

You're welcome, and happy to help.

Really sorry to bother you ones more....but what if A1 is in the Tab "Test1" and B1 is in the Tab "Test2" ?

Happy to learn.
Thanks a million.

 

[Berry1904]

Really sorry to bother you ones more....but what if A1 is in the Tab "Test1" and B1 is in the Tab "Test2" ?

Happy to learn.
Thanks a million.

And last but not least, where to put the VBA so it triggers automaticly?
Now I wrote the code in the tab it is referring to, but what if it is referring to multiple tabs, like in this case.

Sorry....

 

[dreid1011]

Really sorry to bother you ones more....but what if A1 is in the Tab "Test1" and B1 is in the Tab "Test2" ?

Happy to learn.
Thanks a million.

You will have to split the code between the two sheets then. Maybe like this:

Test1 - place code in sheet module for Test1

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Not Intersect(Range("A1"), Target) Is Nothing Then
Sheets("Test2").Range("B1") = Range("A1")
End If
Application.EnableEvents = True
End Sub

Test2 - place code in sheet module for Test2

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Not Intersect(Range("B1"), Target) Is Nothing Then
Sheets("Test1").Range("A1") = Range("B1")
End If
Application.EnableEvents = True
End Sub

 

[dreid1011]

And last but not least, where to put the VBA so it triggers automaticly?
Now I wrote the code in the tab it is referring to, but what if it is referring to multiple tabs, like in this case.

Sorry....

Any type of event code (worksheet_change and others) has to be placed in the sheet module where the originating event will take place.

 

[Berry1904]

It works!
You don't know halve how happy you made me.

Thnx!