Using VBA Workbook SheetChange only works in one direction

0 votes

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.

Jan 20 in Others by Kithuzzz
• 27,740 points
34 views

1 answer to this question.

0 votes

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.

answered Jan 20 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
0 answers

I Want To Print 1 to 100 Numbers Using Arrays In Javascript Only

Please bear with me as I am ...READ MORE

Aug 18, 2022 in Others by krishna
• 2,820 points
410 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 51,240 points
222 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,240 points
254 views
0 votes
1 answer

Deleting Empty rows in Excel using VBA

On Error Resume Next worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 When ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 51,240 points
158 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
274 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9 in Others by narikkadan
• 51,240 points
32 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,240 points
182 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP