Bi-Directional Data Entry

[lovecolorado]

I want to create a Excel file with three worksheets, where each wprksheet contains a large table. While most of the cells' content is unique, several dozen of the cells' content will appear at different places in all two, or all three, worksheets.

The table is very simple for all of the cells whose content is unique. My question has to do with the cells whose content is duplicated in two or all three worksheets. For these cells, I could just force the user to go to a 'input'' worksheet and enter changes from there. That is very simple to implement.
However, to make updates simple for the user (even though the spreadsheet becomes more complex for me), I don't want to force a user who is making updates on one worksheet to have to go to another worksheet to change values for cells whose content appears in multiple worksheets. Instead, I want a user to be able to enter a new value into any cell of any worksheet and, if that cell's content appears in multiple worksheets, for that value to appear in that cell's location on the other worksheet(s).

Normally, I follow the description of what I'm trying to do with a copy of the code I've been working on. In this case, I don't have any code because I can't figure out how to do this. Despite my lack of ideas on how to do this, and despite my inability to find a solution by searching this site and the internet in general, I am confident that someone with better VBA skills than me can tell me how to do this - even for several dozen cells scattered across three worksheets. The question is 'how', so I'm reaching out for suggestions.

Thank you in advance for your help on this, as I am completely stymied.

[harrisonland]

Might be easier to come up with a solution if you post a sample cut-down workbook.

However, in principle: use the worksheet's "on change" event:

Please Login or Register  to view this content.

• For each worksheet where duplicates might exist:
• check if record exists in another worksheet
• if so, replace that record
• replace any associated values
• check the next worksheet and repeat

Could that be a starting point?

Tim


EDIT: NB: don't forget to switch off application events, otherwise you'd end up with an infinite loop!

[lovecolorado]

Thank you, Tim, for that suggested start.
I am confident that the line of code you cited will be part of the solution.
The place I get stuck (and I didn't mention this before because I want to avoid directing other people's minds down a failed path) is how to capture a value that is entered into any of these multi-worksheet cells. If I can capture the value, then I can write it to the proper locations on each worksheet.
But . . . how do I capture a value that may be entered at any of three locations?

[harrisonland]

It's just

Please Login or Register  to view this content.

within the on_change event.

To capture a value in any of the alternative locations, you'd just include an "on change" event for that worksheet. I use this as a basic template:

Please Login or Register  to view this content.

[lovecolorado]

First: Thank you. I was too stymied to think of the simple solution of just putting code to detect a change on all three worksheets. With that, I can easily capture the changed value and write it to the appropriate locations on other worksheets. Because there are several dozen such cells, this will generate a lot of code, but it will get me to where I'm trying to go.

Second: That's some excellent code skeleton you've suggested. Thank you further for that!

[nigelog]

See attached - code is in sheetchange events and ranges set in Module called by Workbook open event

Sample code
Module

Please Login or Register  to view this content.

sheet module

Please Login or Register  to view this content.

[harrisonland]

No worries... You know where we are if you get stuck again.

Thanks for feedback.

Tim

[nigelog]

Thankyou for the feedback, glad to help