Links to Other Worksheet

  • Thread starter Thread starter Alexey E. Kolmyk
  • Start date Start date
A

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.
 
Alexy,

You can link to another sheet with a formula like

='Other Sheet Name'!A1
 
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.
 
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?
 
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
 
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.
 
So, there is no way to avoid formula string parsing?
May be some property exists?
 
No, no property exists.

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