cells = each other

[Eraengineer]

Looking for vba where if you change A1 on sheet 1 it changes A2 on sheet 2 and vice versa. Tried Google but failed....

 

[mumps]

Just to clarify ... if you change A1 on sheet 1, you want A2 on sheet 2 to equal A1 on sheet 1 ... if you change A2 on sheet 2, you want A1 on sheet 1 to equal A2 on sheet 2. Is this correct?

 

[Eraengineer]

yes

 

[mumps]

Place this code into the worksheet code module for sheet 1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    On Error Resume Next
        Sheets("Sheet2").Range("A2") = Target
End Sub

Place this code into the worksheet code module for sheet 2:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
    On Error Resume Next
        Sheets("Sheet1").Range("A1") = Target
End Sub

 

[Rick Rothstein]

ADVERTISEMENT

Alternately, instead of maintaining two separate event procedures in two separate Worksheet Modules as mumps suggested, you could to what you want with only one event procedure in just the Workbook Module (double click the ThisWorkbook item in the Project Window to bring up the Workbook Module)...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Application.EnableEvents = False
  Select Case Sh.Name
    Case "Sheet1": Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Range("A1")
    Case "Sheet2": Sheets("Sheet1").Range("A1") = Sheets("Sheet2").Range("A1")
  End Select
  Application.EnableEvents = True
End Sub

Note also that instead of maintaining On Error traps as mumps did, I avoided generating the error by simply turning event handling off before the code starts and then turning it back on after the code finishes.

 

[Eraengineer]

I tried mumps but it goes through this strange looping procedure.... Try it, it is kinda different...

 

[Eraengineer]

ADVERTISEMENT

Rick, yours did the trick. The error was causing the loop. Thank you. Now I just need to change the sheet2 range A1 to textbox1....

 

[Eraengineer]

I am just shooting in the dark here but I tried this and I got denied. Any thoughts on how to make this work for a drop down list?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Select Case Sh.Name
Case "Sheet1": Sheets("Manpower Graph").Shape("Drop Down 8") = Sheets("Manpower").Range("B4")
Case "Sheet2": Sheets("Manpower").Range("B4") = Sheets("Manpower Graph").Shape("Drop Down 8")
End Select
Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

"I got denied" doesn't really tell us anything... if you got an error message, you should tell us what the message was and what line the code stopped on... then we would have something specific to hone in on. Just guessing now... is your "Drop Down 8" a list in a cell? If so, don't focus on the list, instead, focus on the cell the list is attached to as that is what will hold the selected value? As for what I think your error might be ("Object doesn't support this property or method")... that would be coming from the fact you used Shape("Drop Down 8") instead of Shapes("Drop Down 8").

 

[Eraengineer]

I have to focus on the list due the list being displayed on a graph. I did not get an error it just did not work. I tried linking the drop down to another cell but it reformated the values in that selected cell since they are dates. I tried formatting the dates back to dates but it would not work. I feel this is happening because my dates are being constructed by an array. I also tried this which is what I think you were talking about

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Select Case Sh.Name

Case "Sheet1": Sheets("Manpower Graph").ActiveChart.Shapes("Drop Down 8") = Sheets("Manpower").Range("B4")
Case "Sheet2": Sheets("Manpower").Range("B4") = Sheets("Manpower Graph").ActiveChart.Shapes("Drop Down 8")

End Select
Application.EnableEvents = True
End Sub