Persisting Treeview in VBA

J

Jonathan Brown

I've been able to create and populate a treeview control on a userform by
following the tutorial found here:

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
 
R

RB Smissaert

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 are:
'------------------------
'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 said:
I've been able to create and populate a treeview control on a userform by
following the tutorial found here:

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
Click to expand...
 
J

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 said:
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 are:
'------------------------
'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 said:
I've been able to create and populate a treeview control on a userform by
following the tutorial found here:

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
Click to expand...
Click to expand...
 
R

RB Smissaert

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.
Click to expand...
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 said:
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 said:
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 are:
'------------------------
'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



message
I've been able to create and populate a treeview control on a userform
by
following the tutorial found here:

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
Click to expand...
Click to expand...
Click to expand...
 
J

Jonathan Brown

This is brilliant. So far it's working well enough to read from my data and
rebuild itself based on all conditions. My next step now is to code the
event handlers for when I expand/collapse, check/uncheck a node to update the
data. This part shouldn't be too hard. Thanks for your help! In case
you're interested here's how my code ended up:

Dim arrSearch() As String
Dim lonDimension1 As Long, i As Long, j As Long
Dim r As Long
Dim nodX As node

lonDimension1 = Sheets("Manning Config").Range("BU65536").End(xlUp).Row - 10

ReDim arrSearch(lonDimension1, 4)

'Load all the data into a two dimmensional array.
For i = 0 To lonDimension1
For j = 0 To 4
arrSearch(i, j) = ThisWorkbook.Sheets("Manning
Config").Range("BU10").Offset(i, j).Value
Next j
Next i

'Loop through the array and build the treeview.
For r = 0 To lonDimension1

If r = 0 Then
'Create root node.
Set nodX = UserForm1.tvFilter.Nodes.Add(, , arrSearch(r, 2),
arrSearch(r, 0))

If arrSearch(r, 3) = False Then
nodX.Expanded = False
Else
nodX.Expanded = True
End If

If arrSearch(r, 4) = True Then
nodX.Checked = True
Else
nodX.Checked = False
End If

Else
'Create child nodes
Set nodX = UserForm1.tvFilter.Nodes.Add(arrSearch(r, 1), tvwChild,
arrSearch(r, 2), arrSearch(r, 0))

If arrSearch(r, 3) = False Then
nodX.Expanded = False
Else
nodX.Expanded = True
End If

If arrSearch(r, 4) = True Then
nodX.Checked = True
Else
nodX.Checked = False
End If

End If

Next r



RB Smissaert said:
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.
Click to expand...
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 said:
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 said:
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 are:
'------------------------
'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



message
I've been able to create and populate a treeview control on a userform
by
following the tutorial found here:

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
Click to expand...
Click to expand...
Click to expand...
 
R

RB Smissaert

Nice to hear it all worked and post back if you have any trouble with the
treeview events.

RBS


Jonathan Brown said:
This is brilliant. So far it's working well enough to read from my data
and
rebuild itself based on all conditions. My next step now is to code the
event handlers for when I expand/collapse, check/uncheck a node to update
the
data. This part shouldn't be too hard. Thanks for your help! In case
you're interested here's how my code ended up:

Dim arrSearch() As String
Dim lonDimension1 As Long, i As Long, j As Long
Dim r As Long
Dim nodX As node

lonDimension1 = Sheets("Manning Config").Range("BU65536").End(xlUp).Row -
10

ReDim arrSearch(lonDimension1, 4)

'Load all the data into a two dimmensional array.
For i = 0 To lonDimension1
For j = 0 To 4
arrSearch(i, j) = ThisWorkbook.Sheets("Manning
Config").Range("BU10").Offset(i, j).Value
Next j
Next i

'Loop through the array and build the treeview.
For r = 0 To lonDimension1

If r = 0 Then
'Create root node.
Set nodX = UserForm1.tvFilter.Nodes.Add(, , arrSearch(r, 2),
arrSearch(r, 0))

If arrSearch(r, 3) = False Then
nodX.Expanded = False
Else
nodX.Expanded = True
End If

If arrSearch(r, 4) = True Then
nodX.Checked = True
Else
nodX.Checked = False
End If

Else
'Create child nodes
Set nodX = UserForm1.tvFilter.Nodes.Add(arrSearch(r, 1), tvwChild,
arrSearch(r, 2), arrSearch(r, 0))

If arrSearch(r, 3) = False Then
nodX.Expanded = False
Else
nodX.Expanded = True
End If

If arrSearch(r, 4) = True Then
nodX.Checked = True
Else
nodX.Checked = False
End If

End If

Next r



RB Smissaert said:
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.
Click to expand...
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


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?

:

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 are:
'------------------------
'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



message
I've been able to create and populate a treeview control on a
userform
by
following the tutorial found here:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA Export to PDF 0
Evaluate vba matric 3
Remove Identical words 0
VBA Coding Help for Beginner 0
Word macro that can call cells out of Excel for a find and replace 0
Tried and true copy code lines fail me here 7
Search an array of sheets for an array of numbers & return count of numbers 11

Top