Help to show/hide Data Graphic text depending on shape data?

[Vendril]

Hi,

I have a floor plan with desks/offices on it showing names with data graphics.  I would like to hide the text on specific items if the text matches a number of text items ("Vacant", "Spare", "Not used" etc.) and show if it does not.  I am running into a couple initial issues and could use some feedback please.

I have located the Data Graphics property that show the text in the ShapeSheet (Miscellaneous > HideText) and have the following formula:

Code Select Expand

=IF(Prop.msvCalloutField.Value="Vacant",TRUE,FALSE)

The Formula in that Prop formula is the field from my dataset =Sheet.3176!Prop._VisDM_Primary_User (Sheet.3176 being that desk) and the Value for this particular shape is Vacant.

The first problem is that after closing the shapesheet the .value text in formula is removed and then doesn't work anymore. The second problem is that I have over 200 locations spread over 3 sheets that I will need to update and about 30 different text options I want to include into the "hide if true" section.

Is there a way using VB to accomplish my goal, perhaps on a refresh?  The maps are published on SharePoint for our end users and currently I am manually making the shapedate = no shapedata.... which is far from ideal.

Thanks.

[FlowerGirl]

Connect your data to Excel, then control your inputs from there.

FlowerGirl.

[wapperdude]

First, to compare text you need strsame function...can't compare text directly as you show. 

Establishing a single if compare to look at 30 some cases is a bit formidable.  It would be easier to have each case push into the show/hide cell, rather than have the show/hide pull from all 30 qualifiers in a single statement.  The advantage of pushing, you can easily add or remove a single entry to modify the behavior.  Much easier than editting a long, complex formula.

Wapperdude

[Vendril]

Thanks. 

@ Flowergirl - unfortunately the data is already in a MS SharePoint List, along with a heap of other data associated to the office.  I have modified my External Data connection to not pull these extra columns.

@ wapperdude - the strsame works.  I am not sure what you mean by push/pulling the formula.  Would it be possible to add a variable in the formula where the "vacant" is and then have an array of all my other text inputs to match it in VBA.  Alternatively I guess I could just add another column into my dataset and try filter it before it hits Visio (much like Flowergirl recommends).

Thanks

[wapperdude]

By pushing / pulling I mean...perhaps, an example...think of a fork.  At the points of each tine is a cell, and then one more cell at end of handle.  Each "tine" cell would push a "result" into the handle cell.  On the other hand, the "handle" cell would pull information from each "tine" cell.

Hope that makes sense.