Treeview Recursion VBA Excel

I populate a treeview using an excel spreadsheet as its source.

the format of the treeview is:

Category
    Assembly Name
        Sub Assembilies

I want to recursively put a check beside nodes if the parent is selected.

i.e. check Category and all the Assemblies and Sub Assemblies will be checked
 or check Assembly and all of its Sub Assembilies are checked
jnsimexAsked:
Who is Participating?

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

x
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.

techhealthCommented:
Do a recursive subroutine that keeps calling itself over individual member of the Children collection then.  If there's no children, check the current node (recursion end condition).


jnsimexAuthor Commented:
I do not believe that there is a nodes children collection in VBA.
TommySzalapskiCommented:
There should be. The Nodes collection of the TreeNode is all the child nodes.

Sub markNodes(node As TreeNode)
Dim n As TreeNode
node.Value = True
For Each n In node.Nodes
markNodes(n)
Next n
End Sub

Or something like that.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jnsimexAuthor Commented:
Ok so I decided to not use recursion since I know my treeview will only go 3 levels.

Using the code below, when i place a check in the top level node (Category), the next level node down is checked (Assemblies) and so is the first node of the next level down (Sub Assemblies) but it crashes when it gets to the second sub assemblies node.
Private Sub TreeView1_NodeCheck(ByVal node As MSComctlLib.node)
Dim n As MSComctlLib.node

If node.Checked = True Then

    Set n = node.Child
    For i = 1 To node.Children
    n.Checked = True
    
    If Not n.Child Is Nothing Then
    For j = 0 To n.Children
     n.Child.Checked = True <error here no object
      Set n = n.Child.Next
    Next j
    End If
    
    Set n = n.Next
    Next i
End If


End Sub

Open in new window

TommySzalapskiCommented:
Change it to
For j = 1 To n.Children
TommySzalapskiCommented:
You just went one too far on line 11. You did it right on line 7.
TommySzalapskiCommented:
Oh, and you also need a new variable for the child node. Try this
Private Sub TreeView1_NodeCheck(ByVal node As MSComctlLib.node)
Dim n As MSComctlLib.node

If node.Checked = True Then

    Set n = node.Child
    For i = 1 To node.Children
    n.Checked = True
    
    Set m = n.Child
    If Not m Is Nothing Then
    For j = 0 To n.Children
      m.Checked = True
      Set m = m.Next
    Next j
    End If
    
    Set n = n.Next
    Next i
End If


End Sub

Open in new window

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
TommySzalapskiCommented:
Oops. Line 12 should still start j at 1
For j = 1 To n.Children
jnsimexAuthor Commented:
works like a champ now..Thank you.
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
Programming

From novice to tech pro — start learning today.