referencing layers and formula recalc

[scott]

A guy on Experts Exchange has several questions about layers. His goal is to have a shape that is aware of the visibility settings for various layers on the containing page. He can have as many as 50 layers on each page of his circuit diagrams.

His was hoping to use syntax like the Pages[pagename] function, i.e., ThePage!Layers.Visibility[layername] but that doesn't work -- the argument must be an integer for Layers.Property[n]. Because of the inherent unpredictability of layer ordering, using a specific integer isn't very helpful.

In his research he discovered David Parker's article on the ShapeSheet https://msdn.microsoft.com/en-us/library/office/gg144579(v=office.14).aspx in which David suggests this formula for validating whether a layer is visible:
=INT(INDEX(0,"ThePage!Layers.Visible["&INDEX(0,LayerMember)+nn&"]"))
where nn is an offset into the list of layers. (The formula is fairly far down the page under the heading "Layer Membership".)

Although he is using David's formula for a different purpose that what David proposed, it sounds logical, but here's the rub: the formula evaluates correctly when dropped into a user cell, but doesn't recalc when the visibility cell value changes. I didn't succeed in finding a cell to hook a DEPENDSON() function to, nor one in which to plant a SETF() to reset the formula.

Any ideas for a technique to allow a shape to monitor the visibility of a specified layer without using code?

[Thomas Winkel]

Hi,

http://visguy.com/vgforum/index.php?topic=7605.0

[scott]

Excellent! Now that is a truly ridiculous ShapeSheet formula!

I hadn't noticed that the person I was trying to help had also posted his question in this forum, but I'm glad he did because you created the syntax I was missing to make the SET() + DEPENDSON() idea work.