Display Page Number Based on Page Name, Defined in Shape Data

Started by mitros, March 14, 2020, 12:07:49 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

mitros

Hello to all. So far, I used Visio only as sketching tool, so I'm not very familiar to custom formulas, VBA etc...
I need some help to implement custom cross-referencing. So far, I have created this master with some SHAPE DATA. And, for example, I have two pages, 1.634 and 1.635. If I fill the DATA with 1.635, I want to automatically get the number 2 below it.

If I enter the custom formula in field Prop.goes_to_page like this:
=Pages[1.635]ThePage!User.pagenumber+DEPENDSON(NOW()) it is OK, I get the number 2. , atached figure "visio shape"



Just to say that I have created the user cell for page 1.635, User.pagenumber , please see atached figure "visio page"


I hope someone is willing to help me with this.

Thank you in advance

Surrogate

You want select some PageName in dropdown list in ShapeData window and get value that stored in some cell in page with selected name?

mitros

Thank you for reply,

Not actually dropdown. I created this shape (master) with 4 data fields.
1. KONEKTOR
2. IDE NA CRTEZ
3. IDE NA STRANU
4. IDE NA KONEKTOR

So when I place this shape on page  1.634 and fill "IDE NA CRTEZ" field (in translation-goes to drawing) in shape data task pane with 1.635, I want to get the value "2" in field "IDE NA STRANU" (in translation-goes to page), because page 1.635 holds number 2.

As I already mentioned, if I enter the custom formula in shape sheet of this shape, in field Prop.goes_to_page like this:
=Pages[1.635]]ThePage!User.pagenumber+DEPENDSON(NOW()) it is OK, I get the number 2 in Prop.goes_to_page. So, its working when adressing page directly.

How can I rewrite this formula to use "Prop.goes_to_drawing" field of the same shape instead of fixed value 1.635?

I tried =Pages[Prop.goes_to_drawing]]ThePage!User.pagenumber+DEPENDSON(NOW()), but it is not working.

I hope it is easier to understand now, it's little complicated.

Thank you in advance

Surrogate

 Yes, i understand! Few years ago i did solution with using SETF function for create formula for another pagename

Visio Guy

Dobar Dan Mitros,

This can be done with a very strange technique. If you build a formula in the ShapeSheet using string parts, it doesn't work. So, for example:

User.Test = "Pages[" & "1.634" & "]!ThePage!User.PageNumber"

Visio will evaluate that cell as a string:

Pages[1.634]!ThePage!User.PageNumber

Yes, it happens to look like a valid formula, but it won't actually be a formula. It is actually just the string "Pages[1.634]!ThePage!User.PageNumber", so it won't work for your case.

However, the EvalText formula will evaluate text that is on a shape. If that text happens to be a valid ShapeSheet formula, Visio will try and evaluate the formula! See these two articles for examples of this concept in action:


So I've put this into action for your situation. The attached VSDX file actually works. See the illustration below for a breakdown of the structure. I'll outline it here with some bullet points:


  • All pages except "Nemam" have the User.PageNumber cell.
  • You type in the text for the group. For example "Ceteri"
  • The red-text at the top is a subshape of this grouped shape. It references the group's text, and has that inserted as a text field (Ctrl + F9 is the short cut for Insert > Field). There is additional text wrapped around it to complete the correct syntax for the formula. This subshape then evaluates it's own text in User.EvalText
  • The bottom shape (with the "4") references User.EvalText in the red-text shape. It has the result inserted into it's text.
  • If the text in the red-text shape doesn't evaluate to anything, you will get 0. That means it has not ShapeSheet meaning to Visio.
  • To finish this shape, all we need to do is make the red-text shape invisible. We can do this by setting HideText = TRUE and Geometry1.NoShow=TRUE

For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

mitros

Thank you both!

These post were very helpful, but I would like to solve this problem using shape data, not shape text (but if I stuck with this I'll do like VISIO GUY explained). The reason for that is that with data fields it is easier to enter the values for someone who isn't used to Visio environment . I tried to use SETF with no luck. I find it very strange that indirect referencing of shapedata fields isn't possible because when I use:
=Pages[1.634]!ThePages!User.pagenumber, everything works, and when I want to replace that fixed value with value stored in data field next to it (goes_to_drawing), that cannot be done. I posted example with the shape, I'm not sure do I have to upload the stencil where the shape is or not.

Thank you both again!

Visio Guy

The solution isn't about using text. Text is the necessary trick to get this to work.

In other words: "In order to build a formula from bits of text, it HAS to be in some shape's text somewhere".

That red-text in my example should be hidden. The user should never touch that shape, nor see it.

You could hook it up so that a user types "Ceteri" in a Shape Data field, and the system would still work the same. It's a bit hard to explain.
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

mitros

Of course, but on that shape there should be 3 different text fields to enter, and when those field are subshapes of main shape I suppose it's more time consuming to enter that fields than in data fields. Just that. Thanks again, I'll try to make something with all these advices...

Best regards

wapperdude

Played around with your shape.  There is another work-around to the text/string issue that VisioGuy points out.  See attached.
It requires adding 1 row to the User Defined section, and entering the SETF/Getref construct.  This seems to work, but may not be the "most proper" solution.

Using SETF within the target cell doesn't work... to my knowledge.  Also, the Dependson now() is unnecessary.

Visio 2019 Pro

mitros

Thank you wapperdude,

I'm not sure I know what did you do with this shape but this is exactly what I want!
Only thing I changed is pagenumber formula with DEPENDSON because page number are not working correctly without it.
I'll test it today, but I believe that's it!

Thank you ALL again!

Visio Guy

Guys,

The thing I don't like about SETF is that if SETF fails, it doesn't do anything. So you don't get any changes.

In the image below, you can see I've typed in "Bob", which isn't a page name. The last good page name that I typed in still displays. So now, someone might think that Bob is page 3, which it (he) isn't.

With my method, the EVALTEXT(TheText) evaluates to 0 if the formula is "bad". So you will see a change when there is a problem.
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

Visio Guy

Ok, I've found a work-around for when the formula set fails: add two SETFs. The first clears the result, the second attempts to set the "built" formula, so for example:

User.Setter =
SETF(GetRef(Prop.goes_to_page),"""---""")
+SETF(GetRef(Prop.goes_to_page),"Pages["&Prop.goes_to_drawing&"]!ThePage!User.pagenumber")

What happens is this: if the "Pages[blah blah" portion fails because the page name is invalid, you will get "---" in Prop.goes_to_page.

If the second part is successful, it overrides the "---" with the formula, and references the page's User.PageNumber cell correctly.
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010