Link to home
Start Free TrialLog in
Avatar of Thomas Woehlke
Thomas Woehlke

asked on

Struggling with a Value Not Refreshing in Shapesheet

Hello,

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

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

Open in new window

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.

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?
ASKER CERTIFIED SOLUTION
Avatar of Scott Helmers
Scott Helmers
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Thomas Woehlke
Thomas Woehlke

ASKER

I actually found that formula on the Microsoft site. Without the int() wrapping the function it just spits out a string, but with the int() it shows a value.  What's also odd is without the int() it will update the string value. By any chance would there be a STR() equivalent function I could use to spit out the name in the form of a string? I searched the sdk documentation and did not find one so I'm guessing not.



By any change is there a looping function I can use in the shapesheet? To loop all the layers? I'm resistant to use lots of User data fields because I could have as much as 50 or so layers, and lots of shapes in the schematics I'm creating. I may have no choice but to got he macro route, which will be a difficult sell here.
50 or so layers
I was hoping you weren't going to say something like that!

The cost of using 50 user cells in each shape isn't that bad... but it sure isn't pretty. However, if the list of potential layers is well-defined in advance, it would preclude the need for macros. (Here's a thought: what if you create a single user cell with a formula like this:
=";" & ThePage!Layers.Visible & ";" & ThePage!Layers.Visible[2]  & ";" & ThePage!Layers.Visible[3]...  
[Note the semicolon at the very beginning to push layer one into zero-relative position one in the list.] Then you could use INDEX(n) to retrieve the visibility value for layer n. The single user cell would contain a honking long formula, but it's a single cell instead of 50.)

I noticed the behavior with and without the INT() function. It is curious. By any chance, do you have the URL for the page where you found that formula? I'd like to ask some other Visio experts about it.

There isn't an equivalent to the STR() function.

Alas, there isn't a looping function in the ShapeSheet. We've discussed that need for that with the Visio dev team but I wouldn't expect to see something like that happen anytime in the near future.
oops. I did not put in the link correctly the first time.

Chapter 3: Understanding the ShapeSheet (Microsoft Visio 2010 Business Process Diagramming and Validation)

Scroll to the bottom of the page where they talk about layers.
Thanks. I should have known the formula came from David Parker -- he's "Mr. Layer"! He's exceptionally knowledgeable about Visio in general, but uses and works with layers extensively. I'll ask him whether he has any thoughts.
It looks like you asked a similar question in the Visio Guy forum -- and the good news is that you got a better answer than I could come up with! For the benefit of anyone else who reads this thread and is looking for the solution, here's the link:
http://visguy.com/vgforum/index.php?topic=7605.0

The formula proposed there by Thomas Winkel accommodates the specific case in which the shape in question is on a single layer. Even though the formula is pretty ridiculous already, it seems like it would be possible to parameterize it to accommodate layer n instead of just layer 1. If that's useful, let me know and I'll experiment a bit, otherwise it looks like you have a workable solution.