CODEWITHSUNDEEP
excelvba
Xenos
Xenos

Reputation: 83

Forming a loop that build a tree view structure

I have got a challenging problem that I tried solving but unsuccessful and I need some guidance on this.

I am trying to build a tree view with the structure shown in the image below. In the below example there are 2 root nodes. For the first root node, it has got 2 child nodes. The first child node (1.A) has got no "sub-child nodes" while the second child node (2.B) has multiple child and "sub-child" nodes. Then second root node has the same structure.

TreeView

To simplify the problem, functions to create root nodes and child nodes have been created. To create a root node, I can simply do:

    Set cRoot = Tree.AddRoot("Root Node")

To create a child node, I can simply do:

    Set cNode = cRoot.AddChild("1.A")
    Set cNode = cRoot.AddChild("2.B")

And if I want to dive deeper to the child node I can do:

    Set cNode = cNode.AddChild("2.1 level2")
    Set cNode = cNode.AddChild("2.1.1 level3")

Once I run all the above code, the below will be generated.

TreeViewSimplified

My question/challenge is, without hardcoding it, how to reverse it. So if I have the tree structure as the first picture with few number of root nodes and child, how to build a loop that will add all. I basically want to parse the sheet information back to the vba code internally.

To me, the logic would be read first cell, if it is not empty then that would be a root node. Then if it is a root node, search and see if it has a child node. In this case the first child node is "1.A", etc.

In short, how do I build a loop that will add the root and child nodes back with the addRoot() and addChild() functions I have got? I go as far as it find the furthest branch of the first child node but I am not sure how to make it "goes back" and search if there is a second child node and then continue dive deeper.

Upvotes: 0

Views: 1480

Answers (1)

Skin
Skin

Reputation: 11197

Try this on for size ...

Private Sub BuildTree()
    Dim objSrcSheet As Worksheet, lngRow As Long, objCell As Range
    Dim objSrcRange As Range, objParent As Range

    Set objSrcSheet = Sheet1
    Set objSrcRange = objSrcSheet.Range("A1:" & objSrcSheet.Cells.SpecialCells(xlCellTypeLastCell).Address)

    With objSrcRange
        For Each objCell In .Cells
            If objCell.Value <> "" Then
                If objCell.Column = 1 Then
                    ' If it's column one, it's a root node, therefore, just add it normally.
                    TreeView.Nodes.Add , , objCell.Address, objCell.Value
                Else
                    ' Loop back through the previous column to find the parent.
                    For lngRow = objCell.Row To 1 Step -1
                        Set objParent = .Cells(lngRow, objCell.Column - 1)

                        If objParent.Value <> "" Then
                            TreeView.Nodes.Add objParent.Address, tvwChild, objCell.Address, objCell.Value
                            Exit For
                        End If
                    Next
                End If
            End If
        Next
    End With
End Sub

... this is using the the (somewhat) standard Microsoft Common Controls Tree View.

enter image description here

https://www.microsoft.com/en-us/download/details.aspx?id=10019

This is what the source data looks like in the worksheet ...

enter image description here

... and the logic assumes that all data starts from cell A1.

If you're not using the same control as me, you may be able to adapt it but in all honesty, I can't be sure but the logic is sound so it gives you a fairly useful starting point.

I hope that helps.

Upvotes: 1

Related Questions