Again on treeView

[Kaizer]

Hi again,

Here is problem. Say, there is a tiny DBase in Excel which me grow in the future:

ProdGroup Region Product Sales
Condiments South Sugar 10
Condiments East Salt 2
Condiments North Sugar 5
Poultry West Chicken 50
Poultry South Duck 20

I need to fill the treeView with the unique values from ProdGroup (Parent Node) and unique values from Product (Child Node).

At the end I need to have two parent nodes (Condiments & Poultry) and four child nodes (Sugar, Salt for Condiments & Chicken, Duck for Poultry).

Can you help me with the VBA code?

Thank you in advance.

Edited by DRJ: Continuation of this post.

[Jacob Hilderbrand]

We can do this. Where is the data layed out? What Columns etc.? If you can post an attachment that would help as well.

[Jacob Hilderbrand]

Ok, see if this works for you. I assumed the data is in Columns A-D and that Column E is free to be used for some temporary data.

Option Explicit
Dim Title           As String
 
Private Sub TreeView1_DblClick()
Me.Caption = Title
End Sub
 
Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
Title = Node.Text
End Sub
 
Private Sub UserForm_Initialize()
Dim i               As Long
Dim LastRow         As Long
Dim Val1            As String
Dim xNode           As Node
Dim NodeKey         As String
LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    With Me.TreeView1
        For i = 2 To LastRow
            Val1 = Sheets("Sheet1").Range("A" & i).Text
            If Application.WorksheetFunction.CountIf( _
            Sheets("Sheet1").Range("A1:A" & i), Val1) = 1 Then
            Set xNode = .Nodes.Add
            NodeKey = Val1
            With xNode
                .Key = NodeKey
                .Text = Val1
                .Expanded = False
            End With
        End If
    Next i
    For i = 2 To LastRow
        Sheets("Sheet1").Range("E" & i).Value = _
        Sheets("Sheet1").Range("A" & i).Value & "@@@" & _
        Sheets("Sheet1").Range("C" & i).Value
    Next i
    For i = 2 To LastRow
        Val1 = Sheets("Sheet1").Range("A" & i).Text
        If Application.WorksheetFunction.CountIf( _
        Sheets("Sheet1").Range("E1:E" & i), _
        Sheets("Sheet1").Range("E" & i).Text) = 1 Then
        Set xNode = .Nodes.Add(Val1, tvwChild)
        NodeKey = Sheets("Sheet1").Range("E" & i).Text
        With xNode
            .Key = NodeKey
            .Text = Sheets("Sheet1").Range("C" & i).Text
        End With
    End If
Next i
End With
Sheets("Sheet1").Range("E:E").ClearContents
Set xNode = Nothing
End Sub

[Jacob Hilderbrand]

Also if you want to display the ProdGroup and the Product as the caption for the User Form, try this. Replace the code for the NodeClick event with the following.

Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
On Error Resume Next
Title = Node.Parent.Text & " - " & Node.Text
If Err <> 0 Then
Title = Node.Text
End If
On Error GoTo 0
End Sub

[Kaizer]

Jacob,

Thank you again.

Happy New Year!

[Jacob Hilderbrand]

You're Welcome

Happy Holidays

[Zack Barresse]

Solved, I know. Colo has some cool stuff here. Fwiw.