Circular scripting between sheets in the same workbook

[excel user]

Hi All,
I am trying to find out if circular scripting is possible in excel and how
easily. We are trying to create a "web downloadable" excel with multiple
sheets and the idea is to have circular sripting between atleast 2 sheets
i.e. f somebody enters a value in a cell in sheet 1, it should be auto
populated in sheet 2 in a cell that refers to the cell in the first sheet and
vice versa. One way communication seems to be fairly easy. Where we are
running into trouble is having the 2 way communication i.e. 1=2 and 2=1.

 

[Jacques ALARDET]

Hello excel user

If Sheet1 = Sheet2 and vice versa, all cells containt a formula : If
somebody enter a value, he scratche the formule

This is correct, if the option Excel Iteration is active with 1 itération

But if somebody enter a value if sheet1!C5, he does nots enter another value
in Sheet2!C5 : because in this cause Sheet1 is not same Sheet2

I hope inderstand your trouble

J a c q u e s

 

[bruceceng]

Hi All,
I am trying to find out if circular scripting is possible in excel and how
easily. We are trying to create a "web downloadable" excel with multiple
sheets and the idea is to have circular sripting between atleast 2 sheets
i.e. f somebody enters a value in a cell in sheet 1, it should be auto
populated in sheet 2 in a cell that refers to the cell in the first sheet and
vice versa. One way communication seems to be fairly easy. Where we are
running into trouble is having the 2 way communication i.e. 1=2 and 2=1.

If I understand your question properly, I think you could do the
following:

Add the following event to Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
If modifyFrom2 = False Then
modifyFrom1 = True
Sheet2.Range(Target.Address) = Target
modifyFrom1 = False
End If
End Sub


Add the following event to Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
If modifyFrom1 = False Then
modifyFrom2 = True
Sheet1.Range(Target.Address) = Target
modifyFrom2 = False
End If
End Sub

Add the following code to a public module:

Dim modifyFrom1 As Boolean
Dim modifyFrom2 As Boolean

Basically any time a change is made to sheet 1, it will fire the event
which will copy the change to sheet2. However, before it copies to
sheet2 it will set a flag so that sheet2 will not try to rewrite back
to sheet1 (causing an infinite loop). However not all changes are
properly captured, like changes to formating and "dragging" of data.

Bruce Eng
bruceeng.com