Pearls from the auld Scotsman
Few people know Visio software and communicate their knowledge as well as Dave Edson. In this regular column Dave dispenses pearls of wisdom in his own inimitable style.

Custom Property Dependencies in Visio

David A Edson

Ever since Visio gave the developers ands end-users Custom Properties, we have been developing more and more ingenious ways to use them to enhance the behavior and use of the SmartShapes symbols that we create as part of our solutions.

However there are some limitations that can be the source of frustration if not downright irritation when dealing with Custom Properties. Here is a scenario for you to ponder:

A shape has two Custom Properties; FixedList "ONE" and FixedList "TWO". List One contains three choices; "A", "B", and "C". Your desire is to create a dependency between List One and List Two such that when you select "A" from List One, you have the choices "1" and "2" available in List Two. If you Select "B" from List One, you want "3" and "4" in List Two. Additionally, if you select "C" from List One you want to be limited to choices "5" and "6" in list Two. True dependencies listings. This would be great! Imagine a scenario in Sales Configuration Management where selecting a particular option brings up a listing of only the options applicable to that first selected option. This is a common occurrence in Automotive sales, High-end Audio and Video sales as well as many, many others.

All right… with that said, you dutifully set up your list boxes names Prop.Letter and Prop.Number in your Custom Properties. You declared each as type "1"; fixed list, and you used the format cell in the first list to add the formula "A;B;C". When the user edits the Custom Properties of that list he or she sees and can only select the items "A", "B", or "C" from the list. Grand!! Now in the second list you place into the format cell the following formula:

=IF(LOOKUP(Prop.Letter,Prop.Letter.Format)=0,"1;2",
IF(LOOKUP(Prop.Letter,Prop.Letter.Format)=1,"3;4","5;6"))

As we analyse this formula, we see that it says "If the Prop.Letter Custom Property Value Cell contains "A" (index item 0), then create the list "1;2", otherwise, "If the Prop.Letter Custom Property Value Cell contains "B" (index item 1), then create the list "3;4", otherwise create the list "5;6". In theory, when we select, say…., "B" from List One, the Properties Dialogue should display the choices "2" and "3" in List Two, changing dynamically to accommodate our selections… Well, we are wrong!!!

Though the evaluation does happen… it is quite quirky and it takes a minimum of three times in and out of the Custom Properties dialogue to actually see and properly set the requested dependencies. All the while the user is being presented with what SHOULD be invalid choices. Hardly a "world-class" solution, aye?

Well our auld friend VBA can come tae the rescue here again!!! It is actually a very simple matter to set up dependencies in lists in VBA. The code that follows details the entire functions. Following the code I will explain a wee bit.

Private Sub UserForm_Initialize()
LetterChoice.AddItem ("A")
LetterChoice.AddItem ("B")
LetterChoice.AddItem ("C")
LetterChoice.Value = "A"
End Sub

Private Sub LetterChoice_Change()
NumberChoice.Clear
Select Case LetterChoice.Value
Case "A"
NumberChoice.AddItem ("1")
NumberChoice.AddItem ("2")
NumberChoice.Value = "1"
Case "B"
NumberChoice.AddItem ("3")
NumberChoice.AddItem ("4")
NumberChoice.Value = "3"
Case "C"
NumberChoice.AddItem ("5")
NumberChoice.AddItem ("6")
NumberChoice.Value = "5"
End Select
End Sub

Private Sub ValidChoice_Click()
Dim shTheShape As Visio.Shape
Dim ceLetterCell As Visio.Cell
Dim ceNumberCell As Visio.Cell
Dim LetterHolder As Variant
Set shTheShape = Visio.ActivePage.Shapes.Item("Smart")
Set ceLetterCell = shTheShape.Cells("Prop.L.Value")
Set ceNumberCell = shTheShape.Cells("Prop.N.Value")
LetterHolder = LetterChoice.Value
ceLetterCell.Formula = Chr(34) & LetterHolder & Chr(34)
ceNumberCell.Formula = Val(NumberChoice.Value)
Unload ListChooser
End Sub

These three Subroutines are the complete code required here… Let’s look at the first shall we?

The Subroutine UserForm_Initialize () is called each time the VBA form is displayed. The call to actually display this form is the Subroutine DisplayChoices and is called from the Actions section of the ShapeSheet as =RUNADDON("DisplayChoices"):

Public Sub DisplayChoices()
ListChooser.Show
End Sub

It’s only purpose is to launch the display of the form and trigger the Initialize function that follows here next:

Private Sub UserForm_Initialize()
LetterChoice.AddItem ("A")
LetterChoice.AddItem ("B")
LetterChoice.AddItem ("C")
LetterChoice.Value = "A"
End Sub

Notice that this routine simply fills the first COMBO box in the VBA form with the choices "A", "B", and "C" and then sets the default value for the list to "A".

The next function is the heart of the dependency scheme. This function is triggered any time a change is made to the selection of the first COMBO box on the form:

Private Sub LetterChoice_Change()
NumberChoice.Clear
Select Case LetterChoice.Value
Case "A"
NumberChoice.AddItem ("1")
NumberChoice.AddItem ("2")
NumberChoice.Value = "1"
Case "B"
NumberChoice.AddItem ("3")
NumberChoice.AddItem ("4")
NumberChoice.Value = "3"
Case "C"
NumberChoice.AddItem ("5")
NumberChoice.AddItem ("6")
NumberChoice.Value = "5"
End Select
End Sub

Note here that it is simply a Select Case statement that looks at the value of the first COMBO box. First we clear out any value in the second COMBO box. We then look at the first COMBO box. If it is "A" then we fill the second COMBO box with the values "1" and "2" and set the default value to "1". The process for "B" and "C" is identical, save for the actual values.

Finally we push the choices back to the Visio Shape. This is done when the user clicks the Command Button. The code for this is:

Private Sub ValidChoice_Click()
Dim shTheShape As Visio.Shape
Dim ceLetterCell As Visio.Cell
Dim ceNumberCell As Visio.Cell
Dim LetterHolder As Variant
Set shTheShape = Visio.ActivePage.Shapes.Item("Smart")
Set ceLetterCell = shTheShape.Cells("Prop.L.Value")
Set ceNumberCell = shTheShape.Cells("Prop.N.Value")
LetterHolder = LetterChoice.Value
ceLetterCell.Formula = Chr(34) & LetterHolder & Chr(34)
ceNumberCell.Formula = Val(NumberChoice.Value)
Unload ListChooser
End Sub

Here we get a reference to the Shape by name (in this case "Smart"), get references to the particular Custom Property cells (in this case "Prop.L.Value" and "Prop.N.Value" for the Letter and Number cells respectively, then we hand off the values in the COMBO boxes to the Formula property of each Custom Property cell that we have referenced. Lastly, we unload the form.

Notice that two additional bits of business have been accomplished here in the SmartShape. Firstly we have set the Text of the shape to be referenced from the Custom Property values, and we have set the Custom Property Invisible cells to TRUE and the ASK cells to FALSE to avoid the user being able to use the standard Visio Custom Property Dialogue. We have replaced it with our own form. This VBA form actually works as required and avoids any ambiguity or confusion for the user.

Linked to this wee article you will find the Visio .VSD file with the entire example and code for both the quirky method as well as the robust VBA method. I sincerely hope that you will take this braw example and expand upon it tae create your ain world class solutions in Visio.

‘Till next month, lads an’ lassies….

Slainte!! An’…. Haste ye bak!!!

Dave "The Auld Scotsman" Edson