Excel VBA treeview control

Question

0.00/5 (No votes)

See more:

I have inserted a treeview in excel, however in the properties tab i there is no option to link the control source. Basically i would like to know which parent node and child node is selected.

What I have tried:

VB

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Worksheets("Sheet1").Activate
    '=======================
    ' PARENT NODES
    '=======================
    
    TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 1).Value, Text:=Sheet1.Cells(1, 1).Value
    TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 2).Value, Text:=Sheet1.Cells(1, 2).Value
    TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 3).Value, Text:=Sheet1.Cells(1, 3).Value
    TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 4).Value, Text:=Sheet1.Cells(1, 4).Value
    TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 5).Value, Text:=Sheet1.Cells(1, 5).Value
    
    '=======================
    ' CHILD NODES
    '=======================
    
    Call FillChildNodes(1, "Africa")
    Call FillChildNodes(2, "Americas")
    Call FillChildNodes(3, "Asia")
    Call FillChildNodes(4, "Australasia")
    Call FillChildNodes(5, "Europe")
End Sub

Sub FillChildNodes(ByVal col As Integer, ByVal continent As String)
    '=====================================================
    '   GET THE LAST ROW DITH DATA IN IT FOR COLUMN col1
    '=====================================================
    
    Dim LastRow As Long
    With Sheet1
        LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    
    Dim counter As Integer
    counter = 1
    
    '=====================================================
    '   LOOP ROUND AND ADD CHILD NODES
    '=====================================================
    For Each country In Range(Cells(2, col), Cells(LastRow, col))
    
        TreeView1.Nodes.Add Sheet1.Cells(1, col).Value, tvwChild, continent + CStr(counter), country
        counter = counter + 1
    
    Next country
    
End Sub

Private Sub TreeView1_NodeClick(ByVal node As MSComctlLib.node)
    If node.Key = "Africa" Or node.Key = "Americas" Or node.Key = "Asia" _
        Or node.Key = "Australasia" Or node.Key = "Europe" Then
            
    Else
        Dim LastRowID As Long
        LastRowID = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
        
        For Each ID In Range(Sheet2.Cells(2, 1), Sheet2.Cells(LastRowID, "A"))
               
        If ID.Value = node.Text Then
            
                Label6.Caption = ID.Offset(, 1).Value
                Label7.Caption = ID.Offset(, 2).Value
                Label8.Caption = ID.Offset(, 3).Value
                Label9.Caption = ID.Offset(, 4).Value
                TextBox1.Text = ID.Offset(, 5).Value
                   
            End If
        Next ID
    End If
End Sub

Posted 7-Jul-18 19:08pm

Member 13902663

Updated 8-Jul-18 22:51pm

Nelek

v2

Add a Solution

Comments

Member 13902663 10-Jul-18 4:49am

Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)

