Changing page names messes up shapesheet cross-sheet reference ability

[jrinn]

Not sure if anyone else has had this happen, but if I don't figure a way around this I've lost weeks worth of work building a solution around cross-sheet references.

Apparently if you rename a page in Visio you cannot refer to that page with it's new "displayed" name in the shapesheets.  I just spent an entire day trying to figure out why I couldn't type the right format into a shapesheet cell without getting an error.

Please try this quick example yourself:

Create a new (don't rename an existing, create a new) page named "P1"
Create a new rectangle on P1 (should be "Sheet.1")
Open Sheet.1 shapesheet, add the Shape Data section and change

Code Select Expand

Prop.Row_1="data from Pages["&PAGENAME()&"]!"&NAME()
The resulting value should look like "data from Pages[P1]!Sheet.1"

Open DOC shapesheet, add the User-defined section and change

Code Select Expand

User.Row_1 to =Pages[P1]!Sheet.1!Prop.Row_1
The resulting value should look like "data from Pages[P1]!Sheet.1"

Everything OK, right?

Now change the name of "P1" to "P2".
Notice that Sheet1!Prop.Row_1 updates because of the direct reference to the PAGENAME.
Also notice that TheDocument!User.Row_1 value also gets updated to "P2" although the formula still points to "P1".
How can that be?
Now do something really normal.
Update TheDocument!User.Row_1 formula to reference "P2" instead of "P1" since the drawing shows "P1" now.
Guess what?  "Error in Formula" appears.  You need to set it back to "P1" to get it to work again even though "P2" is the drawing name.

If anyone else picked up this file (or even yourself after forgetting the original name) and tried to program the shapesheet they'd be screwed by not knowing the original page name.

Am I doing something wrong?
is this a bug to my installation?
Is there another way to make these references that won't break like this?

I'd appreciate any help I can get.
  jrinn

[jrinn]

Oops! Typo in the 9th line from the bottom...

Update TheDocument!User.Row_1 formula to reference "P2" instead of "P1" since the drawing shows "P1" now.

should read:

Update TheDocument!User.Row_1 formula to reference "P2" instead of "P1" since the drawing shows "P2" now.

[Jumpy]

Hi jrinn,

I didn't try it out, but is sounds like a problem one sometimes has with shapes and that is Name vs. NameU.
NameU being the universal name.

When you after creating the page the first time change the name to "P1" you change the Name and the NameU of the page.
Every later change of the name of the page will only change the Name.
Therefore after renaming your page again to "P2", the Name is "P2", but the NameU is still "P1".

And seeing your problem I fear NameU is the one used for referencing.

If you want to change the NameU to "P2", too, you have to use VBA I think and the exercises of your last post may pay off. Now the document pagechanged event could be used, to set shp.NameU = shp.Name or sth. like that.

[stevray]

The problem is exactly the Name/NameU thing.  When you change the name of a page, you change Name but you need to use NameU to reference that page.

I had a similar problem when I wanted to put text like "See page 6 for details" on pages in a way that the page number would be updated correctly as pages were added, deleted and/or reordered.  After help from the gurus on this site, I solved that by writing a little addon that scans all of the foreground pages and:

1. Changes NameU to Name if they are not the same.
2. Checks to see if the page has a User.PageName cell; if not adds it with the formula PAGENAME().
3. Checks to see if the page has a User.PageNumber cell; if not adds it with the formula PAGENUMBER().

Then, whenever I want to put in a reference to another page's number, I use the formula Pages[pagename]!ThePage!User.PageNumber where pagename is the name of the page that I want to reference.  Since my addon has synchronized Name and NameU, I no longer have to worry about which to use.

[jrinn]

Thank you both!

That was easy and worked well.

Jumpy, you were right, there is a PageChanged event so every time a page name is changed I automatically pick it up and can update all cross-references in my file that used that page name versus figuring out a more dynamic page change cross-referencing scheme.
No scanning of every page appears to be necessary.

I'll have to think about storing of page names and numbers and see if that helps me or not.

steveray, can you give me a better example of using this method? (the situation where it works well)