On May 6, 1:48 pm, excel user <excel
wrote:
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