Solved

Populate a treeview control in Excel 2010 recursively

Posted on 2012-03-22
28
2,564 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
  • 16
  • 12
28 Comments
 
LVL 41

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 41

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
 

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 41

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 41

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 41

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 41

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 41

Expert Comment

by:dlmille
ID: 37755740
0
 

Author Comment

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

Craig
0
 
LVL 41

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 41

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 41

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 41

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 41

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 41

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 41

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 41

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 41

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now