PDA

View Full Version : Solved: Using Treeview Control



Aussiebear
05-24-2008, 04:09 AM
I'd like to try and use the Treeview control on a form to display data contained in lists. How do i do this?

For example, in the attached workbook (Treeview.xls) I have four teams, each with four players. I'm hopeing that the main nodes will be named Team A, Team B, Team C & Team D. Under Team A will be 4 child nodes - Tony, Phil, Ian & Sam. Then a Mani node (Team B) etc etc.

I've seen Ken Puls example on his website but it deals with Sheets.

Bob Phillips
05-24-2008, 05:35 AM
Private Sub Userform_Activate()
Dim ary
Dim oNode As Node
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long
Dim sh As Worksheet

Set sh = ActiveSheet

With TreeView1.Nodes
TreeView1.LineStyle = tvwRootLines

LastCol = sh.Cells(2, sh.Columns.Count).End(xlToLeft).Column
For j = 1 To LastCol

LastRow = sh.Cells(sh.Rows.Count, j).End(xlUp).Row
.Add , , sh.Cells(2, j).Value, sh.Cells(2, j).Value
ReDim ary(1 To LastRow - 2)
For i = 3 To LastRow

Set oNode = .Add(sh.Cells(2, j).Value, tvwChild, sh.Cells(i, j).Value, sh.Cells(i, j).Value)
Next i
Next j
End With
End Sub

Aussiebear
05-24-2008, 03:46 PM
Thank you Bob. Works well

Aussiebear
05-24-2008, 06:19 PM
Private Sub Userform_Activate()
Dim ary
Dim oNode As Node
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long
Dim sh As Worksheet

Set sh = ActiveSheet

With TreeView1.Nodes
TreeView1.LineStyle = tvwRootLines

LastCol = sh.Cells(2, sh.Columns.Count).End(xlToLeft).Column
For j = 1 To LastCol ' This allows the column count to be dynamic

LastRow = sh.Cells(sh.Rows.Count, j).End(xlUp).Row ' This finds the last row in any column
.Add , , sh.Cells(2, j).Value, sh.Cells(2, j).Value 'Have no idea what these three lines do.
ReDim ary(1 To LastRow - 2)
For i = 3 To LastRow

Set oNode = .Add(sh.Cells(2, j).Value, tvwChild, sh.Cells(i, j).Value, sh.Cells(i, j).Value) ' This sets the child nodes?
Next i
Next j
End With
End Sub


Bob's more than likely not on line right now but could someone help me understand what this code is doing on a line by line basis please?

malik641
05-24-2008, 09:29 PM
Hey Ted,

Check out the comments. I changed the code a little, too. Hope this helps:
Private Sub Userform_Activate()
Dim ary
Dim oNode As Node
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim sh As Worksheet

Set sh = ActiveSheet

With TreeView1.Nodes
TreeView1.LineStyle = tvwRootLines

' you know this one =)
LastCol = sh.Cells(2, sh.Columns.Count).End(xlToLeft).Column

For j = 1 To LastCol
' get last row per current column
LastRow = sh.Cells(sh.Rows.Count, j).End(xlUp).Row

' Add a parent node using the current column and
' row 2 as the parent name. when you do not set
' a 'Relative' or 'Relationship' parameters,
' it's considered the parent (I guess)
.Add Key:=sh.Cells(2, j).Value, _
Text:=sh.Cells(2, j).Value

' resize the array with a 'minus 2' to compensate for the
' skipped row 1 and the header row 2 (2 rows total)
ReDim ary(1 To LastRow - 2)

For i = 3 To LastRow
' notice the 'Relative' section. it points to
' the header row 2 in the current column
' this shows it's relationship as the header
' row's value is the parent
Set oNode = .Add(Relative:=sh.Cells(2, j).Value, _
Relationship:=tvwChild, _
Key:=sh.Cells(i, j).Value, _
Text:=sh.Cells(i, j).Value)
Next i
Next j
End With
End Sub

Aussiebear
05-24-2008, 10:09 PM
Thanks Joseph, I now have a better understanding of the issue.

Bob Phillips
05-25-2008, 03:53 AM
The array was an unnecessary step that I started with, abandoned, and forgot to remove the code from. I don't need it as I load the treeview directly, my original intent being to load an array and then load the treeview from the array, wasteful.

Aussiebear
05-25-2008, 05:46 AM
I appreciate the effort anyway Bob.