Using VBA Workbook SheetChange only works in one direction

Question

I'm attempting to use Excel's Workbook SheetChange function. I want different worksheets to feed data into a master worksheet such that when a cell in one source sheet is changed, the corresponding cell in the master sheet will as well.

I'm now working on getting this thing to operate with just one cell so that I may expand on it. The code I have only functions in one direction; when I make changes to a cell in the source sheet, the value of that cell in the master sheet also adjusts to reflect those changes. The value in the master sheet, however, kind of juggles around when I attempt to update it before deciding to stick with the value taken from the source sheet. 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 problems. This only happens if I try to have the cell component of the address be the same across the two sheets. Only if the cell in both sheets is the same does this problem arise.

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 can I avoid this problem? Since the uses I found for this Workbook SheetChange method are for things that occur in one sheet rather than numerous sheets, I was unable to locate any information online on how to avoid this.

Answer 1

You are making heavy use of Target.Address in this situation. Target.Address only returns the cell address in this case; it does not return the sheet that the cell is on. For instance, $A$1 would be returned. No, Sheet 1! $A$1. This indicates that regardless of which sheet this address is on, your if statement checks to see whether "$A$1" Equals "$A$1." As a result, this loop will only ever execute the first sentence, making it only work in one direction.

Second, there is a tonne of duplicate code that hard-codes a lot of addresses. This can be greatly condensed, as shown 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 keeps the entire cell—address, values, and everything else—in memory by using the first and second cells as a range. As you did, it then turns off EnableEvents (good effort by the way, to prevent yourself from getting stuck in an infinite loop as most people would with this kind of code). After that, it determines whether cell 1 is your target cell before switching cell 2's value to cell 1's value. No requirement for a distinct function.