Visio Guy

Visio Discussions => ShapeSheet & Smart Shapes => Topic started by: Michelle on December 22, 2008, 09:56:24 AM

Title: Why won't this work?
Post by: Michelle on December 22, 2008, 09:56:24 AM
I have a box in my drawing that is to display text based on the choices a user makes.  The user is to choose from a list in the Shape data window.  Their choices are: On Left Wall, 4 in, 8 in, (and so on), On Right Wall.  In the text fields section of the shape sheet for the box where I want the text to appear I have this:

=(IF(OR(STRSAME(Pages[Page-1]!Excel.8!Prop.ConduitLeftRight1,("On Left Wall")),STRSAME(REF(),("On Right Wall"))),"@","0 u"))

If I replace the Ref() after the second STRSAME with "Pages[Page-1]!Sheet.8!Prop.ConduitLeftRight1" like I want, I get an error.  Of course leaving it like it is only produces a blank box if "On Right Wall" is chosen. 

I'm thinking maybe I have a comma or parenthasees wrong?  But I can't figure it out...

Thanks to all who help, and Merry Christmas!

Title: Re: Why won't this work?
Post by: joemako on December 22, 2008, 12:02:47 PM
Not sure if I fully understand what you are looking to do, but would this statement accomplish what you are looking to do?:

=(IF(STRSAME(Pages[Page-1]!Excel.8!Prop.ConduitLeftRight1,("On Left Wall")),"@",IF(STRSAME(Pages[Page-1]!Sheet.8!Prop.ConduitLeftRight1,("On Right Wall")),"0 u","Neither")))

To me, it seems strange that you have one referring to "Excel.8" in one and the other "Sheet.8". Maybe that is the source of your error, because there was nothing wrong with your parentheses.

If you could explain fully each of the cases you want to capture and their results, I could help you craft the proper IF/OR statement you need.
Title: Re: Why won't this work?
Post by: Michelle on December 22, 2008, 01:18:44 PM
The users will make a choice from a list in the Shape data box.  Their choices are:  "On Left Wall, 4 in, 8 in, 1 ft 0 in, 1 ft 4 in, etc with many ft-in choices, On Right Wall"  I want a box on the second page to textually show the choice they made.  I'd like the box to say "On Left Wall" or "2 ft 4 in" whatever it is they choose.  If I just simply fornat the text to "@" the dimensions don't show up right.  They come up like this: "1 ft 8.0000 in"  I don't want all those decimals.  So I thought I could create the IF/OR statement to tell Visio to show the "On Left Wall" and "On Right Wall" choices just like they are written (the "@" in the formula) and also to format any other choices (i.e. the numeric ones) to show feet & inches without the decimals (The "0 u" in the formula). 

Perhaps I tried to get too complicated....  How can I just make visio show any one of the user's choices and have the numeric ones show up correctly without the decimals? I tried to change my format picture at the end and was playing with options like "# u" instead of "0 u", etc. but pretty much ANY change I made to the string resulted in an error.  I'm lost.

my Sheet.8 is named Excel, when I enter Sheet.8! into any formula, Visio changes the "sheet" with the name "excel" when I hit enter.

btw, the example you show, I do not understand the "Neither" at the end?  I think that throws the formula way off, when I use yours, I get a crazy number: "19001900iA121900r" which is of course not one of the choices the user has! 

Thanks for looking...

Title: Re: Why won't this work?
Post by: Michelle on December 22, 2008, 01:40:07 PM
I am getting closer, I think  ::)

I tried this:
=(IF(OR(STRSAME(Pages[Page-1]!Excel.8!Prop.ConduitLeftRight1,("On Left Wall"),STRSAME(Pages[Page-1]!Excel.8!Prop.ConduitLeftRight1,("On Right Wall")))),"@","0 u"))

And I get the "On Left Wall" to show if chosen, and the dimensions show correctly if chosen... but if the user chooses "On Right Wall" the box on page two is blank...

Not sure if I'm getting closer, or just driving myself crazy!

Title: Re: Why won't this work?
Post by: joemako on December 22, 2008, 03:08:42 PM
Thank you for the additional info, I am getting closer to understanding to what you are looking to do.

What are the shapes you are working with (the one that you change the value on, and the one that displays the value)? Can you upload a Visio with just those two shapes, or your whole file if you can?

By "a list in the Shape data window", you mean a custom property, I think. Is that correct?

See the Visio I attached, if you change the value in the "Custom Properties" dialog for the shape on Page-2, the text on the first page changes to the value of the selection.

Look at the section Text Fields in the shapesheet of the text box on Page-1. Two import fields, Format and Value.

generally, you would want the Format to be set to "=FIELDPICTURE(0)", you can see a list of valuse and what they translate to at (

I set the value field to "=Pages[Page-2]!Sheet.1!Prop.ConduitLeftRight1"

Page-2 - the NameU of the page

Sheet.1 - the name of the shape (or known as sheet for shapesheet)

Prop - Custom Properties section

ConduitLeftRight1 - the name of the row in the Custom Properties section

I think I need to understand the shapes you are working with fully help.

some other resources to look into:

for creating references see:

for a discussion on local name and a universal name see:

Title: Re: Why won't this work?
Post by: Michelle on December 23, 2008, 10:27:44 AM
Well, I don't know why I couldn't get things to work before, but I seem to have it now.  I discovered that if I entered only half of the formula, and tried to go in after and add the second half it wouldn't work.  Visio changes my "Sheet.8!" to "Excel.8!", but if the formula has "Excel.8!" in one area and "Sheet.8!" in antoher, it gets confused and kicks an error message.  This time I made sure I did the entire formula at one time in the way I *thought* it should work:

=(IF(OR(STRSAME(Pages[Page-1]!Excel.8!Prop.ConduitLeftRight1,("On Left Wall")),STRSAME(Pages[Page-1]!Excel.8!Prop.ConduitLeftRight1,("On Right Wall"))),"@","0 u"))

And it did! 

Thanks for your help, Joe... I guess just working through this one was helpful for me. 
Merry Christmas  :)