[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

MS Visio: How to synchronize multiple cells beetween beetween multiple master instances in different pages?

I have build a system in Visio 2013 back ago, and now my client wants to synchronyze multiple cells beetween multiple master instances in different pages. Each page has 100+ master instances, each master has 10+ cells to be synchronized. So in each master's cell I entered a formula like this:

and so on.

that works well to the synchronize purposes, but it will take a LOT of time to synchronize all the master instances.

I want to take the approach to make the "MasterInstanceID.000001" a variable in the formula, related to a User Defined cell, so I only  have to change the 000001 number once in each master instance, and not in each shapesheet cell, when I have had applied the changes in the master, and all the master instances being actualized. I can't use the EVALTEXT() function, because I'm not allowed to change the text displayed by the master instance.

Any ideas? Best regards.
Gustavo Garcia
Gustavo Garcia
  • 4
  • 3
2 Solutions
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Unless I've missed what you're trying to accomplish, I think there is a much more efficient way to achieve the desired result. Am I correct that what you're trying to do is to change one value and then have all instances of that master, regardless of the page on which they are located, reflect the new value?

If so, try this:
  1. In the document ShapeSheet for the stencil, create a user cell, let's call it User.MasterValue1
  2. In each master in the stencil, create a Shape Data cell, let's call is Prop.MasterValue1, and set its value to =TheDoc!User.MasterValue1

When you drag a master onto a page, it's Prop.MasterValue1 cell will automatically reflect the value in the document ShapeSheet. When you change the value in the doc sheet, all instances that refer to it will be updated automatically.

Re #1 above, to get to the ShapeSheet for the stencil document:
  • Right-click the header bar for the stencil and then select Drawing Explorer Window
  • In the Drawing Explorer window, right-click Stenci3 (or whatever name appears at the top of the window), and then select Show ShapeSheet

NOTE: You can create User.MasterValue1 in the doc sheet for the actual diagram, but it isn't necessary. The first time you drag one of your masters onto any page in the document, Visio will create a doc sheet cell for you because the master contains a formula that refers to the doc sheet.
Gustavo GarciaAuthor Commented:
Hi Scott! Thank you for your answer, but that's not what I'm trying to accomplish.

I'm trying to synchronize a group of cells in several instances of master 1,  let's call it a SourceMaster. The instances are already dragged to page 1 of a Visio document, and with useful data, with a other group of cells in other several instances of master 2, let's call it a RecipientMaster. RecipientMaster instances are already dragged and placed in page 2 of the same document. I want to synchronize cells of a particular SourceMaster instance, with a particular RecipientMaster instance.

Since there are several instances to synchronize and each instance is already placed, I want to explore the posibility to make the unique ID number of SourceMaster instances reference a "variable" somehow in the synchronization formulas in RecipientMaster instances cells, so once I've edited the RecipientMaster, and updated all its instances, I only have to change the referenced ID number of the SourceMaster instance,  to synchronize all the cells of the instance with synchronization formulas. As far as I've tried, I haven't  found a method to accomplish this. Please help!
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
As a general answer, you can't use variables in ShapeSheet formula. However, there is probably still a way to accomplish what you want...

Are your relationships one to many? Or one to one? For example in the diagram below do the cells in A need to be synced with W, X, Y, and Z? Or A to W, B to X, etc.?shape exampleEither way, it's likely that using doc sheet or page sheet cells as the communication vehicle might still work. For example, the SETATREF() formula in the source masters could reference a doc sheet cell instead of a cell in a specific shape, and then the cells in the recipient masters could simply be equated to those same doc sheet cells. That way any time you make a change to a value in a source instance, the corresponding doc sheet cell will get updated, which will automatically change the value in all corresponding recipient instance cells.

Am I getting closer?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Gustavo GarciaAuthor Commented:
Hi Scott, thank you for your answer. You're getting a step closer, but not still there yet. My relationships are one cell to one cell for different master instances. Using a doc or page user cell to sync each cell in this particular case is still impractical, because I've in this particular system around 700 master instances and I need to synchronice about 5 to 7 cells in each instance. Making that amount of user cells references in a page or doc would be still too data capture intensive.

I already made a report with the sheetID of each master instance I have to sync, and they wont change in the future, that means that the cell's relationships are static and well known. Right now, with the approach you suggested I've been playing with some user cells  and tunneling techniques, like triple """ """ quotation marks, trying to make the sheetID of master instances to sync a "fixed" variable for the particular master instance, but still I had no luck.
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Knowing that you have several thousand cells to sync certainly changes the options for what you're trying to do.

With that volume of relationships, and if the relationships don't change, why not write VBA code to build all of the formulas for you? That would eliminate thousands of SETATREF() formulas and the attendant performance hit you've described.

It would take a little while to put the code together, but then it would be easy to tweak it as need to finalize the formula. And making changes in the future would be dramatically simpler as well.

Without knowing what your Excel report contains, but assuming that you've got all of the appropriate shape IDs and cell names, I would suggest writing code to 1) read relationship pair data from the Excel workbook, and then write formulas into shapes.
Gustavo GarciaAuthor Commented:
Hi Scott! Thank you for your comment. I was trying to avoid a solution that implies to write a extra code just to do that, but it seems there are no other way. I'm not well versed in Automation yet. Can you point me to the procedures used to write a formula in a cell from excel?

Best regards
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
I understand about not wanting to write code if you don't need to, but I think it may save you a lot of work in the long run. Also, keep in mind that the diagrams you distribute won't necessarily need to include the code -- from what you've described, the code should only be necessary for setting the formulae into the shapes. After that the diagram should be able to live on its own.

Two suggestions for getting started:
1) Fellow Visio MVP John Goldsmith posted an intro for Visio developers on his excellent blog.
2) I created a three-part series called "Becoming a Visio Power User" for Microsoft Virtual Academy. Part 3 is all about getting started writing code. It contains a lot of small VBA examples that should be helpful.

Beyond that, keep posting questions here.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now