Excel Treeview - adding sub nodes

Hi Everyone,

I am trying to populate this treeview from an Excel Spreadsheet.

Here are some of the properties of the sheet:
name: Projectors
A1: "3D System"
B2: "3D System Components"
B3: "3D Systems Materials"

I want the treeview to take the sheet name and have that as the top level node, next node will be A1 value and the next level node will be B2 and B3 value.


      - 3D Systems
            - 3D Systems Components
            - 3D Systems Materials

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
The relative part for the subnodes should be:
 ws.name & "," & rngformula.address
Rather than rngformula.text
jnsimexAuthor Commented:
Here is the coding that I am trying to make work.

It fails when a new node is being appended to the child node.
Private Sub TreeView_Populate()
'Author : Ken Puls (www.excelguru.ca)
'Purpose: Populate the treeview control
     Dim ws As Worksheet
     Dim rngFormula As Range
     Dim rngFormulas As Range
     Dim rng1Formula As Range
     Dim rng1Formulas As Range

     With Me.TreeView1.Nodes
          'Clear TreeView control

          For Each ws In ActiveWorkbook.Worksheets
               'Add worksheet nodes
               .Add Key:=ws.Name, Text:=ws.Name

               'Check if any formulas in worksheet
           '    On Error Resume Next
               Set rngFormulas = ws.Cells.Range("A1")
           '    On Error GoTo 0

               'Add formula cells
               If Not rngFormulas Is Nothing Then
                    For Each rngFormula In rngFormulas
                         .Add relative:=ws.Name, _
                              relationship:=tvwChild, _
                              Key:=ws.Name & "," & rngFormula.Address, _
               Set rng1Formulas = ws.Cells.Range("B2:B10")
                  'Add formula cells
               If Not rng1Formulas Is Nothing Then
                    For Each rng1Formula In rng1Formulas
           Fails here >>>>>>     .Add relative:=rngFormula.Text, _
                              relationship:=tvwChild, _
                              Key:=rngFormula.Text & "," & rng1Formula.Text, _
                    Next rng1Formula
               End If
               Next rngFormula
            End If
               'Release the range for next iteration
               Set rngFormulas = Nothing
          Next ws
     End With

Open in new window

jnsimexAuthor Commented:
Thanks rorya. It is working now.

Just another quick question, is there a way to have the child nodes selected when only the parent is node is checked.
Rory ArchibaldCommented:
I think you can only have one selected at a time, but I confess I don't use TreeViews very often.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.