Tracing Dependents to other Worksheets (XL2000 SR1)

Topic

New Reply

Hello,

I am trying to develop a utility that can trace dependents and precedents to other sheets in the workbook.

The code that I am using to list the precedents or dependents is :

For n = 1 To ActiveCell.Precedents.Count
UserForm1.ListBox1.AddItem ActiveCell.Precedents(n).AddressLocal
Next n

This code lists all cells that are contained on the same sheet as the formula, but if the formula includes a cell from another sheet, it does not show up.

Is there away to capture all of the cell address for every cell that impacts or is impacted by another cell?

Thanks,

Reply | Quote

Replies

You can’t do this with the precedents property (which returns an absolute reference even if the underlying formula uses a relative reference). Even
MsgBox ActiveCell.Precedents.Count
only counts precedents on the same worksheet.

To overcome this, you could parse the formula itself, extracting the references to other cells/ranges/worksheets/workbooks. You might also want to list repeated references only once. Consider too how you want to represent named ranges – ‘precedents’ returns the range, not its name.

Hope this helps.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote