Programatically navigate to depedent cell(s)

[Pratik Mehta]

Friends,

I am using Excel VBA to keep track of depedent cells.

In a certain scenario, if any cell is getting changed and
it is having any depedent cells, I want to set focus to
that depedent cells. I am using Change event to achieve
the same functionality. Code snippet is like below:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler:
Target.Dependents.Activate
Exit Sub
errHandler:
Application.StatusBar = Err.Description
End Sub

It works fine if depedent cells are in the same sheet.
But, if depedent cell is in other sheet and/or workbook.
It is not working and giving error like "No Cells were
found"!.

Please guide me how can I do that?

 

[Tom Ogilvy]

No VBA methods support linked cells to other sheets (that I am aware of).
Stephen Bullen used the Excel4 commands associated with the dependency
arrows to solve this problem. You can check out his utility to resolve
circular error references where the code is open to inspection.

http://www.BMSLTD.ie

On the left is a link to his excel page.

 

[Guest]

Hi Tom,

I visited the site refered by you, http://www.BMSLTD.ie,
but I found the example "FindCirc.zip" on that site.

But that code is locked for viewing.

Means, I can just see how it works. But, I am not able to
understand how it's done.

Anyway, Thanks for your reply,

pratik

 

[Tom Ogilvy]

There should be two files in the zip. An XLS and an XLA. The XLS is open
for viewing. I may be wrong about having to use the xl4 macro technique,
but in any event, Stephen claims it works with links to other sheets, so
whatever method he used should show you how to do it.

The XLS is an xl95 formatted file, so you should have no problem looking at
the code. (I looked at it in xl2000 and also ran it and it worked fine).