[VBA EXPERTS NEEDED] Mirroring columns to two different sheets

[gomgom2]

I just created a thread but seems its gone.. so re-writing.

What I am trying to do is make a range which will be mirrored in two different sheets.

That is, they accept input which will be reflected in either of the range I specify.

For example, if I put 123123 on Range1, it will be shown the same on range 2.

Also, When I change to 11111 in rage2, it will automatically update range1.

For Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range
    Set r1 = Sheet1.Range("Range1")
    Set r2 = Sheet2.Range("Range2")
    If Intersect(Target, r1) Is Nothing Then Exit Sub
        
        r2.Value = r1.Value
    
End Sub

For Sheet2,

To avoid the loop

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range
    Dim c As Range
    Set r1 = Sheet2.Range("vslname2")
    Set r2 = Sheet1.Range("vslname1")


    If Intersect(Target, r1) Is Nothing Then Exit Sub
    If r2.value<>r1.value then
        r2.Value = r1.Value

    End If
End Sub

Please help!

 

[FormR]

Hi, welcome to the forum!

You didn't tell us what you are having problems with, but to avoid going into a loop you would temporarily disable events; one way of re-writing your code to handle that could be something like.

Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Sheet1.Range("Range1")
Set r2 = Sheet2.Range("Range2")
If Not Intersect(Target, r1) Is Nothing Then
  Application.EnableEvents = False
  r2.Value = r1.Value
  Application.EnableEvents = True
End If
End Sub

Sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Sheet1.Range("Range1")
Set r2 = Sheet2.Range("Range2")
If Not Intersect(Target, r2) Is Nothing Then
  Application.EnableEvents = False
  r1.Value = r2.Value
  Application.EnableEvents = True
End If
End Sub

 

[Johnny C]

A similar thread which might add some info...
https://www.mrexcel.com/forum/excel-questions/1028420-can-i-edit-cell-between-multiple-sheets-different-locations.html

 

[gomgom2]

Thank you very much for the replies. This site is just amazing. I wasn't even expecting a feedback.

May be I was too disappointed after my first writing being deleted. Let me write it in more details.

1. What I am trying to do is linking both ranges so that I can work on either of the sheets.

2. I cannot afford disabling events, since I already have other codes that will be affected by this.

3. Special Thanks to Johnny C, I have looked into all the replies you made earlier in the previous thread. But somehow it is whole another level making it to the range rather than just one destined cell.

Please help make this work....... thank you.

 

[FormR]

2. I cannot afford disabling events, since I already have other codes that will be affected by this.

Hi, this is temporary and only occurs whilst the sheets are updated - events are immediately turned back on automatically within the code.

 

[Fluff]

@gomgom2

May be I was too disappointed after my first writing being deleted. Let me write it in more details.

Your original post was not deleted, it simply required moderator approval & you should have received a message to that affect. In future please wait for posts to be approved, rather than re-posting.
Cheers

 

[gomgom2]

Hi, this is temporary and only occurs whilst the sheets are updated - events are immediately turned back on automatically within the code.

I do understand it is temporary. However, I was wondering if there is any other way to make it work rather than disabling and enabling again.

To be more specific, I have made a switch to turn on and off the event to be able to use some features selectively. By using this code, it will always be turned on...

 

[FormR]

Hi, you posted some code in post # 1, but you haven't told us in what way it doesn't work - maybe that would be a good starting place?

I have made a switch to turn on and off the event to be able to use some features selectively. By using this code, it will always be turned on...

If events have been disabled by your switch then the change event won't fire and it won't turn them back on again