Using VBA Workbook_SheetChange only works in one direction-CodePudding

I am trying to use the Workbook_SheetChange feature of excel. I want to have multiple worksheets feed information into a master worksheet, and if you update a cell in a source sheet, the corresponding cell in the master sheet will also change, and vice versa.

Currently, I am starting small with just trying to get this thing to work with one cell so I can build on this. The code I have works, but only in one direction; when I edit something in the source sheet, the cell value in the master sheet changes to that new value. However, when I try to change the value in the master sheet, the value in the master sheet kinda bounces around until it finally decides to stick to the value derived from the source sheet. This only occurs whenever I try to have the cell portion of the address be the same between the two sheets; if the target cell in Sheet1 is $A$1 and the target cell in Sheet2 is any cell that is not $A$1, then there are no issues. This issue only occurs if the cell in both sheets is the same.

Below is the code that I am currently using.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    cell_1 = Worksheets("Sheet1").Range("$R$3").Address
    cell_2 = Worksheets("Sheet2").Range("$R$3").Address

    If Target.Address = cell_1 Or Target.Address = cell_2 Then
        Call cellUpdate(Target.Address)
    End If

End Sub

Sub cellUpdate(Target As String)
    cell_1 = Worksheets("Sheet1").Range("$R$3").Address
    cell_2 = Worksheets("Sheet2").Range("$R$3").Address

    Application.EnableEvents = False
        With ActiveWorkbook
            If Target = cell_1 Then
                Worksheets("Sheet2").Range("$R$3").Value = Worksheets("Sheet1").Range(Target)
            ElseIf Target = cell_2 Then
                Worksheets("Sheet1").Range("$R$3").Value = Worksheets("Sheet2").Range(Target)
            End If
        End With
    Application.EnableEvents = True
End Sub

How do I get around this issue? I couldn't find any information online on how to avoid this since the uses I found for this Workbook_SheetChange function are for things that occur in one sheet rather than multiple sheets.

CodePudding user response：

What is happening here is that you extensively use Target.Address. The issue with this is that Target.Address only returns the cell address, not the sheet it is on. For example it would return $A$1. Not Sheet1!$A$1. This means that in your if statement it tests whether "$A$1" = "$A$1" regardless of which sheet this address is on. Therefore it will only ever run the first clause of this loop resulting in it only working one way.

Secondly, you have a lot of redundant code, hard-coding a bunch of addresses multiple times. This can be massively simplified as demonstrated below:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell_1, cell_2 As Range
    
    Set cell_1 = Worksheets("Sheet1").Range("$R$3")
    Set cell_2 = Worksheets("Sheet2").Range("$R$3")

Application.EnableEvents = False
    If Target = cell_1 Then
        cell_2.Value = cell_1.Value
    ElseIf Target = cell_2 Then
        cell_1.Value = cell_2.Value
    End If
Application.EnableEvents = True
End Sub

This code uses the first and second cell as range which stores the entire cell in memory, address, values, the lot. It then switches off EnableEvents as you did (good effort by the way, to prevent yourself from getting stuck in an infinite loop as most people would with this kind of code). Then it checks whether your target cell is cell 1 and switches the value of cell two with cell one, and the same for cell 2. No need for a separate function.

CodePudding user response：

Couple of observations:
Worksheets("Sheet1").Range("$R$3").Address will always return the string $R$3, as will Worksheets("Sheet2").Range("$R$3").Address.
Both lines return exactly the same thing - it doesn't care what sheet it's on.

The Call statement isn't required.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Application.EnableEvents = False
    'Address is R3 on whichever sheet you're changing.
    If Target.Address = "$R$3" Then
        'Figure out which sheet was changed and update as required.
        Select Case Sh.Name
            Case "Sheet1"
                Worksheets("Sheet2").Range("R3") = Sh.Range("R3")
            Case "Sheet2"
                Worksheets("Sheet1").Range("R3") = Sh.Range("R3")
            Case "Sheet3", "Sheet4"
                'Do stuff if you're on sheet 3 or sheet 4.
            Case Else
                'Do stuff if you're on any other sheet.
        End Select
    End If
    Application.EnableEvents = True

End Sub