Solved

Populate a treeview control in Excel 2010 recursively

Posted on 2012-03-22
28
2,808 Views
Last Modified: 2012-03-23
Hi,

I have a two lists in excel (named ranges) :

NamedRange1: called taskIDs - has two fields
taskID, taskName

NamedRange2: called taskRelationships - has two fields
parentTaskID, childTaskID

Both named ranges are dynamic named ranges where I want to refresh data into and re-populate the treeview when ever I need to.

I need to build/populate my treeview control with a recursive hierarchy.
Sample data:
taskIDs (named range)

taskID       taskname
PD0000     Product Data Maintenance
PD1000     Product Data
PD2000     Production Data
PD1010     New Product
PD1020     New product branch
PD3000     Product Costing


taskRelationships (named range)
parenttaskID    childtaskID
PD0000                PD1000
PD0000                PD2000
PD0000                PD3000
PD1000                PD1010
PD1000                PD1020
....
and so on; the task relationships may be several levels deep which I am trying (desperately) to build using some sort of recursive vba code.

So the treeview needs to look like:

PD0000 - Product Data Maintenance
          PD1000 - Product Data
                    PD1010 - New Product
                    PD1020 - New product branch
          PD2000 - Production Data
          PD3000 - Product Costing
     ....

I would appreciate any help anyone can offer, as it is doing my head in.


I hope this is clear enough to describe my issue.

Thanks in advance for any help.

Craig
0
Comment
Question by:slobber72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 12
28 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37755401
Can you post a sample of your data?
0
 

Author Comment

by:slobber72
ID: 37755498
Please see attached xlsx file.

There is a userform ( a very, very bad one that I have tried to play with) with associated code that starts to show what I have tried to do.

However I have failed quite dismally to get the result I need....

Thanks,
Craig
120322-TaskItems-help.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755535
Is there a reference I need to add to see your whole userform?  I added treeview 6.0 control but still getting an error just pulling this up.

What version Excel are you using?

Dave
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:slobber72
ID: 37755561
Hi,

Please see attached image of my references from Excel VBA .....

It is a treeview control 6.0 - no special addins or anything like that....

I am using Excel 2010 on Windows 7.

hope this helps.

Craig
23-03-2012-12-36-21-PM.jpg
23-03-2012-12-39-13-PM.jpg
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755661
Well for some reason Version 6.0 is not working with my installation.  Mind if I assist using Version 5.0 as the code is likely the same?

You also have an invalid range name (at least in the downloaded version).

Please share your offset formula for range name, "TaskIdNames"

Current formula:

