Linking a cell to another. vba

[finaljustice]

Hello there, I've been wondering if there is a way link cells to another through VBA.

Lets say, I want to link Cells : A1,B1,C1,D1 from sheet 2 to the same cells in sheet 1.


Thanks
Final

 

[Desu Nota from Columbus]

Hello there, I've been wondering if there is a way link cells to another through VBA.

Lets say, I want to link Cells : A1,B1,C1,D1 from sheet 2 to the same cells in sheet 1.


Thanks
Final

With Sheets("Sheet1")
     Range("A1").FormulaR1C1 = "=CONCATENATE(Sheet2!RC,Sheet2!RC[1],Sheet2!RC[2],Sheet2!RC[3],Sheet2!RC[4])"
    End With

Is this what you meant? Take sheet 2 values and put the into one cell in sheet 1?

Oops, I should have read a little more carefully...

 

[mikerickson]

Put this in the ThisWorkbook code module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cellAddress As Variant
    Dim toSheet As Worksheet
    If Sh.Name = "Sheet1" Then Set toSheet = Sheets("Sheet2")
    If Sh.Name = "Sheet2" Then Set toSheet = Sheets("Sheet1")
    If Not toSheet Is Nothing Then
        On Error GoTo ErrorOut
        Application.EnableEvents = False
        For Each cellAddress In Array("A1", "C1", "D1")
            toSheet.Range(cellAddress).Value = Sh.Range(cellAddress).Value
        Next cellAddress
    End If
ErrorOut:
    On Error GoTo 0
    Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

Here is another event procedure (it goes in the ThisWorkbook code module as well) for you to consider...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Const FirstSheet As String = "Sheet1"
  Const SecondSheet As String = "Sheet2"
  Const CellsToLink As String = "A1,B1,C1,D1"
  If Not Intersect(Target, Sh.Range(CellsToLink)) Is Nothing Then
    Application.EnableEvents = False
    Worksheets(FirstSheet).Range(Target.Address).Value = Target.Value
    Worksheets(SecondSheet).Range(Target.Address).Value = Target.Value
    Application.EnableEvents = True
  End If
End Sub

Note: You need to change the three constants (Const statements) to match your actual setup.

 

[Rick Rothstein]

Just as a follow-up on my last posting. I know there are some who will not like the fact that my previously posted code always assigns the values back to the cell(s) the user just changed as well as to the linked cell... the reassignment will happen so quickly as I don't find any problem with it myself; however, for those who are troubled by it, here is my code presented in a more conventional manner...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Const FirstSheet As String = "Sheet1"
  Const SecondSheet As String = "Sheet2"
  Const CellsToLink As String = "A1,B1,C1,D1"
  If Not Intersect(Target, Sh.Range(CellsToLink)) Is Nothing Then
    Application.EnableEvents = False
    If Sh Is Worksheets(FirstSheet) Then
      Worksheets(SecondSheet).Range(Target.Address).Value = Target.Value
    Else
      Worksheets(FirstSheet).Range(Target.Address).Value = Target.Value
    End If
    Application.EnableEvents = True
  End If
End Sub