Visio2007 - Metric Timeline - updating intervals

[Myles7]

Hi guys - I'm pretty comfortable with VBA Excel programming and have written a number of projects that interact with Powerpoint and MS Project - but I'm new to Visio VBA and struggling just a bit.

I've written a simple project (in Excel - but that's probably irrelevant here) that generates a Metric Timeline diagram from a set of Excel tables. I can quite happily create the timeline, set its start/end-dates, position on the page - and then plot, manipulate and format milestones quite happily.

Where I've hit a (knowledge) problem is in trying to dynamically change the interval markers on the timeline itself.

I can (programmatically) change the Shapesheet settings for the interval and marker frequency (User.visTimeScale etc.) and turn the intervals/markers on/off ("User.visDisplayIntmDates" and "User.visDisplayIntm") - but the timescale setting is not respected - i.e. if I change the setting from Months ('5') to Quarters ('6') and then remove/replace the intervals and dates, the old setting (i.e. Months) is used and not the new one (Quarters).

Furthermore, if I then use the Configure Timeline dialogue from within Visio itself, it does correctly show that I have changed the Time Scale value.

The *only* way I can successfully change the timescale is via the Configure TImeline Dialogue - so I must be missing some kind of event trigger or other ShapeSheet field setting to get it to actually refresh the chosen interval...?


If only the Macro Recorder would give me a hint - but it records nothing when I change the interval details via the UI dialogue.

Can anyone point me in the right direction?

Thanks and regards,
Myles

[JohnGoldsmith]

Hello Myles,

Some Visio solutions can be standalone, ie they consist of ShapeSheet logic and basic application functionality, while others rely on additional code.  The timeline is one of those that uses an addon and you need to invoke the addon code to ensure that it updates all of the necessary steps.  For example changing the timescale will add and delete marker shapes depending on which direction you're going.

As it happens Chris Castillo wrote up an answer to a similar timeline problem a little while ago and so here's a slight adaption of his code:

Code

Public Sub TimelineTest()
  
Dim shp As Visio.Shape
Set shp = ActiveWindow.Selection.PrimaryItem
If (Not shp Is Nothing) Then
    If (shp.CellExistsU("User.visTimeScale", 0)) Then
        Application.AlertResponse = 1
        shp.CellsU("User.visTimeScale").FormulaU = 6
        Application.Addons("ts").Run "/cmd=3"
        Application.AlertResponse = 0
    End If
End If

End Sub

You may want to choose a different method of getting to the timeline shape but essentially this just demonstrates how to turn off alerts in order to get the addon refresh, but not the dialog.

Hope that helps.

Best regards

John

[Myles7]

Brilliant - thanks - I already had the alerts turned off to suppress the dialogues and had the shape defined as an object - so it was easy to adapt my code.

This has also given me a further step forward in understanding some of what I could see in the Shapesheet.

 

One thing I've just noticed though:

I was trying to use:

Code

ShpTimeline.Cells("User.visTimeScale").Result("") = 6

rather than...

ShpTimeline.Cells("User.visTimeScale").FormulaU = 6

Is there any practical difference in this context for using one over the other - they both seem to give the same result...?

[JohnGoldsmith]

Hello Myles,

Glad that worked for you.  In terms of Result or Formula properties, a cell object has both a formula and value property.  In this case it's just a single value that you're setting so Result is fine.

Have a look at this link Working with Formulas in Cells from DVS for more info.

Best regards

John