=OFFSET(TaskItems!#REF!,0,0,COUNTA(TaskItems!$A:$A)-1,3)

is the #Ref supposed to be $A$1?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755687
Are you there?  Your code starts with Parent Key of P55 but your data doesn't reflect that.  I need you to interact with me for a few so I can get functioning to where you say you are.  Then, I can assist probably very quickly.

Please advise,

Dave
0
 

Author Comment

by:slobber72
ID: 37755708
Hi Dave,

Sorry - I was in a meeting at work !

The formula for the named ranges should be:

ParentTaskIDS
=OFFSET('Task Relationships'!$A$2,0,0,COUNTA('Task Relationships'!$A:$A)-1,2)

TaskIDNames
=OFFSET(TaskItems!$A$2,0,0,COUNTA(TaskItems!$A:$A)-1,2)

Also, the ParentKey value of "P55" should be "P0000". The code runs through, gives an error and then shows the userform, with a very limited treeview population.

If you can try these formula changes to see if you get the same result that would be great.

thanks,
Craig
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755711
I got that sorted, and was working on populating the treeview.  Parents are populating.

Does your code populate everything, or are you getting errors?  I'm working on the second half adding children to understand what you've done (versus just scrapping and rewriting).

PS - not sure why you're looping as there's only one child per row.

e.g., i rewrote to:
        ' 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

Open in new window

Let me know.

Dave
0
 

Author Comment

by:slobber72
ID: 37755733
Thanks,

I only got the first level to work and then I had errors - the code in the spreadsheet is limited by my distinct lack of knowledge of how to loop through rows of data in excel using vba (my previous experience has been with recordsets in access which are a bit different).

In reading your re-written code, it makes more sense for the first level - the trick is how to get mutliple levels of child nodes added. I was thinking of using a called procedure, that bookmarked its place in the list, and called itself to add the next level of child nodes, and returned to the list of parent tasks.(if that makes sense). But I just can't do the coding to effect this.....

Cheers,
Craig
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755735
I'm getting it going.  The first loop only needs to look at the first column in your range.  Also, the second loop.

Almost there getting your existing work coding.

Are you around for a bit, to give feedback?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755740
0
 

Author Comment

by:slobber72
ID: 37755760
Trying it now - won't be long.

Craig
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755774
I think there's a different way to go at this.  By the way, you have sibling keys to the very top level parent, e.g., PC0000 which your code is not picking up.

Is the real base "Tree Hills View"?  Then top level parents are added to this (e.g., P55?) , with their children, correct?

Dave
0
 

Author Comment

by:slobber72
ID: 37755778
When I run the "uflaunch" macro within  excel, I get the following error:

Run Time error 1004:

Unable to get the Vlookup property of the WorksheetFunction class

any clues on this ? Is it a missing reference library on my machine ?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755784
No, it shouldn't - will look into that in a few.  We may not be prefixing with the right sheet When you say it works, it is missing PCxxxx right?  Is this as far as you want me to take it?
0
 

Author Comment

by:slobber72
ID: 37755796
Dave,
Attached is the "actual data" I am using.

Please note the blank cells at the bottom of column A which indicates the "child"tasks are at the very top level of the tree.

If you copy and paste these over the top of the current values, it will give you a better picture of the overall requirement - I was using PD0000 as a base for testing, but this data is the full raw data.
120322-TaskItems-help-r2.xlsm
0
 

Author Comment

by:slobber72
ID: 37755808
Dave,

Yes - it was missing everything that wasn't connected to the root of PD0000, which is as I expected.

Craig
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37755813
Check this.

Here's the solution code:

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

Open in new window


I'll now take a look at your "real Data" and test

see attached.

Dave
120322-TaskItems-help-r2.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755818
Seems to work fine with your test data.  Note I found some children attached to other parents, so you get a MSGBOX error, and it continues.  I also didn't concern myself with max # items in treeview or # levels deep, as your real data appears to work in the initial loop.

See attached with real data.

Dave
120322-TaskItems-help-r3.xlsm
0
 

Author Closing Comment

by:slobber72
ID: 37755821
Dave spent heaps of time on this for me - really good answer in quick time - thank you !!!!
0
 

Author Comment

by:slobber72
ID: 37755823
Thanks Dave - that is just what I was after - I really appreciate your help !!
Regards,

Craig
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755824
Glad I could help.  My first treeview exercise.  I haven't looked at linked list (and this is somewhat like that) in probably 15-20 years...

Cheers,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755827
testing for treeview node existance is similar to using a dictionary to see if an element has been added.

I suggest writing a function (or class method) so you can emulate that - and clean up the on error traps which is messy:

e.g.,

Function tvNodeExists(TV as object,strNode as String) as Boolean
Dim chkNode as String
 
  tvNodeExists = true

  on error resume next
  chkNode = TV.Node(strNode).Text
  if err.number <> 0 then rvNodeExists = false

Exit Function
0
 

Author Comment

by:slobber72
ID: 37759499
Hi Dave,
The treeview works great.
There are a few operational questions I was hoping to ask.

The button to activate the userform is on the taskrelationships tab. This works fine. But when I run the uflaunch macro from a button on say the taskid tab, the treeview fills with one level of the same task repeated thousands of times. Any clues on this one ?

How would the code look if you didn't disallow duplicate child entries ?. ie: there is one 'taskid' called pc9999 which is used as a seperator which is use din various places through the relationships.

Thanks again - you have been such a big help thus far !!
Regards,
Craig
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37759528
>>the uflaunch macro from a button on say the taskid tab, the treeview fills with one level of the same task repeated thousands of times. Any clues on this one ?

That's because in the userform, we use Range without a prefix, so it defaults to the current worksheet which could be wrong.

Line 53 uses Range(of an address) and should be wks.Range(of an address) like this:

For Each myRow In rngParentTaskIDs.Worksheet.Range(rngParentTaskIDs.Columns(1).Address)

or set wks = rngParenttaskIds.worksheet

then

For Each myRow In wks.Range(rngParentTaskIDs.Columns(1).Address)

>>How would the code look if you didn't disallow duplicate child entries ?. ie: there is one 'taskid' called pc9999 which is used as a seperator which is use din various places through the relationships.

You tell me, lol.  Why not try it?

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

Open in new window


see attached.

I don't think the PC9999 is getting attached elsewhere.  Might take some research to determine why or the treeview doesn't allow it either.  Ask a new question for research purposes if needed.

Dave
120322-TaskItems-help-r4.xlsm
0
 

Author Comment

by:slobber72
ID: 37759589
Dave,

Just to finalise my query on this (perhaps more for your benefit, I hope).

This is data output from an Oraacle database which our team is configuring an application for at the moment.The reason I was trying to attach a child to more than one parent is that this is the menu structure of the application we are configuring.

There is a more detailed method that the application uses to number the menu items.

If you search the data for PC8000 you will see there are several entries for PC9999. In the application PC9999 is a seperator. However each relationship record actually has a unique identifier. PC9999 is also used as a seperator on several other sub-menus (PC3000, PC90000) . So, I understand I shouldn't try to add PC9999 to PC8000 five times (that would be silly :)  ), but I would like to add it to another parent (PC3000,PC9000) if needed.

I hope this clears up my query - no need for any further work - I think I've overstepped the boundaries of polite questions anyway !

Btw - the revised range reference code you posted worked great !

Thanks and regards,

Craig
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37759600
Not sure you can with treeview, as it surely is not letting us do it, and i'm on a honeymoon with treeview thanks to you finally got my feet wet.  However, you should be able to create multiple separators, re: PC99991, PC99992, etc., even from your SQL query output.

Cheers,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37759610
An alternative not in treeview:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24510705.html

Also, see related links in the thread as someone asked for just this exact original question you posted, with recursive routine - I didn't look closely at that solution so not sure if its accomplishing anything different than what we already did.

We didn't need to do recursion and the children were at different levels.  If our solution works the same, I might suggest it more efficient (perhaps), but the recursive routines are always I think more simply understood (perhaps), lol.

Dave
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question