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")
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 Variant) As 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
Place the following in a Userform module.
Option Explicit
Private Sub UserForm_Activate()
SettingImageList
MakeFamilyTree
End Sub
Private Sub SettingImageList()
Dim myImgList As New ImageList
Dim bt
With myImgList.ListImages
.Add Key:="Image1", Picture:=Sheet1.Image1.Picture
.Add Key:="Image2", Picture:=Sheet1.Image2.Picture
.Add Key:="Image3", Picture:=Sheet1.Image3.Picture
.Add Key:="Image4", Picture:=Sheet1.Image4.Picture
.Add Key:="Image5", Picture:=Sheet1.Image5.Picture
.Add Key:="Image6", Picture:=Sheet1.Image6.Picture
.Add Key:="Image7", Picture:=Sheet1.Image7.Picture
.Add Key:="Image8", Picture:=Sheet1.Image8.Picture
.Add Key:="Image9", Picture:=Sheet1.Image9.Picture
.Add Key:="none", Picture:=Sheet1.none.Picture
End With
With TreeView1
Set .ImageList = myImgList
.Indentation = 14
.LabelEdit = tvwManual
.HideSelection = False
End With
End Sub
Private Sub TreeView1_NodeClick(ByVal node As MSComctlLib.node)
Me.TextBox1.Text = node.Text
Me.TextBox2.Text = GetInfo(node.Text, False)
End Sub
Using TreeView Control
Add 10 image controls on the worksheet. Name them Image1 to Image9 and an image named "none".
