VBA Code will not affect multiple sheets

Posted by Charles Nilsen on October 04, 2001 4:39 AM

First, thanks to those who helped me yesterday with this...

Now I am trying to make my script, which highlights in yellow any cell that changes along with it's dependents, work for ALL sheets in a workbook. Thoughts?

I have the following code, in the window in the VBA editor under "Worksheet" on the left pull down and "sheet change" for the right pull down:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Target.Interior.ColorIndex = 6
On Error Resume Next
If Target.Dependents Is Empty Then
Target.Dependents.Interior.ColorIndex = 6
End If
End Sub

I'm sure that where I have this code is part of the problem, but I don't know what else to do with it.

Posted by Robb on October 04, 2001 4:55 AM

Charles

Your code seems to work, but I think you have it in the wrong place - it needs to
be in the Workbook code.

When you are in VBE, make sure the Project Explorer is visible -
it is available via the VBE "View" menu (or Ctrl +R whilst in VBE).

In the Project you will see one item called "This Workbook" (usually at the end of
the Sheets list) - double click on it.

A code window will open for the Workbook - paste your code in there.

It should now work for all sheets in the Workbook.

Any help?

Regards

Posted by Jerid on October 04, 2001 4:58 AM

Your code will work if it's in the ThisWorkbook module.

Posted by Charles Nilsen on October 04, 2001 5:05 AM

Well, it's under "This workbook" and is sheetchange. To clarify -> what the script does not do is follow dependents across multiple sheets. It does seem to work for each individual sheet indepentently (i.e. dependents within the same sheet as the changed cell), however.

Thanks!!!