Building dynamic hyperlinks

John Goldsmith's profile image

John Goldsmith

Header

Moving on from the previous post, where I looked at the basics of hyperlinks in Visio, today I’m going to consider how to build dynamic hyperlinks using the ShapeSheet.

I’ve thought up a couple of slightly contrived examples to demonstrate how this functionality might be applied but there are no doubt many other applications out there and I’d love to hear if you have other examples...

Links based on time

For the first example I wanted to find a site that has varying URL’s based on time and of course this is exactly the way in which blogs are structured.  So I’ve picked Scott Guthrie’s blog as he’s been posting to his current blog since 2003 and is pretty regular to boot.

The basic idea here is to add a link to a timeline milestone shape so that the link changes as it moved up and down the timeline.  Like most blogs, this one has a monthly archive and is structured as follows:

scottguthrieURL

…so really you’re just concatenating a string based on other ShapeSheet cells.

Timeline walkthrough

  1. Create a new timeline document (File / New / Schedule / Timeline)
  2. Drag a new Cylindrical timeline master onto the page
  3. In the following Configure Timeline dialog set the Start and Finish time periods to 01/01/2003 and 31/12/2009 and Time scale to ‘Months’
  4. Drag a Cylindrical milestone master onto the page and then delete it.  (This step adds a milestone master to the Document Stencil, which you can edit.  All subsequent milestone shape instance will then be based on this edited master.)
  5. Make the Document Stencil visible by clicking File / Shapes / Show Document Stencil
  6. Double click the Cylindrical milestone master in the Document Stencil to open it for editing
  7. Once in master editing mode, right click on the milestone shape and select Show ShapeSheet (if that’s not available have a look at this post on Visio developer mode)
  8. Right-click anywhere in the ShapeSheet and select Insert Section.  From the Insert Section dialog, check the Hyperlinks box and click OK
  9. Right-click anywhere in the User-defined cells section and click Insert Row and rename the row Year.
  10. Repeat the above item three times to add new User rows named Month, LinkBase and Label.

OK, you should now be looking at the milestone master’s ShapeSheet with all the relevant cells, so just copy out the formulae as per the image below, close the ShapeSheet, the master editing window and accept the changes.

ShapeSheet

The main thing to notice here is that the address for the hyperlink is made up of a link base (ie the part that doesn’t change) and then has the year and month tacked on the end.  The Month and Year User cells simply reference the date cell (User.visMilestoneDate) that the Timeline addon updates every time the milestone’s position changes.

Note that the only change I’ve made to the Shape Data section is to add a reference to our new User.Label cell so that this is reflected in the shape’s text.  However, there’s one more change to make.  By default, when the shape is dropped onto the page, the Timeline addon writes to two cells (that effect this solution) – User.visMilestoneDate and Prop.visName.Value.  The former gets updated with a date value of its position on the timeline and the latter with a string that’s displayed in the Description field of the ‘on drop’ dialog.  This last action overwrites our User.Label formula and so it won’t be updated the next time the shape gets moved.

To fix this you need to add another User cell that will force the User.Label value back into the Prop.visName cell when it’s updated.  So open up the milestone master’s ShapeSheet once more and add a new User row named DateTrigger.  Adding the formula below creates a dependency on the date cell and every time this changes, the SETF() function writes the User.Label value to the Prop.visName cell.

DateTrigger

Links based on position

In the second example I want to demonstrate how to use the same principle of string concatenation, but this time drawing the information from the shape’s position.  I’ll use the well worn BCG matrix as this has limited dimensions giving any item within the matrix one of four values, and I’ll use those values as a search parameter for Bing.  The basic shape I’ve constructed looks like this:

BCGLinks

There's basically the matrix, and a square with a (yellow) control handle so it can be moved around the grid.  The entire shape has a hyperlink that is based on the quadrant in which the square sits.

You can see the relevant ShapeSheet cells here:

BCGUserCells

As per the Timeline example I’ve added a link base cell and an array of possible search terms.  The MarketShare cell calculates whether the control handle (and therefore the square) is in the left (High) or right (Low) columns, while the MarketGrowth cell calculates the position in terms of the top (High) and bottom (Low) rows.  The SearchIndex cell returns an index that allows the SearchTerm to retrieve the correct item from the SearchTerms cell.

The full SearchIndex formula is as follows: 


User.SearchIndex = 
IF(STRSAME(User.MarketShare,"Low"),IF(STRSAME(User.MarketGrowth,"Low"),3,1),IF(STRSAME(User.MarketGrowth,"Low"),2,0))

The LinkBase and SearchTerm values are finally consumed by the Hyperlinks section:

BCGHyperlinkCells

Note that for the actual address you need to swap any spaces with a plus character. This is just the way Bing breaks up its search terms.

If you’d like to download either of the above two example then you can get them here:

…and of course for further information on hyperlinks check out the following:

Microsoft MVP Logo

John has been a Microsoft MVP, in the award categories of Visio and M365, since 2008.