Bi-directional (two-way) Link in Excel using cells in multiple rows and columns

[louperrotta]

As of right now I can only get it to work with one cell to one cell, not multiple but I am very much a layman...here is what I was using:

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
   If Target = Range("A1") Then
      Sheets("Sheet2").Range("B1").Value = Target.Value
   End If
End If

End Sub


Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("B1")) Is Nothing Then
  If Target = Range("B1") Then
    If Sheets("Sheet1").Range("A1").Value <> Target.Value Then
      Sheets("Sheet1").Range("A1").Value = Target.Value
    End If
  End If
End If

End Sub

So when I tried to use (A1:A20) & (B1:B20) it was error...what am I doing wrong? Hahaha, again, I'm a layman! Here:

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
   If Target = Range("A1:A20") Then
      Sheets("Sheet2").Range("B1:B20").Value = Target.Value
   End If
End If

End Sub


Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
  If Target = Range("B1:B20") Then
    If Sheets("Sheet1").Range("A1:A20").Value <> Target.Value Then
      Sheets("Sheet1").Range("A1:A20").Value = Target.Value
    End If
  End If
End If

End Sub

Thank you so much! You are all amazing!! Hope you can help!

 

[louperrotta]

Yes, the code belongs in the ThisWorkbook module.

If the code is not running, it could be because you have Application.EnableEvents = False, because the code you were using errored before resetting Application.EnableEvents = True.

In the Immediate Window, try typing Application.EnableEvents = True and then retry making changes.

If you can't see the Immediate Window in the VB Editor, select View/Immediate Window or type CTRL+G.

Huh, yeah, I put

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

Dim i As Long
Dim rng1 As Range, rng2 As Range

Application.EnableEvents = False

For i = 2 To 4 'Assumes Sheet 1 <---> Sheets 2,3,4. Extend if you wish
Set rng1 = Worksheets("Sheet1").Range("A1:C20").Offset(20 * (i - 2))
Set rng2 = Worksheets("Sheet" & i).Range("A1:C20").Offset(20 * (i - 2))
If Target.Worksheet.Name = "Sheet1" Then
If Not Intersect(Target, rng1) Is Nothing Then rng2.Value = rng1.Value
ElseIf Target.Worksheet.Name = "Sheet" & i Then
If Not Intersect(Target, rng2) Is Nothing Then rng1.Value = rng2.Value
End If
Next i

Application.EnableEvents = True

End Sub

and then Application.EnableEvents = True in the Immediate Window but still nothing...it's so weird...

 

[louperrotta]

Yes, the code belongs in the ThisWorkbook module.

If the code is not running, it could be because you have Application.EnableEvents = False, because the code you were using errored before resetting Application.EnableEvents = True.

In the Immediate Window, try typing Application.EnableEvents = True and then retry making changes.

If you can't see the Immediate Window in the VB Editor, select View/Immediate Window or type CTRL+G.

 

[StephenCrump]

I can't see any obvious problems, based on the picture you've posted.

Give this a try. It works fine for me: Links.xlsm

 

[louperrotta]

So, I downloaded the file and it has that same formula in it but when I type stuff in the A Column nothing shows up, I don't get it, so weird...I just don't understand why it is not working....

 

[louperrotta]

I can't see any obvious problems, based on the picture you've posted.

Give this a try. It works fine for me: Links.xlsm

So, I downloaded the file and it has that same formula in it but when I type stuff in the A Column nothing shows up, I don't get it, so weird...I just don't understand why it is not working....

 

[louperrotta]

Haha oops sorry I replied twice, sorry haha

 

[StephenCrump]

Do you have macros enabled?

If you put a breakpoint in Sub Workbook_SheetChange, can you step through and see what's happening?

 

[louperrotta]

Do you have macros enabled?

If you put a breakpoint in Sub Workbook_SheetChange, can you step through and see what's happening?

So I enabled macros but I'm still lost on the breakpoint, for some reason the code is just not working, because I should be able to type data in Sheet 1 and it appears in sheets 2, 3, etc. correct? **** dude I'm sorry, I just don't know why it's not working once I download it...****...

 

[StephenCrump]

but I'm still lost on the breakpoint ...

I'm not sure what you mean by this?

If you put a breakpoint at the top of Sub Workbook_SheetChange and change any cell in the workbook, the macro should stop at the breakpoint, so that you can step throught the code line by line (using F8 key) to see what's happening.

But it sounds like your code isn't running at all? Can you create and run any macro?

'In a code Module
Sub Hello()

    MsgBox "Hello!"
   
End Sub

 

[louperrotta]

I'm not sure what you mean by this?

If you put a breakpoint at the top of Sub Workbook_SheetChange and change any cell in the workbook, the macro should stop at the breakpoint, so that you can step throught the code line by line (using F8 key) to see what's happening.

But it sounds like your code isn't running at all? Can you create and run any macro?

'In a code Module
Sub Hello()

    MsgBox "Hello!"
  
End Sub

Hey! Hahaha, I'm an idiot, I typed the code in wrong (I'm trying to type it out to practice typing stuff in instead of copy an paste haha) and then when I typed it in correctly on a new book, I accidently did have macros on lol, I feel like an idiot. But I finally got the macros working! YAY! Thank you so much for dealing with me! You are so awesome man!

May I ask one more thing?

May I ask how the code you wrote actually works? Like how you wrote it the way you did, what makes it work, and how VPNs work in general? Again, no pressure, I'm just wondering! I'm starting some tutorials! How long have you been working with excel?