Solved: Creating a data tree in the worksheet

Hello everybody
Hope everybody is doing fine here.
i am haveing a set of data in a format in which one value contains a certain set of values. i want to create a tree of those data by clicking on a single button. The data should appear in the form of trees with the parent node having a plus sign in front of them.
here is my module code:
[vba]
Option Explicit
Sub MakeFamilyTree()
Dim arrName As Variant
Dim arrParent As Variant
Dim arrMatrix() As Variant
Dim arrTemp As Variant
Dim elm As Variant
Dim i As Long, j As Variant
Dim ret As Variant
Dim nodX As Node
Dim bExists As Boolean
'Reset Tree View control
UserForm1.TreeView1.Nodes.Clear
'Get data from the worksheet as an array
With Sheets("BOM_Formatted").Range(Sheets("BOM_Formatted").[D2], Sheets("BOM_Formatted").[D65536].End(xlUp))
arrName = .Value
arrParent = .Offset(, 1).Value
End With
'Sorting in an array
ReDim arrMatrix(1 To UBound(arrName), 1 To 1)
For Each elm In arrParent
i = i + 1
ret = Application.Match(elm, arrName, 0)
If IsError(ret) Then
arrMatrix(i, 1) = arrName(i, 1)
Else
j = 3
ReDim Preserve arrMatrix(1 To UBound(arrMatrix), 1 To j)
arrMatrix(i, 1) = arrName(i, 1)
arrMatrix(i, 2) = elm
arrMatrix(i, 3) = arrParent(ret, 1)
Do
ret = Application.Match(arrParent(ret, 1), arrName, 0)
If IsError(ret) Then Exit Do
If arrParent(ret, 1) = "" Then Exit Do
j = j + 1
ReDim Preserve arrMatrix(1 To UBound(arrMatrix), 1 To j)
arrMatrix(i, j) = arrParent(ret, 1)
Loop
End If
Next
arrTemp = CustomTranspose(arrMatrix)
'Let's add each data to nodes
For i = 1 To UBound(arrTemp)
For j = 1 To UBound(arrTemp, 2)
If Not IsEmpty(arrTemp(i, j)) Then
With UserForm1.TreeView1
bExists = False
For Each elm In .Nodes
If elm = arrTemp(i, j) Then bExists = True
Next
If Not bExists Then
If j = " " Then
Set nodX = .Nodes.Add(, , arrTemp(i, j), arrTemp(i, j), _
Image:=GetInfo(arrTemp(i, j), True))

'Else
' Set nodX = .Nodes.Add(arrTemp(i, j - 1), tvwChild, arrTemp(i, j), arrTemp(i, j), _
' Image:=GetInfo(arrTemp(i, j), True))
End If
'nodX.Expanded = True
End If
End With
End If
Next
Next
End Sub

Function CustomTranspose(ByVal buf As Variant) As Variant
'Transpose an order of an array from Parent to Child
Dim arrTemp() As Variant
Dim i As Long, j As Long, k As Long
ReDim arrTemp(LBound(buf) To UBound(buf), LBound(buf, 2) To UBound(buf, 2))
For i = 1 To UBound(buf)
k = 0
For j = UBound(buf, 2) To 1 Step -1
If Not IsEmpty(buf(i, j)) Then
k = k + 1
arrTemp(i, k) = buf(i, j)
End If
Next
Next
CustomTranspose = arrTemp
End Function
Function GetInfo(sName, bAorD) As String
'Returns appropreate image
Dim rFound As Range
Set rFound = Sheet1.Columns(1).Find(sName, lookat:=xlWhole)
If rFound Is Nothing Then
GetInfo = "none"
Else
GetInfo = IIf(bAorD, rFound.Offset(, 2).Value, rFound.Offset(, 3).Value)
End If
End Function

[/vba]
here is my userform code:
[vba]
Private Sub UserForm_Initialize()

'Purpose: Load userform with desired defaults
'Set control defaults
With Me


.TreeView1.LineStyle = tvwRootLines
End With
'Populate the Treeview
Call TreeView_Populate
End Sub
Private Sub TreeView_Populate()

'Purpose: Populate the treeview control
Dim ws As Worksheet
Dim rngFormula As Range
Dim rngFormulas As Range
With Me.TreeView1.Nodes
'Clear TreeView control
.Clear
For Each ws In ActiveWorkbook.Worksheets
'Add worksheet nodes
.Add Key:=ws.Name, Text:=ws.Name
'Check if any formulas in worksheet
On Error Resume Next
Set rngFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
'Add formula cells
If Not rngFormulas Is Nothing Then
For Each rngFormula In rngFormulas
.Add relative:=ws.Name, _
relationship:=tvwChild, _
Key:=ws.Name & "," & rngFormula.Address, _
Text:="Range " & rngFormula.Address
Next rngFormula
End If
'Release the range for next iteration
Set rngFormulas = Nothing
Next ws
End With
End Sub


