Visio Guy

Visio Discussions => ShapeSheet & Smart Shapes => Topic started by: geoffg41 on August 14, 2021, 01:02:27 AM

Title: Problem with Page to page field access
Post by: geoffg41 on August 14, 2021, 01:02:27 AM
Hi All

I have a problem with the following statement or to be precise the last part " FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")&"?","")" for what ever reason I cannot get this part to work it comes up with a (Error in formula) I know that it is accessing the right shape on the right page as I put a test item in there and it showed up in the selection area

Its obvious I am doing something wrong  can anyone point me in the right direction 

=IF(STRSAME("Yes",Sheet.59653!Prop.Row_20),"Max FLI: "&FORMAT(Sheet.59654!User.FLI,"0.0##")&"?","")&IF(STRSAME("Yes",Sheet.59653!Prop.Row_21),IF(STRSAME("Yes",Sheet.59653!Prop.Row_20),CHAR(10),"")&"Calculated FLI: "&FORMAT(Sheet.59654!User.ImpedanceUpstream,"0.0##")&"?","")&IF(STRSAME("Yes",Sheet.59653!Prop.Row_19),IF(STRSAME("Yes",Sheet.59653!Prop.Row_22),CHAR(10),"")&"Calculated Load: "&FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")&"?","")
Title: Re: Problem with Page to page field access
Post by: Surrogate on August 14, 2021, 05:57:18 AM
Hi,

Did you rename pages in this documen more than one time?
https://techcommunity.microsoft.com/t5/visio-blogs-archive/name-and-nameu/ba-p/237259 (https://techcommunity.microsoft.com/t5/visio-blogs-archive/name-and-nameu/ba-p/237259)

Read more about local and universal names (Name/NameU) problem!

Quote from: Nikolay on May 18, 2019, 09:28:18 PM
May this confusion come from Name and NameU?
See, each shape has two names. One you can see in the UI (in the "Shape Name" window for example or the drawing explorer).

Another one (NameU) is hidden. But when you name a shape for the first time, using the dialog, both names are set.
But later on, if you modify the "public" name, the "hidden" NameU is not modified.

Note that in the ShapeSheet formulas, you can only use the "hidden" NameU to refer to a shape.
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 14, 2021, 06:29:04 AM
Hi Thanks for the reply

Yes i did is there a way around this or is there a way of finding out its current name

Thanks
Title: Re: Problem with Page to page field access
Post by: Surrogate on August 14, 2021, 07:55:46 AM
Quote from: geoffg41 on August 14, 2021, 06:29:04 AMis there a way of finding out its current name
yes, you can.
Use PAGENAME  (https://docs.microsoft.com/en-us/office/client-developer/visio/pagename-function) or NAME (https://docs.microsoft.com/en-us/office/client-developer/visio/name-function) function with argument 750, like PAGENAME(750)
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 15, 2021, 03:22:52 AM
Hi There

I have confirmed the Page Names are correct but am still having no success in getting the values I require  I have tried accessing the page User.Value by itself not in the previous formula and i am still having the same issue "=Pages[2225SUB]!Sheet.467!User.Value " what I done understand is that when I write the formula it finds the user name so I presume that it has access to the page information is there anything else that it could be

Thanks   
Title: Re: Problem with Page to page field access
Post by: Surrogate on August 16, 2021, 08:18:54 AM
Quote from: geoffg41 on August 15, 2021, 03:22:52 AM
I have confirmed the Page Names are correct but am still having no success in getting the values I require  I have tried accessing the page User.Value by itself not in the previous formula and i am still having the same issue "=Pages[2225SUB]!Sheet.467!User.Value " what I done understand is that when I write the formula it finds the user name so I presume that it has access to the page information is there anything else that it could be
I see space at last position in selected formula ! It is OK ? Also i intrested in which position of formula you get error ?
My advice dont use same long formulas. I usually don't try to write such a long formula on one line.
break it up into parts, and see if the result is different from what I expect.





User.Part1=IF(STRSAME("Yes",Sheet.59653!Prop.Row_20),"Max FLI: "&FORMAT(Sheet.59654!User.FLI,"0.0##")&"?","")
User.Part2=IF(STRSAME("Yes",Sheet.59653!Prop.Row_21),IF(STRSAME("Yes",Sheet.59653!Prop.Row_20),CHAR(10),"")&"Calculated FLI: "&FORMAT(Sheet.59654!User.ImpedanceUpstream,"0.0##")&"?","")
User.Part3=IF(STRSAME("Yes",Sheet.59653!Prop.Row_19),IF(STRSAME("Yes",Sheet.59653!Prop.Row_22),CHAR(10),"")&"Calculated Load: "&FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")&"?","")
User.Full=User.Part1&User.Part2&User.Part3
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 16, 2021, 10:10:18 PM
Thanks for the reply I copy your suggestion and the problem still exists
below is the highlighted area that creates a error in formula

(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")&"?","")

I'm not sure what else to do
Title: Re: Problem with Page to page field access
Post by: wapperdude on August 17, 2021, 12:07:32 AM
To facilitate finding the error, create a new User row and copy paste your formula that seems to be failing...FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")

That should execute immediately.  If there's an error you ought to be able to isolate it quickly.

Could be: the page name
Could be wrong shape
Could be the User row or value...maybe doesn't exist???
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 17, 2021, 05:43:26 AM
Thanks again this is driving me insane

So I have created a separate User.TotalLoad in the Site Supply Page in shape ID 59716 which I have placed the formula =FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##") i have checked the 1545_WED_SUB page  shape ID467 to ensure there is data available
But I still get the same error in formula
Title: Re: Problem with Page to page field access
Post by: Surrogate on August 17, 2021, 06:13:46 AM
@geoffg41, can you share your document?

PS
Quote from: geoffg41 on August 17, 2021, 05:43:26 AM
shape ID467 to ensure there is data available
What about value in this cell ? There is string, number or date ? If not number you can get error with function FORMAT!

PPS
At your side Pages[1545_WED_SUB]!Sheet.467!User.Value get error ?
Title: Re: Problem with Page to page field access
Post by: wapperdude on August 17, 2021, 06:46:31 AM
Actually, you've made progress.  You nailed the down the formula that's the issue.  I suspect it's the page name.

So, try doing this, copy the shape in question to a new page.  Leave the page name unchanged.  Say Page-147, or whatever it is.  The shape probably is sheet.1 on this page.  Now change your formula for the new page and shape.  I'd expect it to work.  If so, then the issue is with the page name.  If not, which would be really weird, then the error would seem to be the shape.  Perhaps create a new shape with a User.Value entry, and see if that works.  If not, there may be some corruption, and as Surrogate suggests, and upload would be in order.
Title: Re: Problem with Page to page field access
Post by: Surrogate on August 17, 2021, 03:36:29 PM
May be this trick can help !
Quote from: Surrogate on September 08, 2015, 10:58:10 AM1.  Select shape that name you need to know
2.   Press keys Alt+F11
2a. If in VBA editor you can't see Immediate Window, press Ctrl+G and press Enter
3.  In Immediate Window paste this code ?"Pages[" & ActiveWindow.Selection(1).Parent.NameU & "]!sheet." & ActiveWindow.Selection(1).ID
Profit: in second line in Immediate Window you get full reference to selected shape :)
I create workbook (https://surrogate-tm.github.io/snippets/shapesheet_en.html#ref) with prepared formulas in cells B2 and B5 !
1. Select shape that name you need to know (Visio Application)
2. Open this workbook
3. If you need get reference to shape at another shape, write cell name in range A2 of workbook.
    If you need get reference to shape at same shape, write cell name in range A5 of workbook.
4. If you need get reference to shape at another shape, select cell range B2 of workbook.
    If you need get reference to shape at same shape, select cell range B5 of workbook.
And copy it.
5. Activate Visio application
6. Open VBA editor / if Immediate window is not activated press keys Ctrl+G
7. Paste formula to Immediate window and press key Enter

(http://visio.getbb.ru/gallery/image.php?pic_id=154)
in immediate window in second line you can get reference to cell
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 25, 2021, 11:15:24 PM
First of all I would like to thankyou guys for all your help I eventually worked it out it was looking for the universal name not the page name so that fixed that problem  thanks again

Would you be able to help out the the following problem
1.  I would like to to insert the page name from the shape data list into the the page name area i have created a "User.PageValue" which is where i can get the value  is this possible
     "=Pages[2225SUB]!Sheet.1855!User.Value"

Title: Re: Problem with Page to page field access
Post by: Surrogate on August 26, 2021, 05:55:57 AM
Hi, geoffg41!

You have Shape Data with type "Fixed list" or "Variable list" there stored names (universal names) of pages. And want get according value after change value in Shape Data?
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 26, 2021, 06:15:14 AM
Hi There
I have a fixed list in the shape data and I want to be able to put the value selected into the  page name  "=Pages[2225SUB]!Sheet.1855!User.Value"

Title: Re: Problem with Page to page field access
Post by: Surrogate on August 26, 2021, 06:24:21 AM
something like selector ?
(https://i.imgur.com/FnYSAvk.gif)
Are you sure that each page have shape with ID=1855 (or NameID = Sheet.1855), and that all these Sheet.1855 have cell User.Value ?
Title: Re: Problem with Page to page field access
Post by: Surrogate on August 26, 2021, 07:33:21 AM
May be this example file can help you

Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 27, 2021, 10:34:01 PM
Hi Surrogate

What i am trying to do is this

In my main page "Site Supply" (http://site%20supply.jpg) I have a number of Substations  each one of these Substations refers to another page (http://2225.jpg) in the top left corner there is a Site supply shape with a "total load value" which is the value I need to insert into the associated substation on the main page  there is never anymore than one of these on each page (except for the main Site Supply Page )

1. For example on the main page "Sub 01 2225 Pump Station"  in its shape data  I have a a list of all the pages to select from
when a selection is made I would like that page to be inserted into here "=Pages[2225SUB]!Sheet.1855!User.Value" then I need to insert the shape sheet which will also be shape name  "Initial Supply & Cable.XXXX " and a number at the end which I was hoping that this could be automatically  inserted into here   =Pages[2225SUB]!Sheet.1855!User.Value
hope this makes sense

Is this at all possible
Title: Re: Problem with Page to page field access
Post by: Surrogate on August 28, 2021, 06:47:49 AM
Hi, Geoff!
Quote from: geoffg41 on August 27, 2021, 10:34:01 PM
I need to insert the shape sheet which will also be shape name  "Initial Supply & Cable.XXXX " and a number at the end which I was hoping that this could be automatically  inserted into here   =Pages[2225SUB]!Sheet.1855!User.Value
hope this makes sense
I am sorry, I am Russian and understand what are you need  :-\
I mean words which i selected as green above. You need get value or shapetext from another 2225SUB page and Sheet.1855?
What you mean as End?
Did you try my example from previous message?
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 28, 2021, 06:58:35 AM
Hi Surrogate

Yes  languages are always a  barrier

What I mean is the shape that the value comes from is in the shape name "Initial Supply & Cable " (http://shape.jpg)

yes i did but couldn't get it to work how i wanted it to
Title: Re: Problem with Page to page field access
Post by: Surrogate on August 28, 2021, 06:48:32 PM
Geoff, you want rename shape via ShapeSheet?
Sorry, but it is impossible ! But you can do it via VBA or another programm language like as C#, Python, PowerShell
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 28, 2021, 08:47:31 PM
Hi Surrogate

Thanks for your help 

What I would like to do is to reference the shape from another page using its shape name
Title: Re: Problem with Page to page field access
Post by: geoffg41 on August 29, 2021, 01:31:37 AM
Hi Surrogate

Thanks again for all your help just letting know that I have worked it out
I ended up putting the shape value on the main page and used your example to get that value where I needed it

Works a treat

thanks again 
Title: Re: Problem with Page to page field access
Post by: wapperdude on August 29, 2021, 09:03:44 PM
Take a look at this.  It shows the constructs for page, shape, and cell references.

https://docs.microsoft.com/en-us/office/client-developer/visio/about-cell-references (https://docs.microsoft.com/en-us/office/client-developer/visio/about-cell-references)