Author Topic: Conditional Formatting Visio 2007 (Read 22921 times)

cpsriskanalyst · « **on:** July 28, 2008, 02:03:55 PM »

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 · « **Reply #1 on:** July 28, 2008, 04:42:03 PM »

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 · « **Reply #2 on:** July 28, 2008, 04:55:50 PM »

Quote from: cpsriskanalyst on July 28, 2008, 04:42:03 PM

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 · « **Reply #3 on:** July 28, 2008, 05:16:25 PM »

WOOHOO! that worked perfectly, I'm thrilled!

Thanks a ton!!!

cpsriskanalyst · « **Reply #4 on:** July 28, 2008, 05:30:10 PM »

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 · « **Reply #5 on:** July 28, 2008, 05:41:46 PM »

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 · « **Reply #6 on:** July 28, 2008, 06:08:25 PM »

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 · « **Reply #7 on:** July 28, 2008, 06:18:04 PM »

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 · « **Reply #8 on:** July 28, 2008, 06:29:54 PM »

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 · « **Reply #9 on:** July 29, 2008, 08:16:33 AM »

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 · « **Reply #10 on:** July 29, 2008, 10:16:34 AM »

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 · « **Reply #11 on:** July 29, 2008, 12:59:11 PM »

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 · « **Reply #12 on:** July 29, 2008, 06:15:09 PM »

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

Visio Guy · « **Reply #13 on:** July 29, 2008, 06:27:34 PM »

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 · « **Reply #14 on:** July 29, 2008, 06:54:04 PM »

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

Visio Guy