If Node.Key = "Mjpahfkk;" Or Node.Key = "ahj;jpuhfkk;" Or Node.Key = "Nytpauhfkk;" _
Or Node.Key = "vz;zhfkk;" Or Node.Key = "cghfkk;" Or Node.Key = "NahRth" _
Or Node.Key = "epahahjpgjpfs;" Or Node.Key = "&j;" Or Node.Key = "1 rhKNty;" _
Or Node.Key = "2 rhKNty;" Or Node.Key = "1 ,uh[hf;fs;" Or Node.Key = "2 ,uh[hf;fs;" _
Or Node.Key = "1 ehshfkk;" Or Node.Key = "2 ehshfkk;" Or Node.Key = "v];wh" _
Or Node.Key = "neNfkpah" Or Node.Key = "v];jh;" Or Node.Key = "NahG" _
Or Node.Key = "rq;fPjk;" Or Node.Key = "ePjpnkhopfs;" Or Node.Key = "gpurq;fp" _
Or Node.Key = "cd;djg;ghl;L" Or Node.Key = "Vrhah" Or Node.Key = "vNukpah" _
Or Node.Key = "Gyk;gy;" Or Node.Key = "vNrf;fpNay;" Or Node.Key = "jhdpNay;" _
Or Node.Key = "Xrpah" Or Node.Key = "NahNty;" Or Node.Key = "MNkh];" _
Or Node.Key = "xgjpah" Or Node.Key = "Nahdh" Or Node.Key = "kPfh" _
Or Node.Key = "eh$k;" Or Node.Key = "Mg$f;" Or Node.Key = "nrg;gdpah" _
Or Node.Key = "Mfha;" Or Node.Key = "rfhpah" Or Node.Key = "ky;fpah" _
Or Node.Key = "kj;NjA" Or Node.Key = "khw;F" Or Node.Key = "Y}f;fh" _
Or Node.Key = "Nahthd;" Or Node.Key = "mg;Ngh];jyUila elgbfs;" Or Node.Key = "NuhkH" _
Or Node.Key = "1 nfhhpe;jpaH" Or Node.Key = "2 nfhhpe;jpaH" Or Node.Key = "fyhj;jpaH" _
Or Node.Key = "vNgrpaH" Or Node.Key = "gpypg;gpaH" Or Node.Key = "nfhNyhnraH" _
Or Node.Key = "1 njrNyhdpf;NfaH" Or Node.Key = "2 njrNyhdpf;NfaH" Or Node.Key = "1 jPNkhj;NjA" _
Or Node.Key = "2 jPNkhj;NjA" Or Node.Key = "jPj;J" Or Node.Key = "gpNyNkhd;" _
Or Node.Key = "vgpNuaH" Or Node.Key = "ahf;NfhG" Or Node.Key = "1 NgJU" _
Or Node.Key = "2 NgJU" Or Node.Key = "1 Nahthd;" Or Node.Key = "2 Nahthd;" _
Or Node.Key = "3 Nahthd;" Or Node.Key = "A+jh" Or Node.Key = "ntspg;gLj;jpd tpNr\k;" Then

Else

Dim LastRowID As Long

LastRowID = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row

For Each ID In Range(Sheet2.Cells(2, 1), Sheet2.Cells(LastRowID, "A"))

If ID.Value = Node.Key & ":" & Node.Text & ":" & SpinButton1.Value Then

Label6.Caption = ID.Offset(, 1).Value
Label7.Caption = ID.Offset(, 2).Value
Label8.Caption = ID.Offset(, 3).Value
Label9.Caption = ID.Offset(, 4).Value
TextBox1.Text = ID.Offset(, 5).Value

End If
Next ID

End If

End Sub

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-07-08T22:51:00

I'm not sure what you mean by "link the control source" nor do I know what you need by "i would like to know which parent node and child node is selected", however hopefully this will help ...

You already have the NodeClick event covered - see that parameter that is passed in node ... that is the node that is selected. You can get the full path for that node e.g.

VB

Debug.Print node.FullPath

could produce

Africa\Benin

To find the parent, use the Parent property of node, but be wary of the top-level nodes that do not have a parent...

VB

If Not node.Parent Is Nothing Then
    Debug.Print node.Parent
End If

Would give

Africa

Possibly the easiest way to see what is available is to put a breakpoint on the line

VB

If node.Key = "Africa" Or node.Key = "Americas" Or node.Key = "Asia" _
        Or node.Key = "Australasia" Or node.Key = "Europe" Then

and then right-click on the node variable and add it to the Watch Window. You'll be able to see all of the properties such as FirstSibling and LastSibling. Unfortunately the links in the official MS documentation tend to route you to the .NET TreeView documentation which doesn't always quite match up with versions available to Excel.

Other points to consider - you are missing

VB

Option Explicit

at the top of your form module. You should always have this set (you can set it up your options so that it is included automatically)
- It's a good idea to describe how you have set up your workbook to help us get quickly to the problem. E.g. here I would have said

Quote:
"I have a workbook with two worksheets, on Sheet1 I have columns entitled "Africa", "Americas", "Asia", "Australasia" and "Europe". Under those column headers there are lists of countries in those continents. Sheet2 contains ....(whatever) but is not relevent to this question"

Excel VBA treeview control

1 solution

Solution 1

Add your solution here

Preview 0

Excel VBA treeview control

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us