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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Rory ArchibaldCommented:
The relative part for the subnodes should be:
 ws.name & "," & rngformula.address
Rather than rngformula.text

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.