Pages Collection and sub shapes

Started by MacGyver, July 01, 2015, 09:16:37 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

MacGyver

I'm trying to get the text from a child shape from a dynamically selected page.
If I wanted to get text from a regular shape on a static page, the formula would look something like this:=SHAPETEXT(Pages[SH 2]!Sheet.1!TheText)


1.) How do I step down to subshape level, I would guess the following formula would work, but it doesn't. (Sheet.12 is the child shape of Sheet.1)=SHAPETEXT(Pages[SH 2]!Sheet.1!Sheet.12!TheText)

2.) How do I dynamically select the page name?  Unfortunately the Pages Collection isn't letting me put anything in there other than a static page name. i.e. the following doesn't work: =SHAPETEXT(Pages[User.Prop1]!Sheet.1!TheText)

Thanks,
Josh



wapperdude

Your Point 1: just reference sheet.12.  Don't put in a "double" sheet reference.  Grouped or not, sheet IDs are unique.

Your point 2:  my initial reaction is you can't do that. 

Wapperdude
Visio 2019 Pro

JohnGoldsmith

I agree with Wapperdude that you can't do item 2 very cleanly, but one option might be to push the whole formula into another cell using the SETF function.  For example:



User.PushCell = SETF(GetRef(User.TargetShapeText),"=SHAPETEXT(Pages["&Prop.TargetPageNameU&"]!Sheet.1!TheText)")

User.TargetShapeText = SHAPETEXT(Pages[PgNameU]!Sheet.1!TheText)

User.CoercedText = IF(ISERRVALUE(User.PushCell),"SomeDefaultText",User.TargetShapeText)


Prop.TargetPageNameU.Value = "PgNameU"



Note, that the 'Pages[...' syntax requires the univeral page name and won't work with the local one.

Depending on the problem that you're trying to solve though I wonder if a page or document cell might work better for you?

If that's the case then you might find this post useful:

http://visualsignals.typepad.co.uk/vislog/2011/11/shapes-with-global-values.html

Hope that helps.

Best regards

John
John Goldsmith - Visio MVP
http://visualsignals.typepad.co.uk/

wapperdude

I vaguely recall trying serf and doing something of this sort, unsuccessfully.  The problem of using a dynamic page approach is that the page name is stored as a string, e.g., whereever = "Page-16"  The function, PAGES[Page-16], expects a literal page reference.  It will not evaluate a parameter reference  A very stubborn function. 

But,  like I said, it's a vague recollection, so John's suggestion might work.
Visio 2019 Pro

MacGyver

John,
Thanks, that is exactly what I was looking for. I started looking at the setf function and couldn't get it working. 

I've done the page cell before, I was trying to avoid doing that as it would require me to replace a shape with an updated one on every page of the drawing (so that when I dropped this shape on the page, it automatically added the pagesheet cell).

For those of you who are curious, I want to create a revision status block on the first page of my drawings. Each, "block" will include a page number and its revision. Right now this is maintained by the user ensuring the revision status block is accurate. I want to make it a formula and have the revision dynamically update. You change the rev on a page, it updates; you change the sheet number in the status block, it updates.

wapperdude

#5
Back at my PC,  glad I encouraged you to try John's recommendation.  It does, indeed, work well.
It also seems to track re-order plus updating page names!

Thanks John, I too, find this useful.

Wapperdude
Visio 2019 Pro