Populate a treeview control in Excel 2010 recursively

        ' Add the first level of task ids to the top fo the tree
        For Each myRow In rngParentTaskIDs

            If myRow = varParentKey Then
                Set myCell = myRow    'why do you have this looping?  Doesn't seem necessary with the structure you have
                'For Each myCell In myRow.Cells
                'Debug.Print "Parent key found !! " & varParentKey
                'Debug.Print "Cell value:" & cell.Value
                varChildTaskID = myCell.Offset(0, 1).Value
                varChildTaskName = varChildTaskID & " - " & Application.WorksheetFunction.VLookup(myCell.Offset(0, 1).Value, rngTaskNames, 2, False)
                Debug.Print varMenuName, varChildTaskID, , varChildTaskName

                'Add first level child menu items
                .Add relative:=varParentKey, relationship:=tvwChild, Key:=varChildTaskID, Text:=varChildTaskName

                'Next myCell

            End If
        Next myRow

Select allOpen in new window

Run Time error 1004:

Unable to get the Vlookup property of the WorksheetFunction class

Option Explicit
Private Sub UserForm_Initialize()
'Purpose: Load userform with desired defaults
'Set control defaults
    With Me
        .CommandButton1.Caption = "Close"
        .Label1 = vbNullString
        .TreeView1.LineStyle = tvwRootLines
    End With
    'Populate the Treeview
    Call TreeView_Populate
End Sub

Private Sub TreeView_Populate()

'Purpose: Populate the treeview control

'On Error GoTo error_handler

Dim ws As Worksheet
Dim rngFormula As Range
Dim rngFormulas As Range
Dim varParent As String
Dim varParentDesc As String
Dim varParentKey As String
Dim rngParentTaskIDs As Range
Dim rngTaskNames As Range
Dim varMenuName As String
Dim varChildTaskID As String
Dim varParentTaskName As String
Dim varChildTaskName As String
Dim f As Worksheet, i1 As Long, i2 As Long, i3 As Long, k As Long, nAdd As Boolean, i As Long
Dim varChild As String
Dim myRow As Range
Dim myCell As Range
Dim varBaseKey As String
Dim testExists As String

    Set rngTaskNames = Range("TaskIDNames")
    Set rngParentTaskIDs = Range("ParentTaskIDs")
    varParentKey = "P55"
    varBaseKey = varParentKey
    varMenuName = "Hills Task View"
    
    With Me.TreeView1.Nodes
        'Clear TreeView control
        .Clear
        
        'Add the top level parent key to the tree
        .Add Key:=varBaseKey, Text:=varMenuName
        
        ' Add the first level of task ids to the top fo the tree
        For Each myRow In rngParentTaskIDs.Parent.Range(rngParentTaskIDs.Columns(1).Address)
            
            varChildTaskID = myRow.Offset(0, 1).Value
            varChildTaskName = varChildTaskID & " - " & Application.WorksheetFunction.VLookup(myRow.Offset(0, 1).Value, rngTaskNames, 2, False)
            'Debug.Print varMenuName, varChildTaskID, , varChildTaskName
        
            'first determine if the key belongs to any parent, already
            On Error Resume Next
            testExists = .Item(myRow.Value).Text
            If Err.Number <> 0 Then 'does not exist, so attach it to the base
                varParentKey = myRow.Value
                varParentTaskName = varParentKey & " - " & Application.WorksheetFunction.VLookup(myRow.Value, rngTaskNames, 2, False)
                .Add relative:=varBaseKey, relationship:=tvwChild, Key:=varParentKey, Text:=varParentTaskName
                'now add the child
                .Add relative:=varParentKey, relationship:=tvwChild, Key:=varChildTaskID, Text:=varChildTaskName
            Else
                'it does exist, so attach it to its parent
                
                'but first, ensure the child is not already attached, as it can only attach to one parent
                On Error Resume Next
                testExists = .Item(varChildTaskID).Text
                If Err.Number <> 0 Or varChildTaskID = "PC9999" Then 'go ahead and attach
                    varParentKey = myRow.Value
                    .Add relative:=varParentKey, relationship:=tvwChild, Key:=varChildTaskID, Text:=varChildTaskName
                Else
                    'MsgBox "Error:  Child Task-> " & varChildTaskID & " is already attached to another parent", vbCritical, "NOT ATTACHING CHILD"
                End If
            End If
                
        Next myRow

    End With
    
    GoTo endit

error_handler:
    MsgBox Err.Number & ": " & Err.Description
    Debug.Print Err.Number, Err.Description
    GoTo endit


endit:
    
End Sub

Private Sub Treeview1_NodeClick(ByVal Node As ComctlLib.Node)
'Author : Ken Puls (www.excelguru.ca)
'Purpose: Write the selected node to a label for later use
    Me.Label1.Caption = Node.Key
End Sub

Private Sub CommandButton1_Click()
'Author : Ken Puls (www.excelguru.ca)
'Purpose: Unload the userform
    Unload Me
End Sub

Select allOpen in new window