Layer Properties and Formulas

[AlexHP]

New problem...

I need to show or hide layers based upon certain criteria. I know that by changing the "Visible" property in the "Layers" section on the Page Shape Sheet for the particular layer that I can control the visibility of the layer. Also, I have found that by placing a formula (referencing the criteria from the Document Shape Sheet) into the "Visible" property cell that I can do this easier than by building VBA code to insert a "1" or "0" into the cell. The problem is that if the user opens the Layer Properties dialog and changes the visibility manually, the formula gets overwritten and cannot be used again. Is there a way to do what I am trying to do without losing the formula (i.e. protect the cell)? Let me know what you think... Thanks!!

Alex...

[Surrogate]

Code

'Copyright (C) 2015 by Surrogate™
'This code is free: you can redistribute it and/or modify
'it under the terms of the Just For Lulz License.
'This program is distributed in the hope that it will be useful,
'but WITHOUT ANY WARRANTY; without even the implied warranty of
'MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
' originally posted at http://visio.getbb.ru/viewtopic.php?p=6813#p6813
Sub Only_N_layer(shpObj As Visio.Shape, n As Integer)
Dim ly As Layer
For Each ly In ActivePage.Layers
If ly.Index <> n Then
ly.CellsC(visLayerVisible).FormulaU = False
Else
ly.CellsC(visLayerVisible).FormulaU = True
End If
Next
End Sub
Sub all_layers(shpObj As Visio.Shape)
Dim ly As Layer
For Each ly In ActivePage.Layers
ly.CellsC(visLayerVisible).FormulaU = True
Next
End Sub
Sub Porevo_with_layers()
Dim la As Layer, sh As Shape
ActivePage.DrawRectangle 0, -1, 0.9, -0.5
Set sh = ActiveWindow.Selection(1)
sh.Text = "All layers"
st = "CALLTHIS(" & """" & "ThisDocument.all_layers" & """" & ")"
sh.CellsSRC(visSectionObject, visRowEvent, visEvtCellDblClick).FormulaU = st
For Each la In ActivePage.Layers
Debug.Print la.Name, la.Index
ActivePage.DrawRectangle 0 + la.Index, -1, 0.9 + la.Index, -0.5
Set sh = ActiveWindow.Selection(1)
sh.Text = "Only " & la.Name & " layer"
st = "CALLTHIS(" & """" & "ThisDocument.Only_N_layer" & """" & ",," & la.Index & ")"
sh.CellsSRC(visSectionObject, visRowEvent, visEvtCellDblClick).FormulaU = st
Next
ActiveWindow.DeselectAll
End Sub

example file
1. Press keys Alt+F8
2. Start routine Porevo_with_layers
3. Now you have some buttons under page, each button for each layer. Then you click on button layers hide or show

[AlexHP]

Surrogate, thank you again for your help. But I'm afraid that I have to say that this is not what I need either. I have some VBA programming which will determine which layers will be turned on or off. The user will not get to decide this. I also already have VBA logic which can provide the show/hide method but I was looking for something different. I was wondering if I could use a formula in the Layers section of the Page shape sheet. I know that I can (since I have already tested it) but the problem is that if the user opens the Layers Properties dialog and changes the layer visibility manually then it wipes out the formula and I won't have it there for any future automatic changes. Is there a way to protect this formula or achieve the same result without getting my formula deleted? Thanks!

Alex...

[wapperdude]

You ought to be able to wrap your layer formula with SETATREF fcn.  See User Submitted Section for details/examples on SETATREF.

Wapperdude

[Surrogate]

Also you can wrap value in cell with GUARD() function, in this case users can't change visibility via Layers Properties Dialog. But in shapesheet it is possible!

[AlexHP]

Surrogate, I think this will work well. Thank you!

Wapperdude, that's a lot of good information on SETATREF. I will go through it. Thanks!

Alex...

[AlexHP]

Wapperdude, I have been reading through your dissertation (or is it more a treatise...?) on SETATREF and trying to get it to work. While I have had some success I am still not quite getting it to do what I would like it to do. I have a value in the Document Sheet ("Prop.ODOpt") that I want to use as a condition for a layer to be visible or not. Here is the condition I am using:

Code

SETATREFEVAL(IF(TheDoc!Prop.ODOpt="2","1","0"))

I had placed this in the "Visible" cell of the layer and then:

Code

SETATREF(Layers.Visible[25])

in the "Print" cell to get then both to be on or off together. The problem comes when I use the Layer Properties dialog to change the visibility it wipes out the formula. Any ideas...? Thanks!

Alex...

[wapperdude]

Ah!   "Layers" disrespects SETATREF, so that won't protect your formula.  That makes what you want, much more difficult.

Let's say you add User Defined section to the page shape sheet, and create a row, Used.LayA.  And, let's say, you have Prop.LayA, to control visibility of Layer A.

You could use SETF to shove a value into Layer A visible cell:  IF(Prop.LayA="X",SETF(GETREF(Layers.Visible[1]),TRUE),SETF(GETREF(Layers.Visible[1]),FALSE)).  This allows the use of shape data entry and GUI interface...mostly. 

Let's say Prop.LayA is true.  But user uses GUI to hide the layer.  Your prop value is still true.  You need to set it to false, and back to true.  You could make the GUI value reset the data value, but now that creates a circular argument.  So, now SETATREF is needed.  But how???

Haven't worked thru this, so, don't have a clean solution, but, at least you can see where the problem is.

Wapperdude

[wapperdude]

See attached file.  The Yellow square is on 1st layer, Layer A.  The page shape sheet has User section added, with 2 rows, User.Row_2 is not used and may be deleted.  The use of SETATREF avoided circular argument.  The DEPENDSON fcn may be unnecessary.  Did not test removing it.

So, you can right click in the page and access page shapedata to set the visibility, or you can go directly to standard GUI and change layer visibility.  Formulas are preserved in both cases.

This should be adaptable for your needs.  Don't know if there's a more efficient configuration, I stopped when this worked.

Wapperdude

[AlexHP]

Well... bugger... 

Great job, Wapperdude, unfortunately this will end up causing me a LOT of extra work. Some of my pages have 60-70 layers, so I would need to add a BUNCH of user and shape data cells.

I'm thinking that maybe I should create a custom Layer Properties dialog for a user GUI, that way I can use GUARD on the formula in the layer's Visible cell to protect them but the user can still manually show/hide the layers if needed. I think I have seen something here about a custom Layer Properties dialog. Any thoughts on that? Thanks...

Alex...

[Yacine]

Right approach!
I'd even go farther and let the user "destroy" the formulas, knowing that your GUI solution can restore them.

[AlexHP]

Yacine, not a bad idea. But then I would have to keep track of ALL of those formulas inside of my GUI VBA logic... 

Alex...

[wapperdude]

Well, you could write a small macro, which could be run on each page, or even loop thru every page...assuming you know max layer count in the doc...and insert the User Section + 2 rows for each layer along with required formulas for each row.

You write the macro once, run it once per page or once per document.

Wapperdude

[AlexHP]

Wapperdude, here is my take on your SETATREF idea...

Alex...

[wapperdude]

Nice.  Seems to work fine.

Wapperdude