PDA

View Full Version : Solved: Error in sequence of data in treeview



kishlaya
02-10-2009, 12:11 AM
hi
i posted this problem before and it was almost solved by xld, except of a small problem. in the attached a sheet called "BOM_Formatted" is there.
in the column L of the sheet one can find the level according to which the treeview in the sheet "Tree" will be populated. however the tree is not coming as desired and it is omitting the last few rows and also the branching is not done properly.:banghead:
i am hoping to find to some useful help here as before. also u can stt the attachment.
the code for the treeview is

Private Sub Worksheet_Activate()
'ActiveWindow.DisplayHorizontalScrollBar = True

'ActiveWindow.DisplayVerticalScrollBar = True
Dim LastRow As Long
Dim mpKey As String
Dim bom As Worksheet
Dim Levels As Variant
Dim i As Long
Set bom = Worksheets("BOM_Formatted")
LastRow = bom.Cells(bom.Rows.Count, "L").End(xlUp).Row

With Me.tvParts

.LineStyle = tvwRootLines
With .Nodes

.Clear

ReDim Levels(1 To 1)
mpKey = "D2" '"K:" & CStr(bom.Range("N2").Value)
Levels(1) = mpKey
.Add Key:=mpKey, Text:=CStr(bom.Range("N2").Value)
For i = 3 To LastRow

mpKey = "K" & i '"K:" & CStr(Trim(bom.Cells(i, "N").Value))
If bom.Cells(i, "L").Value <> bom.Cells(i - 1, "L").Value Then

If bom.Cells(i, "L").Value > UBound(Levels) Then

ReDim Preserve Levels(1 To UBound(Levels) + 1)
Levels(UBound(Levels)) = mpKey
End If
ElseIf bom.Cells(i, "L").Value = bom.Cells(i - 1, "L").Value Then

Levels(UBound(Levels)) = mpKey
End If

If Trim(bom.Cells(i, "N").Value) <> "N/A" Then

.Add Relative:=Levels(bom.Cells(i, "L").Value - 1), _
Relationship:=tvwChild, _
Key:=mpKey, _
Text:=CStr(Trim(bom.Cells(i, "N").Value))
End If
Next i
End With

.Nodes(1).Expanded = True
End With

End Sub

Thanks in advance.
PS: XLD if u are viewing this post kindly help as the code is authored by you so you may provide a quick fix to it. :friends:

kishlaya
02-10-2009, 12:14 AM
here is the attachment

Bob Phillips
02-10-2009, 02:26 AM
You need to give us some specific examples of where it is wrong, you cannot reasonably expect us to search it out.

kishlaya
02-10-2009, 02:52 AM
hi XLD
thanks for quick reply.
well if u can see the sheet data it is removing the values in the uncle's thread i.e parents brother. u can match the first and the last values in the sheet and the tree and u will find that they are not the same. few valuse are mispllaced. i'll try to give u an example.
suppose the level is 5 and under this level 5 sub levels are there i.e all these are level 6. now if the fourth child of level 5 (i.e 4th 6) has two children of its own(i.e levell 7) and the fifth child of level 5 has 6 children(i.e six level 8s). the code is clubbing the fourth and fifth child data together.
my apologies for this confusing explanation but if u can jus compare the tree and the "BOM_Formatted" sheet you can find the difference easily.

Kindly bear with me and help
Regards,
Kishlaya

Bob Phillips
02-10-2009, 03:11 AM
I asked for specific examples, give me a row number, an id from BOM formatted. I don't have time to look through all of your data.

kishlaya
02-10-2009, 03:14 AM
in short i noticed that as long as the level is increasing the treeis in correct order but when it get a level less than the current value the tree values start getting misplaced.

kishlaya
02-11-2009, 08:07 AM
hi
actually in ur code some line i added to encounter the situation when the level falls back by two or more.
here is the new code

Private Sub Worksheet_Activate()
'ActiveWindow.DisplayHorizontalScrollBar = True

'ActiveWindow.DisplayVerticalScrollBar = True
On Error Resume Next
Dim LastRow As Long
Dim mpKey As String
Dim bom As Worksheet
Dim Levels As Variant
Dim i As Long
Set bom = Worksheets("BOM_Formatted")
LastRow = bom.Cells(bom.Rows.Count, "L").End(xlUp).Row

With Me.tvParts

.LineStyle = tvwRootLines
With .Nodes

.Clear

ReDim Levels(1 To 1)
mpKey = "D2" '"K:" & CStr(bom.Range("N2").Value)
Levels(1) = mpKey
.Add Key:=mpKey, Text:=CStr(bom.Range("N2").Value)
For i = 3 To LastRow

mpKey = "K" & i '"K:" & CStr(Trim(bom.Cells(i, "N").Value))
If bom.Cells(i, "L").Value <> bom.Cells(i - 1, "L").Value Then

If bom.Cells(i, "L").Value > UBound(Levels) Then

ReDim Preserve Levels(1 To UBound(Levels) + 1)
Levels(UBound(Levels)) = mpKey
End If
ElseIf bom.Cells(i, "L").Value = bom.Cells(i - 1, "L").Value Then

Levels(UBound(Levels)) = mpKey
End If
If bom.Cells(i, "L").Value < UBound(Levels) Then
x = bom.Cells(i, "L").Value
y = UBound(Levels)
If y - x = 1 Then
ReDim Preserve Levels(1 To UBound(Levels) - 1)
Levels(UBound(Levels)) = mpKey

ElseIf y - x = 2 Then
ReDim Preserve Levels(1 To UBound(Levels) - 2)
Levels(UBound(Levels)) = mpKey
ElseIf y - x = 3 Then
ReDim Preserve Levels(1 To UBound(Levels) - 3)
Levels(UBound(Levels)) = mpKey
ElseIf y - x = 4 Then
ReDim Preserve Levels(1 To UBound(Levels) - 4)
Levels(UBound(Levels)) = mpKey
End If
End If
If bom.Cells(i, "L").Value = bom.Cells(i - 1, "L").Value Then

Levels(UBound(Levels)) = mpKey
End If


If Trim(bom.Cells(i, "N").Value) <> "N/A " Then
On Error Resume Next
.Add Relative:=Levels(bom.Cells(i, "L").Value - 1), _
Relationship:=tvwChild, _
Key:=mpKey, _
Text:=CStr(Trim(bom.Cells(i, "N").Value))
End If
Next i
End With

.Nodes(1).Expanded = True
End With

End Sub


thanks to you it is working fine now.
god bless you.
bubye:friends: