Master Shape\Stencil that uses custom data from shape already in the diagram

[soregums]

Scroll Down to see my solution

So I have sheet "control" that is set as the background of  4 other sheets that holds common information.

I've just successfully put together my first smart shape that has 4 fields and people have to use the shapedata/popup dialog box to edit the text.

Now I want to use one of these fields in my master/stencil shapes to prefill the SiteCode so we don't have to keep typing it.

The Problem
When in "edit master shape mode" there is no Sheet.76!Prop.SiteCode and the formula has an error.
How do I get this to work?

Stated as a question. How do I reference from a Master shape/stencil a field that only exists in the Diagram where this stencil will be used?

Thanks

[cliff50]

Hi
I am thinking that -> Sheet.76!Prop.SiteCode... sheet.76 refers to a shape that is already on a page in the document. therefore ,  when you are in edit master mode of the stencil known as -> "control",  the reference to -> sheet.76 returns error because it cant be located from the edit master mode environment .

perhaps,...  you could use the master shapes add / drop event,  such that when you place the master "control" shape onto a page,  a macro is triggered to force the referencing formula into the custom data cell.  However that would assume, you already know the sheet identifier that you want to reference on that page.

without altering the document design, my thoughts for the moment are, ...it would need to be a VBA solution to reference a shape already in the diagram.

[Yacine]

That sounds confusing.
Can you upload a file (V2007 or lower)?

[Jumpy]

Another approach:

Place the information, that currently is in Sheet.76!Prop.SiteCode in a perhaps similar named cell in the ShapeSheet (PageSheet) of the page. You would than reference that in a shape with the formula ThePage!Prop.SiteCode.
When editing the masterhape to place that new formula, you first have to add that cell (Prop.SiteCode) to the Page of the edit-master-window. The cell can stay empty but must exist.

[vojo]

could do following

eventdrop = docmd(1312) + setf(getref(user.trigger), 1)
user.trigger = <value set on dropping shape>

user.control = if(user.trigger, (if(props.control = 1, thebackgroundpage!user.control1, if(props.control =2, thebackgroundpage!user.control2, if(props.control = 3, thebackgroundpage!user.control3, 0))))),-1)

What this would do is the following
   1.  Upon drop, the props GUI comes up and allows you to select whatever
   2.  The + setf(getref(...    triggers a flag to have the control cell execute
   3.  With user.trigger = 1, the user.control cell takes props.control <or whatever you want to call it> and decides to pull in the value

So for example, this could be used to pick 1 background page out of N....or....1 control value out of M....or or or

[soregums]

Another approach:

Place the information, that currently is in Sheet.76!Prop.SiteCode in a perhaps similar named cell in the ShapeSheet (PageSheet) of the page. You would than reference that in a shape with the formula ThePage!Prop.SiteCode.
When editing the masterhape to place that new formula, you first have to add that cell (Prop.SiteCode) to the Page of the edit-master-window. The cell can stay empty but must exist.

This makes sense.
And seems like the simplest way to go.

Upon reading up about references there is a way to access other Pages.
However there is a caveat on this.
If the page is renamed you need to use the NameU of the page and not the Name...
How do I find out the NameU of a page?? (I have a feeling the page has been renamed since it was created and I don't want to make a new page in case I break other stuff...)

So to test all the above, I did this.

• Create a new page, default name was Page-8, rename to "test"
• On page test, go into page setup, set it as a background page
• Close page setup, create a rectangle, Sheet.1
• Open up the shapesheet for Sheet.1, add a shape data row of SiteCode
• Go to my other page "Logical", go into page setup and set test page as the background page
• open up it's shapesheet for the page
• Create a new shape data row of SiteCode with value of =Pages[test]!Sheet.1!Prop.SiteCode
• close the shapesheet and create a new text field and insert a field with a custom formula of =ThePage!Prop.SiteCode
• Done. Editing the shape data on test page, show up as expected on the logical page text's field: http://i.imgur.com/Oynaxib.png
• as long as there is a ThePage!Prop.SiteCode all is good, just updated my master and works exactly how I want it too, xxxx in the master, and once i dropped it onto this logical page it updated to 999999, like in the screenshot

Now go into the Logical pages shape sheet and modify the value for the SiteCode shape data row to point at =Pages[Control]!Sheet.76!Prop.SiteCode - error...
So I suspect the page I need to reference is actually called something other than Control - any ideas how to find out the NameU name of my page?

Thanks

oh Vojo, i don't get how to "implement" (where is this stuff) your idea as i haven't had any exposure to the user.trigger / user.control parts yet. I understand the logical flow and why it should work and would be a good solution for what i want to do - so thanks, something for me to look into in the future

[soregums]

Now go into the Logical pages shape sheet and modify the value for the SiteCode shape data row to point at =Pages[Control]!Sheet.76!Prop.SiteCode - error...
So I suspect the page I need to reference is actually called something other than Control - any ideas how to find out the NameU name of my page?

Opened up VBA and inspected the values.
NameU is "Background"

as such "=Pages[Background]!Sheet.76!Prop.SiteCode" now works!

What a PIA

[Jumpy]

Name vs. NameU is a regular problem. The first time you rename a page Name and NameU will change to the new name. Every change after that will only change Name (not NameU any longer).

If you found out the NameU with VBA you could change it there, too.
With

Code Select Expand

yourpage.NameU=yourpage.Name for example.
Otherwise you could be iritated when you look at your solution a year from now: "Why do I reference the page background, although the name of the page is Control? Why did I do this? ..."

[soregums]

Name vs. NameU is a regular problem. The first time you rename a page Name and NameU will change to the new name. Every change after that will only change Name (not NameU any longer).

If you found out the NameU with VBA you could change it there, too.
With

Code Select Expand

yourpage.NameU=yourpage.Name for example.
Otherwise you could be iritated when you look at your solution a year from now: "Why do I reference the page background, although the name of the page is Control? Why did I do this? ..."

Yeap, I thought about this, thanks for mentioning it though for others who might stumble into this thread
Due to time constraints I'm having to balance the "do it perfectly vs make it work". So I'm just making notes of relevant things like this.

[soregums]

Hmm

I found the Document User fields and as long as the stencil/master is in the same document just use this field to create the link.
Make it less portable I guess, however eliminates the usability issue of user creating a new page and not defining the page shape data and thne wondering why what they have put in the control page has not updated their new logical page...

So in my use case this link via document.user-defined cells is the best way to go

[soregums]

OK

here is my final solution.

BG Sheet (Control)
+ Sheet.14!Prop.myNumber = the info I want in my custom shape

Stencil/Master Shape
- have a graphic and a text box grouped and is then this group is dragged into the document stencil thus creating a new master shape with a Sheet5 as the topmost thing
+ Sheet5!prop.myNumberValue has a formula of =IFERROR(Pages[Control]!Sheet.14!Prop.myNumber,0)
+ now in my textbox I highlight the text where I want this to appear and insert a field with a custom formula of =Sheet.5!Prop.myNumberValue
- close the master shape, save the changes

Now everything is awesome, I update the Control sheet and the text on my shape is updated.
It is a Site Code and all the objects I need have this site code, the reason I use a "myNumberValue" field is so I can override the number at a shape level with other fields (myNumber/useCustomNumber)

THE ISSUE!!!

When I copy this stencil from one document to another (it is the same document, just in a different file/instance of visio) the formula changes.
+ before copy = IFERROR(Pages[Control]!Sheet.14!Prop.myNumber,0)
+ after paste = IFERROR(Ref(),0)

If I manually go into the master and put the formula back it works because there is a Pages[Control]!Sheet.14!Prop.myNumber

I copy this stencil by having two visio instances open beside each other with the both document stencils in view and drag from one document stencil to the other.

Question
How can I protect this forumla so Visio doesn't try to fix up references and just uses it "as is"?
why? because people need to be able to make stencils and then put these stencils as is back into the central template file that is used.

Thanks

[soregums]

it is looking more and more like I need to use VBA to fix up "ref()" values in formulas

I remembered I used INDIRECT in Excel and found this post[1] - Visio doesn't have INDIRECT

I really need to be able to copy/paste between different Visio files and maintain this valid reference "Pages[Control]!Sheet.14!Prop.myNumber".
This is the last thing to solve on the template I'm putting together and I really didn't want to use VBA as relying on VBA means making sure VBA is enabled/turned on/accepted and users don't always click the button properly AND then it doesn't work and then they do the wrong then and then the diagram doesn't work properly and then they have to start again from scratch  :/

Anyways I'll give it a few more hours and if I can't get it to work will start looking up how to do it in VBA...

[1]http://visguy.com/vgforum/index.php?topic=2156.0

[soregums]

so If i add another property Sheet5!prop.evalFormula and set value to
=SETF(GetRef(Prop.myNumberValue),"IFERROR(Pages[Control]!Sheet.14!Prop.myNumber,0)")

everything works great.

The issue now is this formula is only evaluated if I
- go into the Master Shape
- open the shapesheet
- click on prop.evalFormula value field
- press enter twice (once to go into edit mode, once more to save the edit and thus eval the formula)

Is there a way to get formulas to automatically evaluate without manually doing something to prompt them to eval?

[soregums]

Booyaaa!!

Cracked this muddyfuddy

See attached

What I have here:

• a background page with a fixed input (Number)
• a Master Shape that needs to show this Number as part of its text
• this Master shape can show a differet number if required
• the shape once it is on the page in the diagram can be copy and pasted freely (had issues with using TheDoc!User.fields not existing on the clipboard)
• able to freely move the actual stencil master between different instances of the same file - thus enabling work to be done outside of the original template file
• able to copy/paste shapes on the page to other diagrams (same template) - not advised as it breaks the link to the master, however the shape still functions properly (gets its Number from the control page) (would do this when a new template version is released, most likely cause it has additional master shapes available, won't need to create the diagram from scratch)

Why?
Well look at this screenshot

Each one of those devices (all network equipment follows this naming convention) needs to be manually edited atm and can have anywhere between 10 & 50+ devices on a diagram
I'm making a proper template that removes all of this tedious/error prone editing
Going to cut our diagram creation time drastically, some massive ones can take 4 days to make due to all the manual positioning/typing of all these elements - should be able get a massive diagram done in a day tops.
This template I'm making is nowhere near Visio Consultant Developer standards, however it is by far a lot better than the current way of "copy this base file and arrange all these separate elements that have no structure/smarts"

how
hmm, going to take a while to explain this properly - this whole thred has been leading up to this solution.
Basically to get the forumla in BOX!Sheet.5!Prop.SetNumberF to actually run.
I added a user field (to the document) called ForceUpdate with a value of =RAND()+1
Then added a DependsOn(TheDoc!User.ForceUpdate) to the formula in BOX!Sheet.5!Prop.SetNumberF
Thus whenever TheDoc!User.ForeceUpdate changes it has to execute the BOX!Sheet.5!Prop.SetNumberF formula too (this is once every minute according to the docs on RAND)

It would be nice if there were a less resource intense way to do this, however I'm not going to use VBA so I'm limited in what automatic/events I can use - perhaps someone will see this and let me know of a less heavy handed way to get this to do what i want.

Only took me 5months to get my solution... good thing I had other work todo