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 (http://www.vbaexpress.com/forum/showthread.php?t=1529).

[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 (http://puremis.net/excel/cgi-bin/click.cgi?cnt=c080&url=http://puremis.net/excel/code/080.shtml). Fwiw. :)