Click here to Skip to main content
15,881,172 members
Please Sign up or sign in to vote.
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
Updated 8-Jul-18 22:51pm
v2
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

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"
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900