Ok, so I have two boxes. Box 1 is red and box 2 is white.
When I input the letter "Y" into box 1, I would like it to turn green. If I input the letter "N" into box 1, I would like it to turn white.
If box one has the letter "Y" and has turned green, I would then like box 2 to turn red until text is entered and it too will then turn green.
It is for a form to indicate further information is required in the adjacent box if Y is inputted.
Hope that makes sense!
within a shape: Easy...for example
fillforegrnd = if(strsame(props.letter, "Y"),<green>, if(strsame(props.letter,"W",<Gray",) ///last clause means do nothing
Between shapes: Can be hard
- if shapes have fixed relationship (in a group, in a container, etc), can leverage above
- if shape dont have fixed relationship, then very difficult to do....visio does not have shape discovery
(one can no, without VBA, go find other shapes in the drawing via id or handle)
Thanks vojo.
Adding more than one IF statement is making my box either not change colour or change to black regardless of the colour I specify.
Combinations I've tried so far:-
=IF(STRSAME(Prop.letter,"Y"),3,1)=IF(STRSAME(Prop.letter,"N"),2,1)=IF(STRSAME(Prop.letter,""),2,1) - Stays white
=IF(STRSAME(SHAPETEXT(TheText),"N"),3,2)=IF(STRSAME(SHAPETEXT(TheText),"Y"),3,2) - White with no input, black with any text inserted
=IF(STRSAME(SHAPETEXT(TheText),"N"),2,3) - Red when "N" inputted but stays green at all other times. Would like a third option.
Any help would be much appreciated.
UPDATE...
I've sorted the three colour problem
=IF(STRSAME(SHAPETEXT(TheText),"Y"),3,IF(STRSAME(SHAPETEXT(TheText),"N"),1,IF(STRSAME(SHAPETEXT(TheText),""),2,1)))
Now just need to link the boxes so if box 1 has a "Y" and tuns green, box 2 needs to turn red.
It's not going to happen is it!!
Here's reference on formula structure within the shapesheet: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa200961(v=office.10) (https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa200961(v=office.10)) This shows how to reference cells in another shape...which is what you want to do.
To use this, you need to know the identity of the box1 shape, i.e., sheet.X, where X is a number. Assuming that box1 was the 1st shape on the page, it would be sheet.1. So, your box2 formula would be.. IF(STRSAME(sheet.1!SHAPETEXT(TheText),"Y"),<red>,<gray>), where you must provide the red and gray definitions.
Thanks wapperdude.
Unfortunately it keeps giving me an error from (TheText).
=IF(STRSAME(sheet.1060!SHAPETEXT(TheText),"Y"),2,3)
Any ideas?
Quote from: Crazy Squirrel on November 19, 2021, 10:59:07 AM
Any ideas?
IF(STRSAME(SHAPETEXT(sheet.1060!TheText),"Y"),2,3)Please read more (https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ff768760(v=office.14)) about cell references !
use lots of user cells
user cell 1 = if(<check1>, true, false)
user cell 2 = if (check2>), true, user.cell1)
user cell 3 = if(<check3>),true,user.cell2)
...
user cell N = if(<check n>,true,user.celln-1)
etc
easier to debug
Oops. Thanks Surrogate for catching the error.
Thanks Surrogate. I'd like to get a bit more technical!
Now that box 2 has turned red to indicate input is required, can I make it change to green once text has been entered? Possilbly a then or else funcuntion? (Still need the Y in box 1)
Quote from: Crazy Squirrel on November 30, 2021, 09:47:44 AMPossilbly a then or else funcuntion? (Still need the Y in box 1)
You can use multiple nested IFs (http://visguy.com/vgforum/index.php?topic=1882.msg8468#msg8468)...
My first "IF" seams to be overwriting the others. Could you have a look at it for me please?
=IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"y"),2,
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"n"),1,
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"Y"),2,
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"N"),1,
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"-"),1,
IF(STRSAME(SHAPETEXT(TheText),""),1,RGB(80,200,250)))))))
Quote from: Surrogate on April 08, 2021, 08:33:37 PMI am sorry, i am Russian.
I may have misunderstood the task!
Awesome Surrogate, thank you.
I didn't need the drop down on shape data but managed to get it working with what was entered. I'm sure you guys would have a more simpler way of doing this.
=IF(LEN(SHAPETEXT(TheText))>0,4),
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"y"),2,
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"n"),1,
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"Y"),2,
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"N"),1,
IF(STRSAME(SHAPETEXT(Sheet.1000!TheText),"-"),1,1))))))
For your scenario, the 1st IF would be the final result, and then work backwards in priority. Basically, reverse your order.
If there is a long concatenation, I would recommend using Visio's suggested method of putting each IF in a separate User entry. It really helps for debugging and understanding, especially at a much later date. Then, your construct becomes merely creating a conditional statement using the User row results.