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:
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: