I've been able to create and populate a treeview control on a userform by
following the tutorial found he
http://puremis.net/excel/code/080.shtml
This tutorial uses two columns; Name and Parent, to create the overall
heirarchical structure of the treeview. It also includes code to add images
for each node, but I excluded that from my project as I don't need it. What
I would like to do is take this tutorial a step further by including three
additional columns; Key, Expanded, Checked. I then want to build the
treeview based on those properties as well. I want to include the Key
property because I want to be able to have nodes in the treeview with
duplicate names but with unique keys. I also want to store the Expanded and
Collapsed properties so that when the userform is redisplayed the treeview is
recreated and placed in the state that it was in when it was last open, or
when the workbook was last open.
Here's some example data to work with:
Name Parent Key Expanded Checked
View/Hide All 1 True True
Jonathan View/Hide All 2 False True
Site A Jonathan 3 False True
Site B Jonathan 4 False True
Tim View/Hide All 5 True False
Site C Tim 6 True False
Site D Tim 7 True False
One issue will be that after the treeview has been initially opened and
created then throughout the use of the treeview as the user expands and
collapses nodes while using the workbook it will need to update the above
data in the spreadsheet so that it's current configuration is saved until
next time its used.
Here's the code that I'm using right now and honestly it's really no
different from the code found at that tutorial except it's missing the code
to include images in the nodes.
Sub MakeTreeview()
Dim arrName As Variant
Dim arrParent As Variant
Dim arrKey As Variant
Dim arrExpanded As Variant
Dim arrCollapsed 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.tvFilter.Nodes.Clear
'Get data from the worksheet as an array.
With Sheets("Manning Config").Range(Sheets("Manning Config").[BU10], _
Sheets("Manning Config").[BU65536].End(xlUp))
arrName = .Value
arrParent = .Offset(0, 1).Value
arrKey = .Offset(0, 2).Value
arrExpanded = .Offset(0, 3).Value
arrCollapsed = .Offset(0, 4).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.tvFilter
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))
Else
Set node = .Nodes.Add(arrTemp(i, j - 1),
tvwChild, arrTemp(i, j), arrTemp(i, j))
End If
'node.Expanded = True
node.Checked = True
End If
End With
End If
Next
Next
End Sub
I know this is a lot that I'm asking and I think I'd eventually be able to
figure it out on my own, only after about two weeks of gruelling trial and
error. I also figure this just might be a good brain teaser for a lot of the
geniuses out there. I've searched for the solution to this issue before but
have only been able to find a persisting treeview using
VB.Net. Another
issue is I'm sure this is easily done using a connection to an Access
database but this file is frequently distributed throughout our organization
and I'd hate to have to include the .mdb file with workbook whenever it's got
to be distributed. Idealy the data should remain within the workbook. Any
help on this would be hugely appreciated.
Regards,
Jonathan Brown