Refreshing shape colors in Visio 2002 from a database? 1

[AricWentz]

I'm running an inventory management program through Visio 2002 whereby I have a drawing of a storage bldg. with pallet racking inside. Each racking "slot" is represented by a rectangle which in turn links to a record in an Access table. I've been successful in linking text, dates, and numbers to each shape's custom properties and subsequently fields in the shapes. However, I'd like to add a feature that allows me to code a record to refresh the color of the shape. For instance, I'd like to add a column to my table titled "Color Code" and when I key a record, I could enter a "Y" and refresh the database. Ideally, the shape linked to that record would become yellow. If I were to delete the "Y" from the record and refresh again, the shape would return to its default color. Is this possible? I've explored all I can think of with no success, but I'm also very new to the program.

 

[VisioMVP]

Here is an explanation of basing the colour of a shape on a property of the shape.



Put this formula in the FillForegnd cell.

=Lookup(Prop.Status,"Black;White;Red;Green;Blue;Yellow&quot

(A formula error will occur if the custom property, in this case Status, does not already exist.) Lookup will match the value of the custom property Status to the list shown above. The value returned by Lookup will be a number starting with 0 which corresponds to the position of the matched value in the list. The list shows Visio's numbering sequence for the colours.

If the property choices are "Warning", "Caution" and "Clear" with corresponding colours. In the Lookup formula, replace the words "Red", "Yellow" and "Green" with "Warning", "Caution" and "Clear". You must leave the other items in as place holders to get the correct colour position. The final formula would be:

=Lookup(Prop.Status,"Black;White;Warning;Clear;Blue;Caution&quot

John... Visio MVP

 

[AricWentz]

I've followed the above procedure and the color updating works perfectly. However, I'm wondering if anyone knows of a way I can insert this formula into the fillforegnd cell of everyone of my shapes. I have 192 rectangles on my drawing and I'd prefer not to have to open the shape sheet for each one and insert the formula. Is there a quicker way I can do it for all of the shapes?

 

[Chats]

I found this thread while searching the archives for a problem I am working on.

This has got me a fair way to where I want to be - but AricWentz's final question was never answered - and I really need to know the answer to this myself now.

Is there a way of refreshing the properties of multiple shapes in a single action? I have a link to a database where I am making a number of changes in the database, and then would like all of these changes to be updated onto the visio diagram without me having to click each individual shape.

Any help would be very much appreciated.

Thanks
Ade

 

[VisioMVP]

There is no easy way to do this, but it can be done using VBA. An alternate would be to check out the 3rd party suppliers. I believe Visimation has a tool called VisiClone that may do what you want.

The VBA code to modify the shapes is not very difficult.

John... Visio MVP

 

[Chats]

John,

Thanks - I will use VBA. I have a fair amount of experience of VBA in Excel / Access, but none in Visio.

Its a beginners question, but could you suggest a code line which would do the equivalent of "refresh shape properties" from the right click menu (after database connection has been established). Presumably something like

object.refresh -- but correct object names!

I can write the rest of the code from what I know of VBA, but don't know the object model yet!

Thanks for your help,

Ade