Changing one spreadsheet page from another

[Mitsu]

Hi all, I have a multipage spreadsheet that also has a table of contents. If data on any of the pages has been updated, I want some way for the table of contents to indicate which pages have updated information. This is so users don't need to look at every page to see if anything has been updated. So, if one or more cells have "changed" on a page I would like the TOC to show that pages title as maybe a different color for example.

I truly appreciate all and any help on this one.
Mitsu

 

[RobBroekhuis]

On the ThisWorkbook object code page (go there by double-clicking on its entry in the VBE Object Browser tree), create a Workbook_SheetChange event handler, by selecting "Workbook" from the left dropdown and "SheetChange" from the right dropdown above the code window. This will create the following header:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

inside this sub, you can check if the "change" was important enough to warrant updating the TOC, and if so, use the information in the parameters (especial Sh) to determine which sheet was updated.
For example, if your TOC sheet has the sheet names in range A3:A10, you could do:

dim cell as range
set cell=sheets("TOC&quot.range("A3&quot
do while cell<>Sh.name and cell<>&quot;&quot;
set cell=cell.offset(1,0)
loop
if cell=Sh.name then cell.font.color=vbRed

Note that if you actually change the CONTENT of the TOC sheet (not just formatting), you'll need to use

application.enableevents=false
...
application.enableevents=true

around the statements that do the modifying, to prevent the sheetchange event from launching again, recursively.
I hope that helps - if you need more help, let us know.

Rob

 

[Mitsu]

Wow, Thanks Rob.
I will give that a try.


Mitsu

 

[Mitsu]

I am still lost...
how does that code &quot;know&quot; what sheet and cell is doing the changing... and how would it &quot;Know&quot; to change the proper row's font color on the TOC page?


Mitsu

 

[RobBroekhuis]

The parameters passed to the change event tell you that information:

Sh is a sheet object that refers to the sheet that was just changed (in the code above, I used Sh.name, which is the name of the sheet).

Target is a range object that refers to the cell that was just changed. From your original post, it wasn't clear that you needed to refer to the actual cell location, so I didn't use it in the code above.

The do...loop structure in the code compares Sh.name to the contents of cells in a table starting in cell A3 on a &quot;TOC&quot; sheet. You would have the sheet names in this range; the sheet which has just been changed gets its color changed to red.

Make sense?
Rob

 

[Mitsu]

Hi Rob:

thank you for your time!

Here is what I have for code:




Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Dim fred As Range
Set fred = Sheets(&quot;TOC&quot.Range(&quot;A3&quot
Do While fred <> Sh.Name And fred <> &quot;&quot;
Set fred = cell.Offset(1, 0)
Loop
If fred = Sh.Name Then cell.Font.Color = vbRed

End Sub

I have a workbook with 7 pages. one called &quot;TOC&quot; one called &quot;page 1&quot;, then &quot;page 2&quot; and so forth.

On the TOC in cell A3 it says &quot;page 1&quot;... in cell A4 it says &quot;page 2&quot; and so forth.

All other pages are blank for this test.

when I open the workbook, and then go to &quot;page 2&quot; for example, and then I type anything into any cell and hit enter I get this error.

&quot;run-time error '424': Object Required&quot;


I figured out that the term &quot;cell&quot; in your code was a variable name and not a command so I replaced it with &quot;fred&quot;. I don't know if I also needed to have
&quot;cell.Offset(1, 0)&quot; look like &quot;fred.Offset(1, 0)&quot; or &quot;cell.Font.Color = vbRed&quot; look like &quot;fred.Font.Color = vbRed&quot;

when I made those changes I don't get an error, but It doesn't do anything.


sorry for the long post.


Mitsu

 

[RobBroekhuis]

Yes, change the other references to &quot;cell&quot; to &quot;fred&quot; as well. It sounds like you're doing the right thing. I just reproduced it quickly on my end, and it works. Are you sure you put the workbook_sheetchange sub on the code page for the ThisWorkbook object? It won't work on just any module. If you didn't do that, try it by double-clicking on the ThisWorkbook excel object in the VBE object browser.
Rob