Folks:
The question of sharing data between shapes has come up many times, such as two shapes sharing the same text. But what about going beyond text? Take wireframes, for example. You often need to duplicate the same shape with the same text on multiple pages so that if you changed the text of one, the text of the others would follow suit. But there’s also a need for a shape to always be at the same location on multiple pages such that if you moved any one, the others would also move; and if you changed the size of any of them, the change would be reflected in the others. The same is true for many other attributes as well. SETATREF certainly does the trick – unless you clobber or delete the shape that’s the source of the value! Because then all the shapes that SETATREFed to the deleted shape would be left adrift and unconnected. And that’s Bad.
The inspiration for a solution came from quantum mechanics, of all things, specifically quantum entanglement where a change of state in one particle is reflected instantaneously in another particle, irrespective of distance. As it turns out, the same principles can be applied to the Visio world. I call the solution Quantum Entanglement for Visio Shapes, or simply Entanglement. It’s a 100% shapesheet solution, but it’s easier to set up and maintain via VBA.
Entanglement involves three steps:
1. The fundamental basis for the solution is to create some sort of unique identifier to define the entanglement. Call it an “Entanglement ID.” Once you have it, you can entangle a variety of attributes among any number of shapes on any number of pages. You can either create your own Entanglement ID manually, or use the UniqueID that every shape and page has. To my knowledge, you can only access the Visio-supplied UniqueID via VBA. Here’s the function to get the ID:
Public Function EntanglementGuid(TheShape as Shape) As String
EntanglementGuid = TheShape.UniqueID(visGetOrMakeGUID)
EntanglementGuid = Replace(EntanglementGuid, "{", "")
EntanglementGuid = Replace(EntanglementGuid, "}", "")
EntanglementGuid = Replace(EntanglementGuid, "-", "_")
End Function
The editing of the Entanglement guid is necessary, as will be seen below.
Regardless of the source of the Entanglement ID, whether manually-entered or UniqueID, it gets saved in the entangled shape’s shapesheet’s User section as the value of a cell named User.Guid. If the cell exists, the shape can be entangled; if not, then not. Two or more entangled shapes all share the same Entanglement ID regardless of whose UniqueID it may happen to be.
2. To entangle a particular cell of a shape or page, the actual desired value is stored in the User section of the
document’s shapesheet. The name of that user cell is a concatenation of the name of the cell being entangled (such as PinX, Height, etc.) plus the Entanglement ID, making a cell name such as “User.PinX_E7EA5991_D4FF_4AEF_84A6_D84F1ED9A59A”. Call it the “Entangled Cell Name.” (Note that the braces and dashes have been removed from the Visio UniqueID because they are not allowed to be used in a User cell name.)
3. In the entangled shape’s shapesheet, simply reference the document’s shapesheet cell value using the SETATREF function as follows:
=SETATREF(TheDoc!User.PinX_E7EA5991_D4FF_4AEF_84A6_D84F1ED9A59A)
That’s it. Any time the value of the entangled cell changes in the shape, it’s the value in the document’s spreadsheet that changes, and all other entangled shapes share in the changed value. Even better, you can delete the original shape and not affect any of the other shapes entangled with it. Best of all, it’s a 100% shapesheet solution that’s easy to set up. And unentangling a shape is trivial: just overtype the SETATREF with whatever unentangled value you like. If you want to re-entangle it, you still have the Entangle ID in the source or target shape’s User section as a reference.
One drawback to Visio Entanglement is that it relies on SETATREF, meaning it’s limited only to certain shapesheet cells, and the most-common negative impact is seen with a shape’s User cells. SETATREF does not work with User section cells. Fortunately, user cells can be entangled in the same manner as any other, except that any reference to that User cell name throughout the shapesheet must be replaced with the document’s Entangled Cell Name. That change can be done manually, or a short VBA loop can be written using the CellsSRC function to scan the shapesheet sections and replace the original User cell name with the document’s Entangled Cell Name.
Another drawback is with a shape’s text. Entangling text requires using the Text Fields shapesheet section, which is one that does not work with SETATREF. Here only VBA can help, specifically by adding an Actions row to trigger a macro to prompt for the new text, which the macro then stores in the document’s shapesheet’s entangled cell. Multiple Text Fields rows become problematic, but can still be entangled by using one document shapesheet row per Text Fields row.
Taking it all a step further, a form can be created to allow for turning on and off individual entanglements such that they entangle a shape’s position only, a shape’s size only, the text only, or any combination. Mix and match! (See sample form below.)
One annoyance is that too many entangled shapes tend to clog up the document’s shapesheet visually, and performance issues can start to set in when thousands of rows are used. But then, how often does one look at the document’s shapesheet, or entangle thousands of shapes? Besides me, that is?
I can’ t tell you how much time and trouble entangled shapes have eliminated for me, especially with creating wireframes where buttons and checkboxes need to be at the same location with the same topology on multiple pages. Entanglement has been a life saver. You can call me lazy, but as we all know: when it comes to most anything, laziness is a virtue.
- Ken