VBA TIPS
Using TreeView Control

TreeView Control is used for showing hierarchical structured data visually. For example, you can make Family Tree using this control like this. This is one of the AxtiveX controls.





Firstly you have to add 2 controls from the ToolBox in the Visual Basic Editor.

Right click on the ToolBox dialogue then select Additional Controls...

Next, tick the following controls from the Available Controls list.
  • Microsoft TreeView Control 6.0(SP4) - the version would be depending on your environment.
  • Microsoft ImageList Control 6.0(SP4) - the version would be depending on your environment.

Now tow additional controls have been added as the image on the right.







Example

Okay, let's practice! In this example, all images are placed in Sheet1. (of cource you can use images from your Hard Disk using LoadPicture method, but I like this way because I feel it's handy)

Add 10 image controls on the worksheet. Name them Image1 to Image9 and an image named "none".

You can add image controls View - Toolbars - Control Toolbox from main menu bar.

Right click for Properties of the image control and select appropriate image file (with an extension of bmp, cur, ico, jpg or gif) from picture property box in the properties window.

Then, place the object name in the C1 to C11 cells.

In the cells A2 to B11, place the names and their parent names. In the column D, write descriptions for each person.

Now you can see the picture on the Sheet1 as follows.




TreeView is contains collection of TreeNodes. Each TreeNode is a member of TreeNodeCollection.
All TreeNodes are number from 0 to Nodes.Count-1 sequentially in the order of appearance irrespective of the hierarchial. In this example, I do not use the index, just find Key of the Parent node then adding a child node.

expression is as follows.

TreeView1.Nodes.Add(Relative:="The key of Parent", Relationship:=tvwChild, Text:="Your Text")


With regards to the ImageList control, please have a look at http://puremis.net/excel/code/069.shtml.



Download

A sample workbook is available at http://www.puremis.net/excel/code/image/sample_080.zip

Place the following in a standard module.

Option Explicit


Sub MakeFamilyTree()
    Dim arrName As Variant
    Dim arrParent As Variant
    Dim arrMatrix() As Variant
    Dim arrTemp As Variant
    Dim elm As Variant
    Dim i As Long, j As Long
    Dim ret As Variant
    Dim node As node
    Dim bExists As Boolean

    'Reset Tree View control
    UserForm1.TreeView1.Nodes.Clear

    'Get data from the worksheet as an array
    With Sheets("Sheet1").Range(Sheets("Sheet1").[A2], Sheets("Sheet1").[A65536].End(xlUp))
        arrName = .Value
        arrParent = .Offset(, 1).Value
    End With

    'Sorting in an array
    ReDim arrMatrix(1 To UBound(arrName), 1 To 1)
    For Each elm In arrParent
        i = i + 1
        ret = Application.Match(elm, arrName, 0)
        If IsError(ret) Then
            arrMatrix(i, 1) = arrName(i, 1)
        Else
            j = 3
            ReDim Preserve arrMatrix(1 To UBound(arrMatrix), 1 To j)
            arrMatrix(i, 1) = arrName(i, 1)
            arrMatrix(i, 2) = elm
            arrMatrix(i, 3) = arrParent(ret, 1)
            Do
                ret = Application.Match(arrParent(ret, 1), arrName, 0)
                If IsError(ret) Then Exit Do
                If arrParent(ret, 1) = "" Then Exit Do
                j = j + 1
                ReDim Preserve arrMatrix(1 To UBound(arrMatrix), 1 To j)
                arrMatrix(i, j) = arrParent(ret, 1)
            Loop
        End If
    Next
    arrTemp = CustomTranspose(arrMatrix)

    'Let's add each data to nodes
    For i = 1 To UBound(arrTemp)
        For j = 1 To UBound(arrTemp, 2)
            If Not IsEmpty(arrTemp(i, j)) Then
                With UserForm1.TreeView1
                    bExists = False
                    For Each elm In .Nodes
                        If elm = arrTemp(i, j) Then bExists = True
                    Next
                    If Not bExists Then
                        If j = 1 Then
                            Set node = .Nodes.Add(, , arrTemp(i, j), arrTemp(i, j), _
                            Image:=GetInfo(arrTemp(i, j), True))
                        Else
                            Set node = .Nodes.Add(arrTemp(i, j - 1), tvwChild, arrTemp(i, j), arrTemp(i, j), _
                            Image:=GetInfo(arrTemp(i, j), True))
                        End If
                        node.Expanded = True
                    End If
                End With
            End If
        Next
    Next
End Sub


Function CustomTranspose(ByVal buf As VariantAs Variant
'Transpose an order of an array from Parent to Child
    Dim arrTemp() As Variant
    Dim i As Long, j As Long, k As Long
    ReDim arrTemp(LBound(buf) To UBound(buf)LBound(buf, 2) To UBound(buf, 2))
    For i = 1 To UBound(buf)
        k = 0
        For j = UBound(buf, 2) To 1 Step -1
            If Not IsEmpty(buf(i, j)) Then
                k = k + 1
                arrTemp(i, k) = buf(i, j)
            End If
        Next
    Next
    CustomTranspose = arrTemp
End Function


Function GetInfo(sName, bAorD) As String
'Returns appropreate image
    Dim rFound As Range
    Set rFound = Sheet1.Columns(1).Find(sName, lookat:=xlWhole)
    If rFound Is Nothing Then
        GetInfo = "none"
    Else
        GetInfo = IIf(bAorD, rFound.Offset(, 2).Value, rFound.Offset(, 3).Value)
    End If
End Function

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo