MrExcel Publishing
Your One Stop for Excel Tips & Solutions

three-way link between cell values


Posted by Mark on May 22, 2001 11:47 AM

I have a model that I am working on with three worksheets. I want to give the user the ability to enter data in any of the three worksheets and subsequently display the same data in the same cells in the other two worksheets. I cannot use = signs because the user will need to go back and forth between the workbooks and will have to overwrite the cells numerous times. What I am looking for is a macro (or other mechanism) that will recognize when a cell is changed in the workbook and place the value in the same cell in the two worksheet where the cell was not changed. Again the order that the user will put the data in is random.


Posted by Dax on May 22, 2001 12:53 PM


Hello,
One way you could do this is through the use of VBA. I'm assuming that your workbook only contains the three sheets you mention, and this code is written accordingly.

To use this, go into the VB Editor (Alt+F11), click View, Project Explorer and then double click on the ThisWorkbook icon. Enter this code: -

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ChangedSheet As Worksheet, OtherSheets As Worksheet

'First set a reference to the sheet that had the value changed
Set ChangedSheet = Sh

'Disable application events. If this is not done then this macro
'will run indefinitely.
Application.EnableEvents = False

'Now search through ALL sheets in this workbook. As long as the
'sheet is not the one that the user has changed, change the values
'in the other sheets.
For Each OtherSheets In ThisWorkbook.Worksheets
If OtherSheets.CodeName <> ChangedSheet.CodeName Then
OtherSheets.Range(Target.Address).Value = Target.Value
End If
Next

'Re-enable events.
Application.EnableEvents = True

End Sub

HTH,
Dax.

Posted by Mark on May 22, 2001 1:41 PM

Couple Questions?


Dax

I know you tried to make this simple for me but I have a few questions and I'm sure I'll be able to make it work.
1) Do I have to define the range Target is looking at
2) Do I have to put something workbook specific into codename.

I guess I am uncertain which variables in the code I am supposed to change. I only changed
OtherSheets.Range("a1").Value = Target.Value

Maybe another way to show me the code is if you could show me what it would look like if it were placed into a brand new workbook (sheet1,2,3) where I wanted to make all cells "a1" equal.

Thanks for your time Dax
Mark

Posted by Dax on May 22, 2001 3:18 PM

Re: Couple Questions?

Mark,
The code will work exactly as it is. I'll explain briefly - apologies if I'm telling you things you already know.

When certain things happen within Excel such as a user double clicking a worksheet or chart, changing a value of a cell or simply moving the currently selected cell elsewhere then Excel creates an event which can be used from within VBA to change the way in which the application reacts.

In this case, I've used the Workbook_SheetChange event which is fired by Excel every time a user changes a cell in any worksheet in the current workbook. Excel also provides Target, which is a reference to the range changed by the user, and sh, which is a reference to the sheet the user changed. I've simply put this code to react to a user changing a cell i.e. whatever changes a user makes to a cell (or range of cells) the same cells in the other two worksheets will be updated with the same values.

As for the Codename bit, this is included so that the macro doesn't attempt to update the sheet that the user has just changed because that would be pointless.

I hope this is clear, let me know if not.

Regards,
Dax.

Posted by Dave Hawley on May 23, 2001 2:30 AM


Hi Mark

The code Dax wrote will work just fine!

Are you aware you can select a sheet, then holding down your Ctrl key select other sheets ? This will group them, then whatever happens on one sheet will automatically reflect on the other sheets.


Dave
OzGrid Business Applications