Links to Other Worksheet

[Alexey E. Kolmyk]

Hello,

Could you tell me, how to work with formula, that points
to other sheet. I want to know, when cell value was
changes - event Worksheet_Change does not raised.

Thank you for help.

 

[Chip Pearson]

Alexy,

You can link to another sheet with a formula like

='Other Sheet Name'!A1

 

[Tom Ogilvy]

The cell would not change unless you did a calculate I don't think, so you
would have to store the value of the cell, then make a comparison using that
calculate event, then store the value for the next time.

 

[Alexey E. Kolmyk]

There is no other way?
I think about your solution... So, it may be:
1. Detect all "interworksheet" cells.
2. Save all values these cells.
3. On Calculate event for my worksheet I compare saved
values and new values.

Have I understood you aright?

P.S.: How I can detect "interworksheet" formula?

 

[Tom Ogilvy]

That is pretty much it.

set rng = Cells.SpecialCells(xlFormulas)

will get all the cells with formulas.
You would have to loop through those and find out which ones have a formula
that contains an exclamation point.

Dim cell as range, rng as range
Dim rng1 as range
for each cell in rng
if instr(cell.Formula,"!") then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = Union(rng1, cell)
end if
end if
Next

 

[Alexey E. Kolmyk]

But this code founds some formula like this '="!"'.
Has it?

 

[Tom Ogilvy]

You can certainly add more detailed checks - parsing out the front of the
formula and checking it against all worksheet names and so forth.

Please make your code as robust as you feel is necessary.

 

[Alexey E. Kolmyk]

So, there is no way to avoid formula string parsing?
May be some property exists?

 

[Tom Ogilvy]

No, no property exists.

You can use the find method to search for the string within the formulas.