Excel TreeView multi columns

Question

Hi there,

Sorry for my Noobness with Excel/Vba... coming from a unix world...I need help!

I am trying to build a TreeView in an Excel Form from an excel Sheet with 3 columns. The sheet references a list of cinemas, already organized in a "tree view", where the A Column refers to a cinema group name, the B and C columns refer to the particular cinema names and infos, for example:

A1:Independent Cinemas
B2:CinemaName1 C2:Cinema1 Infos
B3:CinemaName2 C3:Cinema2 Infos
B4:CinemaName3 C4:Cinema3 Infos
A5:Cineplex Cinemas
B6:CinemaName4 C6:Cinema4 Infos
B7:CinemaName5 C7:Cinema5 Infos
A8:Next Group of Cinemas
B9:..... etc etc

Following this description, I want the treeview to look like:

+-A1
---+B2,C2
---+B3,C3
---+B4,C4
+-A5
---+B6,C6
---+B7,C7
+-A8
---+B9,C9
etc...

Sorry for the representation its lame but you get the picture... Here is what I have so far:

Private Sub TreeView_Populate()

Dim wbBook As Workbook
Dim wsZones As Worksheet
Dim rngZones As Range
Dim lngRows As Long

Set wbBook = ThisWorkbook
Set wsZones = wbBook.Worksheets("Cinemas")

lngRows = wsZones.Range("A65536").End(xlUp).row
Set rngZones = wsZones.Range("A1:A" & lngRows)

Dim lElement As Long
Dim rCell As Range

With Me.ZonesTree.Nodes
      'Clear TreeView control
      .Clear

        For Each rCell In rngZones
            'We have a group name in the A columns so we attach it to the tree
            If Not rCell.Text = "" Then
                .Add Key:=rCell.Text, Text:=rCell.Text
                'THIS IS WHERE I BLOCK!!
                'Need the range from Columns B and C until the next Value in the A Column
                'in order to add the children nodes....
                        .Add relative:=CinemaName, _
                          relationship:=tvwChild, _
                          Key:=CinemaName(here it will be B column), CinemaInfos(C column)
                          Text:=CinemaName(B column)
            End If
        Next rCell
End With
End Sub

I also have .ZonesTree.LineStyle = tvwRootLines in my form initialize sub-routine, which create check boxes for each element of the tree. I would like all the check boxes to be selected by default...

Is this feasible? I basically need a "temporary" range containing the values from B et C columns to build the children nodes... In the vba code I added some comments to where Im failing... All help/suggestion will be greatly appreciated!

Answer 1

Have a look at this.

Get the full range for columns a to loop the rows. Also get the Range for B,C and use a counter to indicate which row you are on

Private Sub TreeView_Populate()

    Dim wbBook As Workbook
    Dim wsZones As Worksheet
    Dim rngZones As Range
    Dim lngRows As Long

    Set wbBook = ThisWorkbook
    Set wsZones = wbBook.Worksheets("Cinemas")

    lngRows = wsZones.UsedRange.Rows.Count
    Set rngZones = wsZones.Range("A1:A" & lngRows)
    Dim rngBC As Range
    Set rngBC = wsZones.Range("B1:C" & lngRows)

    'lngRows = wsZones.Range("A65536").End(xlUp).Row
    'Set rngZones = wsZones.Range("A1:A" & lngRows)

    Dim lElement As Long
    Dim rCell As Range
    Dim rowCount As Integer
    rowCount = 1

    With Me.ZonesTree.Nodes
          'Clear TreeView control
          .Clear

            For Each rCell In rngZones
                'We have a group name in the A columns so we attach it to the tree
                If Not rCell.Text = "" Then
                    .Add Key:=rCell.Text, Text:=rCell.Text
                    'THIS IS WHERE I BLOCK!!
                    'Need the range from Columns B and C until the next Value in the A Column
                    'in order to add the children nodes....
                            .Add relative:=CinemaName, _
                              relationship:=tvwChild, _
                              Key:=CinemaName(here it will be B column), CinemaInfos(C column)
                              Text:=CinemaName(B column)
                End If
                'this is how to get the bc range
                Dim currentRowRange As Range
                Set currentRowRange = rngBC.Rows(rowCount)
                Dim b As String
                Dim c As String
                b = currentRowRange.Cells(, 1)
                c = currentRowRange.Cells(, 2)
                rowCount = rowCount + 1
            Next rCell
    End With
    End Sub

Answer 2

Thanks astander, you put me on the way...

Here we go:

Private Sub TreeView_Populate()

Dim wbBook As Workbook
Dim wsZones As Worksheet
Dim rngZones As Range
Dim rngCinemas As Range
Dim lngRows As Long

Set wbBook = ThisWorkbook
Set wsZones = wbBook.Worksheets("Cinemas")

lngRows = wsZones.UsedRange.Rows.Count
Set rngZones = wsZones.Range("A1:A" & lngRows)

Dim rngBC As Range
Set rngBC = wsZones.Range("B1:C" & lngRows)


Dim rCell As Range
Dim lastCreatedKey As String
Dim rowCount As Integer
Dim currentRowRange As Range
rowCount = 1
lastCreatedKey = ""

With Me.ZonesTree.Nodes
      'Clear TreeView control
      .Clear

        For Each rCell In rngZones
            If Not rCell.Text = "" Then
                .Add Key:=rCell.Text, Text:=rCell.Text
                lastCreatedKey = rCell.Text
            Else
                Set currentRowRange = rngBC.Rows(rowCount)
                .Add Relative:=lastCreatedKey, relationship:=tvwChild, Key:=currentRowRange.Cells(, 2).Text, Text:=currentRowRange.Cells(, 1).Text
            End If
            rowCount = rowCount + 1
        Next rCell
End With
End Sub

I actually wanted the C column values as Key and B column values as the ChildrenNode's Text.

Next Problem, I am using the checkbox mode for this tree, means all nodes are selectable. I would like that by default: -all checkboxes are selected

-when you select/unselect a parent node, all the children nodes get selected/unselected

-I want to get the selected data to store it somewhere

Does anyone has knowledge about how to do this? Kind regards, P