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

Here a code fragment that builds a tree from an array:

For r = 1 To LR
If r = 1 Then
'root node
'---------
Set nodX = _
MainForm.TreeView1.Nodes.Add(, , arrSearch(r, 2), arrSearch(r, 6),
1)
nodX.EnsureVisible
Else
'child nodes
'-----------
If Len(arrSearch(r, 4)) 0 Then
'the parameters here a
'------------------------
'the index of the parentnode
'the relationship, in this case tvwChild
'the key as string
'the text of the node
'the image of the node
'----------------------------------------
Set nodX = _
MainForm.TreeView1.Nodes.Add(arrSearch(r, 5), _
tvwChild, _
arrSearch(r, 2), _
arrSearch(r, 6), _
Val(arrSearch(r, 3)))
nodX.EnsureVisible
'set the checkboxes
'------------------
If arrSearch(r, 35) = True Then
nodX.Checked = True
End If
End If
End If
Next r

As you can do:
Dim arr
arr = Range(Cells(1), Cells(20,6))

This should set you in the right direction.


RBS



"Jonathan Brown" wrote in message
...
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

Could you further explain the code fragment for me? When did you declare the
LR variable? Where does LR get it's value? and where did the arrSearch array
come from? How did you fill the array and what are its dimensions? If I
expand or collapse, or check or uncheck, a node does this code update the
data from which the treeview was derived on the spreadsheet to indicate that
it was collapsed or checked?

"RB Smissaert" wrote:

Here a code fragment that builds a tree from an array:

For r = 1 To LR
If r = 1 Then
'root node
'---------
Set nodX = _
MainForm.TreeView1.Nodes.Add(, , arrSearch(r, 2), arrSearch(r, 6),
1)
nodX.EnsureVisible
Else
'child nodes
'-----------
If Len(arrSearch(r, 4)) 0 Then
'the parameters here a
'------------------------
'the index of the parentnode
'the relationship, in this case tvwChild
'the key as string
'the text of the node
'the image of the node
'----------------------------------------
Set nodX = _
MainForm.TreeView1.Nodes.Add(arrSearch(r, 5), _
tvwChild, _
arrSearch(r, 2), _
arrSearch(r, 6), _
Val(arrSearch(r, 3)))
nodX.EnsureVisible
'set the checkboxes
'------------------
If arrSearch(r, 35) = True Then
nodX.Checked = True
End If
End If
End If
Next r

As you can do:
Dim arr
arr = Range(Cells(1), Cells(20,6))

This should set you in the right direction.


RBS



"Jonathan Brown" wrote in message
...
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

LR is a locally declared Long variable and it is just the UBound of the
array
arrSearch.
In my case arrSearch came from a text file, but that is not really relevant.
In your case it may come from a sheet range.
The dimensions of that array will be in the first dimension the number of
nodes and in the second dimension the
number of node properties needed. In my particular example the second
dimension is larger as it holds lots
of other parameters.

If I expand or collapse, etc.
It can update the sheet easily. Just run some code on the relevant events
and update the sheet range according
to the state of the treeview.

RBS


"Jonathan Brown" wrote in message
...
Could you further explain the code fragment for me? When did you declare
the
LR variable? Where does LR get it's value? and where did the arrSearch
array
come from? How did you fill the array and what are its dimensions? If I
expand or collapse, or check or uncheck, a node does this code update the
data from which the treeview was derived on the spreadsheet to indicate
that
it was collapsed or checked?

"RB Smissaert" wrote:

Here a code fragment that builds a tree from an array:

For r = 1 To LR
If r = 1 Then
'root node
'---------
Set nodX = _
MainForm.TreeView1.Nodes.Add(, , arrSearch(r, 2), arrSearch(r,
6),
1)
nodX.EnsureVisible
Else
'child nodes
'-----------
If Len(arrSearch(r, 4)) 0 Then
'the parameters here a
'------------------------
'the index of the parentnode
'the relationship, in this case tvwChild
'the key as string
'the text of the node
'the image of the node
'----------------------------------------
Set nodX = _
MainForm.TreeView1.Nodes.Add(arrSearch(r, 5), _
tvwChild, _
arrSearch(r, 2), _
arrSearch(r, 6), _
Val(arrSearch(r, 3)))
nodX.EnsureVisible
'set the checkboxes
'------------------
If arrSearch(r, 35) = True Then
nodX.Checked = True
End If
End If
End If
Next r

As you can do:
Dim arr
arr = Range(Cells(1), Cells(20,6))

This should set you in the right direction.


RBS



"Jonathan Brown" wrote in
message
...
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