Controlling Shapes in Visio Using Drop Down Lists
Part four of a five-part series of blogs

Microsoft Visio lets you create far more than simple static diagrams. In this blog series we show you how to use Shape Data and the ShapeSheet to create a dynamic shape that will change its dimensions when you choose a value from a drop down list.

  1. Controlling Shape Size in Visio Using Drop Down Lists
  2. Creating Drop Down Lists in Visio Shape Data
  3. Calculating Shape Properties in the ShapeSheet
  4. Using IF Functions and Comparing Text in Visio (this blog)
  5. Protecting the Contents of the ShapeSheet

Posted by Andrew Gould on 21 June 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using IF Functions and Comparing Text in Visio

To make our shape work we need a formula that can test what value has been selected in the drop down list and set the height of the shape appropriately.  To do this we're going to use the IF function.

What Size Should our Shape Be?

Before jumping in to create a complex calculation we should decide what height our shape should be for each size that we can choose.  To keep things simple for now we're only going to change the shape's height according to these rules:

Size selected Height in mm
Small 2000
Medium 3000
Large 4000

Now that we know the dimensions we want to achieve we can plug them in to an IF function.

What Does the IF Function Do?

The IF function tests whether a condition is true and provides one answer if it is and a different answer if it isn't.  The basic syntax of the IF function looks like this:

IF(Condition to test, Answer if true, Answer if false)

So for our example we could first of all ask if the selected size was Small and if so make the height 2000 mm, otherwise make the height 3000 mm.  You can think of the job of the IF function as a flowchart:

Flowchart for IF

You can only end up with one of the two possible answers.

Our example is a little more complicated than that as we have three possible answers.  Fortunately, we can nest functions, or put one function inside another, to create more complex calculations.  The syntax for a pair of nested IF functions looks like this:

IF(Condition to test, Answer if true, IF(Condition to test, Answer if true, Answer if false))

Or, in flowchart terms:

Double IF

The second IF function forms the Value if false argument for the first IF function.

That takes care of the logic of what we're trying to achieve.  The next step is to find out how to compare one piece of text against another piece of text in Visio.

Nesting IF functions is a rough and ready way to test for multiple conditions and works well in this small example.  Although you can nest more IF functions together things rapidly become messy when you do and it's probably worth investigating other functions such as INDEX and LOOKUP instead.

Comparing Strings of Text in Visio

On the training course on which I was attempting to demonstrate this technique, this is the point at which I became unstuck.  In every other Microsoft application you can compare one string of text directly against another.  However, in Visio it turns out that you have to use an obscure function to do the same thing!  The function is called STRSAME and its syntax is shown below:

STRSAME(First bit of text, Second bit of text, Ignore case?)

So when we want to compare the value we have selected in the drop down list against the word Small or the word Medium we have to use the STRSAME function.  This means that we have to nest this function inside our IF function!

Putting it all Together

To build up this calculation follow these steps:

  1. In the ShapeSheet, click into the Height cell and type in the following:
=IF(STRSAME(
Starting the formula

Here we're typing in our function names rather than using the menu, but you could always choose Insert > Function... if you prefer.

 
  1. Now you need to click on the cell in the ShapeSheet that refers to the value selected in the drop down list.  Look for the section called Shape Data and find the property you created earlier.  Now look for the Value column and click on that cell.
Selecting a custom property

Clicking the cell shown here reads the value Prop.Row_1 into the formula.

  1. Now type in a comma, followed by the word Small in a set of double quotes so that the formula looks like this:
=IF(STRSAME(Prop.Row_1,"Small"
  1. The next step is to say whether the text comparison should be case sensitive or not.  To make it case sensitive add a comma, the word FALSE and finally a close round bracket.  Use the word TRUE in place of FALSE to make the comparison case insensitive.  For this example it doesn't matter too much.
=IF(STRSAME(Prop.Row_1,"Small",TRUE)
  1. Now type in another comma followed by the height and units you want the shape to be if someone has selected Small for the size.  We want our shape to be 2000mm tall, so:
=IF(STRSAME(Prop.Row_1,"Small",TRUE),2000mm
  1. Now we need to start building the second IF function, so type in a comma followed by IF and open another set of brackets.
=IF(STRSAME(Prop.Row_1,"Small",TRUE),2000mm,IF(
  1. This time we want to test if the selected size is Medium so we need another STRSAME function:
=IF(STRSAME(Prop.Row_1,"Small",TRUE),2000mm,IF( STRSAME(Prop.Row_1,"Medium",TRUE)
  1. If the size is Medium we want the shape height to be 3000mm, so add a comma followed by that value.
=IF(STRSAME(Prop.Row_1,"Small",TRUE),2000mm,IF( STRSAME(Prop.Row_1,"Medium",TRUE),3000mm
  1. Finally, if the size selected isn't Small or Medium then it must be Large so we want the height to be 4000mm.  Add another comma followed by this value and close two sets of round brackets.
=IF(STRSAME(Prop.Row_1,"Small",TRUE),2000mm,IF( STRSAME(Prop.Row_1,"Medium",TRUE),3000mm,4000mm))
  1. All you need to do now is press Enter and hope that you don't see the message shown below:
Helpful message

It's not the most helpful message! If you do see this dialog box, click OK and check that all the commas, quotes and brackets are in the right place. Alternatively, you could just copy the text from this page and paste it into your formula.

 

If everything worked your formula should look like this:

Completed formula

It's not elegant, but it will do the job.

Testing that it Works

To check that your formula works you can go back to the diagram and try changing the size on the Shape Data window.

Choosing a size Shape with changed height
Choose a different size from the list... ...and the shape should change height.

What's Next?

Before we go patting ourselves on the back there are a few things that we could do to make our shape work better.  The most important thing to do is make sure that the formula we have just created isn't accidentally deleted.  Read on to find out how to protect your precious calculation.

This blog has 0 threads Add post