Excel VBA树视图控件 [英] Excel VBA treeview control

问题描述

我在excel中插入了一个树视图，但是在属性选项卡中我没有选项来链接控件源。基本上我想知道选择了哪个父节点和子节点。



我尝试过：

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:

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

推荐答案

我不确定你的意思是链接控制源也不知道你需要什么我想要知道选择了哪个父节点和子节点，但希望这会有所帮助...



您已经覆盖了NodeClick事件 - 请参阅传入的参数 node ...这是所选的节点。您可以获取该节点的完整路径，例如

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.

Debug.Print node.FullPath

可以产生

Africa\Benin

要查找父节点，请使用节点的Parent属性，但要警惕没有父节点的顶级节点...

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

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

会给

Africa

查看可用内容的最简单方法是设置断点行

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

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

然后右键单击节点变量并将其添加到Watch Window。您将能够看到所有属性，例如 FirstSibling 和 LastSibling 。不幸的是，官方MS文档中的链接往往会引导您访问.NET TreeView文档，该文档并不总是与Excel可用的版本完全匹配。



其他点考虑 - 你缺少

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

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：

我有一个带有两个工作表的工作簿，在Sheet1上我有一个名为Africa，Americas的列， Asia，Australasia和Europe。在这些列标题下面列出了各大洲的国家.Pheet2包含....（无论如何）但与此问题无关

"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树视图控件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！