[/vba]
however after executing i get a tree which contain all the sheets as branches instead of the data.
I am also attaching my file here. in the file the sheet name "BOM_Formatted" i have tabbed the data values so as to appear as a tree(for illustration.)
i hope to get some useful help here as before.
Thank you
:friends:

and yeah...i apologise for such a long code and post..
pardon me...:banghead:

Hello,

I made a similar project...

Take a look.

Hi Benzadous
Thanks for your reply and help. But I am not able to run your macro. when i try to run it it says can't find project or library.
any clues how to run this??
Regards
Kishlaya

can u tell me which reference library to select??

Ok now i am able to open your file and execute the macro also.
but when i apply similar kind of code in my file i get the following error
Compile time error: User defined data-type not defined.
any clues which reference library to include??
it comes in the following section
[VBA]
Private Sub UserForm_Activate(ByVal Node As MSComctlLib.Node)
If Node.Checked Then
Node.Expanded = True
If Cells(Int(Mid(Node.Key, 2)), 1).IndentLevel > 0 Then
INode = Node.Parent.Index
For n = Cells(Int(Mid(Node.Key, 2)), 1).IndentLevel To 1 Step -1
tvTarefas.Nodes(INode).Checked = True
If n > 1 Then INode = tvTarefas.Nodes(INode).Parent.Index
Next n
End If
Else
On Error Resume Next
n2 = Node.Next.Index - 1
If n2 = "" Then
If Cells(Int(Mid(Node.Key, 2)), 1).IndentLevel = 0 Then
n2 = tvTarefas.Nodes.Count
Else
n2 = Node.Parent.Next.Index - 1
If n2 = "" Then n2 = tvTarefas.Nodes.Count
End If
End If
On Error GoTo 0

For n = Node.Index To n2
tvTarefas.Nodes(n).Checked = False
tvTarefas.Nodes(n).Expanded = False
Next n
End If
End Sub
[/VBA]

The error is at
[VBA]Private Sub UserForm_Activate(ByVal Node As MSComctlLib.Node)[/VBA]

"Node" is a parameter that is valid only for TreeView objects... you can't use it on a Userform procedure.

Hi i am still stuck,:banghead: the form will apear but the tree wont. can u gice me a hint how do i make the data in the sheet BOM_Formatted in the form of a tree. (you can check from the attachment.) prob if u could make the first two bracnhes then i'll tak it from there.
Kishlaya

OMG, I can't run your spreadsheet.

I am confused as to what the question is.

What do all of the sheets do?

What are you trying to do that doesn't work (yet!)?

Dear XLD
all the other sheets contain the data for creating the BOM_Formatted sheet. But u can see the data is indented in the BOM_Formatted sheet, I want to make the indented data in a tree forem.

Hi
Can you tell me wat msg you are getting when youtry to run my spreadhseet?

As far as I can see your code does what you want, so I repeat ... What are you trying to do that doesn't work (yet!)?

my code is just indeneting the data whereas i want it to be represented in a tree form with a plus sign in each node and so on, so that the user can expand any branch jus by clicking on the plus icon.

You could use outlining to group sets and get the + sign, but that only goes to 3 levels and won't suffice.

The alternative that I see is a form with a treeview as already proposed earlier. Did you try that?

yeah i tried that but i am not able to put the code in the correct way. the form is appearing apparantly with no tree on it. Can u please have a look at the form code and then modify it for my file for a few branches, the rest i'll do by learning from your code.

I don't think that I will because it looks like too much work for me. It is too much because I still am not understanding what is happening. Everywhere I look the data is already formatted/indented, or it is in separate cells. I have no idea what Master, Master1, BOM all are (BOM looks the same as BOM formatted), and I don't have the time to work it all out from the ground up.

if you saw the sheet of Benzadeus and compare it with my sheet BOM_Formatted both are similar, what the button in Benzadeus sheet is doing to its data(i.e forming a tree in a userform) , i want it to be done to mine. you need not worry about the other sheets, its jus the data in the sheet BOM_Formatted i have to get in a userform in a tree format.

Treeviews need a unique key, your part numbers repeat.

my parts numbers cannot repeat since inside one part many different parts are there. by saying to key nodes repeating do you mean that the ones which are having N/A as their nodes?

Here is a quick shot

hi XLD
Thank you very much for your effprts but I am not able to run the button, i get a msg "Can't find project or library", any clues what should i do get it running..??
Regards,
Kishlaya

Dear XLD
Hey i am able to run ur button, its coming as desired.
Thanks a million mate.
Warm regards,
Kishlaya:beerchug:

Dear XLD
the file which u sent worked prefectly for me but when i execute it on a different sheet i am getting some errors. each time i select a number the tree is showing correctly except a lst few nodes which are being shown ina different family. Kindly see the attached file and help.
Thanks in advance