Conditional Formatting Visio 2007

[cpsriskanalyst]

Hey all, I think this would be simple but I am having difficulties.  I have an org chart with many people in it and for one of the Shape Data fields, which i think is the same as a custom properties field, I have listed a state for each person, there are a total of 4 states and I would like to be able to color code them all.  I went into the ShapeSheet window and tried this formula for FillForegnd =lookup(Shape Data.State,"CA;IL;FL;VA") and it says there is an error in formula.  I am entirely new to visio so I just sort of pieced that formula together from what I was able to pick up from other forums but obviously it doesnt work.  (The name of the shape data field is "State")

also is there a way I can put this formula in once and have it apply to all the shapes on the sheet instead of imputting the formula into each shape individually?

Thanks so much

[cpsriskanalyst]

Ok I got the formula to work using =Lookup(Prop.State,"CA;FL;IL;VA")

When I put this formula into the fillforegnd cell of the shapesheet of one particular shape on my chart it works great.The problem is I have many shapes on my sheet and would like this formula to apply to all of them, how can I go about doing this?

Thanks for your help!

[Paul Herber]

Ok I got the formula to work using =Lookup(Prop.State,"CA;FL;IL;VA")

When I put this formula into the fillforegnd cell of the shapesheet of one particular shape on my chart it works great.The problem is I have many shapes on my sheet and would like this formula to apply to all of them, how can I go about doing this?

Thanks for your help!

I was just about to post a response to you with the above but you beat me to it.
To update all your shapes you need to edit the Document stencil
menu -> File -> Shapes -> Show Document Stencil
There you will find your shape, double click to edit, select the shape, menu Window -> Shape ShapeSheet, put your formula in the appropriate cell and close the shape.
Hopefully it will ask if you want to update all instances of the shape.

[cpsriskanalyst]

WOOHOO! that worked perfectly, I'm thrilled!

Thanks a ton!!!

[cpsriskanalyst]

Ok, if you can answer this question you will be my hero.  I noticed the user.deltaY field in the user defined cells portion of the shapesheet.  I messed around with it and it does exactly what I want it to do, which is to assign the height spacing between a worker and his superior.  Now is there a way to make this dynamic like the color thing? 

I have created a Shape Data field named DeltaY, (so it looks like Prop.DeltaY in the shapesheet) which has a number associated with the spacing I want between that person and his direct superior.

So now I need a nifty formula to plug into the user.deltaY field (I think) to get this problem solved.

This is the final step to complete this silly project I've been working on for a week now, if you can come through for me this would be huge!

THANKS!

[cpsriskanalyst]

BLARGH!!  I'm so close, i was able to use that cool lookup formula and threw in a negative symbol in front of it for the deltaY field and it works great, as long as I insert the formula for one particular shape. 

The problem is when I try to modify the shape in the stencil like you said for the fillforegnd field and try to apply it to all shapes of that type, it doesnt work.  The formula isnt carried through to the other shapes....

Im not sure why this isnt working when it worked so well before...  any ideas?


Thanks again!

[Paul Herber]

Looks like modifying the Document Stencil shape for this cell won't work as it gets updated by Visio. However, there is another way.
menu Organisation Chart -> Change Spacing
select Custom and then press the Values button.
Otherwise you could write a macro to go through all the shapes and set the value to whatever you want.

[cpsriskanalyst]

Yeah the trouble is the spacing is different depending on if a staff member reports to a supervisor or a manager.  So I cant have just one unified spacing throughout.  I am completely unfamiliar with VBA code for visio operations, so is there a simple way to say in visual basic "select first shape, change the deltaY to this formula, select next shape" etc. etc.?

thanks again!

[Paul Herber]

Yes, quite easy, but it's gone midnight here local time and there are probably better VBA people here than me. If you get no other response before tomorrow then I'll have a look for you.

[Paul Herber]

Sub setDeltaY()
  Dim docObj As Visio.Document
  Dim pageIndex As Integer
  Dim pagObj As Visio.Page
  Dim shpIndex As Integer
  Dim shpObj As Visio.Shape
 
  Set docObj = ActiveDocument
  For pageIndex = 1 To docObj.Pages.Count
    Set pagObj = docObj.Pages.Item(pageIndex)
    For shpIndex = 1 To pagObj.Shapes.Count
      Set shpObj = pagObj.Shapes.Item(shpIndex)
      If shpObj.CellExists("User.DeltaY", 0) Then
        shpObj.Cells("User.DeltaY").Formula = "-50mm"
      End If
    Next shpIndex
  Next pageIndex
   
End Sub

[cpsriskanalyst]

wow, thats exactly what I needed.  I can't thank you enough! which is why i feel so bad bothering you for one more thing... I tried to place in where you put the "-50 mm" a formula of the variety, "-lookup(Prop.Sep,"1;2;3;4;5")" where Sep is a new Shape Data field I created with the appropriate deltaY for each person, however it gives me an error when it gets to that step. 

with quotation marks around the whole formula, it says "expected: End of statement" and highlights the second quotation mark(infront of the 1) and makes me change it.

without quotation marks it returns, "Sub or Function Not defined" when I actually run the macro.

Is there a different syntax to use for the lookup formula or a different formula I can put in so this will do what I want? or can you not put formulas into macros like that?

Really sorry to bug you again

THANKS!

[cpsriskanalyst]

nevermind, i inserted this for my formula and it worked great. 

= "-lookup(Prop.Sep, " & Chr$(34) & "1;2;3;4;5" & Chr$(34) & ")"


thanks again though for all your help, I don't know where i'd be without it!!!!


~G

[Visio Guy]

You can show Paul your appreciation by clicking on the beer icon under his name

[Visio Guy]

Another comment:

We've seen the formula: Lookup(Prop.State,"CA;FL;IL;VA")  for looking up the currently selected state.

Since there is a Prop.State Shape Data field, that in all likelihood is a drop-down list of four states, we can make this more efficient. Our Prop.State row should have formulas something like this:

Prop.State.Type = 1
Prop.State.Format = "CA;FL;IL;VA"
Prop.State.Value = "FL"

Note that .Format already has the list. We can reference that in other formulas, like in the LOOKUP expression:

LOOKUP( Prop.State, Prop.State.Format )

Since we are using the current state to index a color, we can combine the whole thing into one expression:

FillForegnd = INDEX( LOOKUP(  Prop.State, Prop.State.Format ), "1;2;3;4" )

This will yield the following:

California is white (1)
Florida is red (2)
Illinois is green (3)
Virginia is blue (4)

I've attached a shape configured this way that y'all can examine and play with (must be registered to download attachments)

Cheers,

Chris

[wapperdude]

Regarding thanking Paul...
Wouldn't that be an Ale icon?