Struggling with a Value Not Refreshing in Shapesheet

[WoehlkeTAD]

Hello,

I have a need to see if the layer a shape is assigned to is visible inside of the shapesheet of the shape. We're assuming the shape here is only assigned to one layer. The only method for doing this I can find requires me to have Visio re-evaluate the cell in order to refresh the value. The method I have found uses this command:

Code Select Expand

=INT(INDEX(0,"ThePage!Layers.Visible["&INDEX(0,LayerMember)+1&"]"))

This code is inserted into a scratch cell on the same shape. Now when I first enter the formula it correctly enters the value but if I turn the layer off it does not update the value. Is there a better way to do this? Is there a way to make this work?

Code Select Expand

=ThePage!Layers.Visible[LayerMember+1]

On a side note. Visio will update the cell correctly if I change the Layer membership on the shapesheet manually, but not if I update the visibility cell manually; pretty odd. Is there a way I can use an event to force the cell to re-evaluate?

Thanks for any help.

[Paul Herber]

All you need is (for visibility of layer 2):

Code Select Expand

=ThePage!Layers.Visible[2]
Note layer numbering starts at 1.
This updates immediately.

[WoehlkeTAD]

In this particular situation this will not work. I will not always know what layer the shape will be on so I cannot use a specific number. I need to somehow use the contents of the LayerMember cell.

[vojo]

I could 2 ways
1:  Since you don't care if the layer is hidden, do something like this
       user.layercheck = if (page!layer.visible[1], 1, if(page!layer.visible[2], 2, etc etc etc
       Some cell to that needs to know = <function> + dependson(user.layercheck)

2:  If VBA, then I believe there is a sheetchangeevent sub....in there add VBA logic to check layers / make decisions

       private sub sheetchangeevent ()
           if sheet.layer.visible[1] = true then sub <my cool function>
           if sheet.layer.visible[2] = true then sub <my other cool function>
           else <nothing>
           end if

check the VBA syntax  ;-)

This generally how Excell VBA detects a change on a spreadsheet and allow functions to be executed on change

[wapperdude]

Since only a single layer membership is expected, then you can leverage both Paul's and Vojo's approach. 

The problem encountered with the LayerMember cell is that it's value cannot be used directly, plus, it's value needs to be incremented by "1".

In addition, if you want "TRUE" or "FALSE" as the result, you need to convert the value reported by the Layers.Visible result.

So...
1) Create two User Defined entries. 
Name the 1st User.Lyr...short and simple.  Enter the formula:  =LayerMember+1.   This takes care of 1st issue.

2) In 2nd user entry add the formula consisting of cascaded "IF" statements.  The outer IF will provide the TRUE / FALSE result.  The inner IF's, sequentially go thru the page layers and check to see if one matches the shape's membership.  IF not, you get an "Ooops".  The formula would look something like this:  =IF(IF(User.Lyr=1,ThePage!Layers.Visible,IF(User.Lyr=2,ThePage!Layers.Visible[2],"oops")),"TRUE","FALSE").  I only show the scenario for two layers, you need to include an IF for every layer defined on the page.

The inner "If" performs the layer check.  A match (true), invokes a call to that page layer's visibility cell, providing the layer status.  That status will be either "1" or "0".  The status value is then converted to true / false.

HTH.
Wapperdude

[Thomas Winkel]

For sure one of the craziest SS formulas I've ever created, but this does the job:
User.Visible=""
User.VisibleTrigger=SETF(GetRef(User.Visible),"=INT(INDEX(0,"&CHAR(34)&"ThePage!Layers.Visible["&CHAR(34)&"&INDEX(0,LayerMember)+1&"&CHAR(34)&"]"&CHAR(34)&"))+DEPENDSON(ThePage!Layers.Visible["&INDEX(0,LayerMember)+1&"])")