Userform Tree Structure / Node Help

wonka1234 · Aug 17th 2016

Hi,

I have the following data set;

[TABLE="width: 500"]

[tr]

[td]

Path

[/td]

[td]

City

[/td]

[/tr]

[tr]

[td]

Global/North America/USA

[/td]

[td]

New York

[/td]

[/tr]

[tr]

[td]

Global/North America/USA

[/td]

[td]

Miami

[/td]

[/tr]

[tr]

[td]

Global/North America/Canada

[/td]

[td]

Toronto

[/td]

[/tr]

[tr]

[td]

Global/North America/Mexico

[/td]

[td]

Mexico City

[/td]

[/tr]

[/TABLE]

Anyone have ideas of displaying this data better then just a spreadsheet? My spreadsheet has over 20000 paths(unique) with corresponding values(unique) in the following columns.
Usually these paths are like above but more extensive, ie it will stay at a continent and go through every country as a child to that parent.

I would like some sort of roll up display.

Any help would be appreciated, thanks.

wonka1234 · Aug 17th 2016

Re: Userform Tree Structure / Node Help

bump

**cytop** · Aug 18th 2016

Re: Userform Tree Structure / Node Help

Just like your thread title says - you could use a TreeView control to display the data allowing the user to collapse/hide nodes...

Your post is light on details of the structure of the data, but assuming it is exactly as you mentioned:
Global/Continent/State with a city in the corresponding cell in the next column, then you can load a TreeView (named 'tv' - it can be on a userform [userform1] or placed directly on a worksheet) using something like

Code

Public Sub LoadTV()


   Dim r As Excel.Range
   Dim n As Node
   Dim v As Variant
   Dim iTemp As Integer
   Dim tv As TreeView
   Dim ws As Excel.Worksheet
   
   On Error Resume Next
   
   Load UserForm1
   Set tv = UserForm1.tv
   Set ws = Sheets("Sheet1")
   
   '// Loop the range of used cells in Col A
   For Each r In ws.Range(ws.Range("A1"), ws.Range("A" & Rows.Count).End(xlUp))
            
      '// Split based on the '/' character
      v = Split(r.Value, "/")
         
      '// Loop each element of the array
      For iTemp = LBound(v) To UBound(v)
         
         '// For later loops, n will be assigned to a node. Clear it
         Set n = Nothing
         
         '// And try to assign to a node with the specified key
         Set n = tv.Nodes(v(iTemp))
         
         '// If that node doesn't exist...
         If n Is Nothing Then
            
            '// If this is the first iteration of the loop
            If iTemp = LBound(v) Then
               '// Add as a 'Root' node
               Set n = tv.Nodes.Add(, , v(iTemp), v(iTemp))
            Else
               '// Else add as a child of the node with the key defined in v(itemp-1)
               Set n = tv.Nodes.Add(tv.Nodes(v(iTemp - 1)), tvwChild, v(iTemp), v(iTemp))
            End If
         End If
         
         '// Expand this node
         n.Expanded = True
         
      Next
      
      '// If there's a value in the corresponding cell in the next column...
      
      If r.Offset(, 1).Value <> vbNullString Then
         '// Add as a child of the last parent node found
         '// No key is used as it is probablt the City may be named the same as State/County
         '// you do not want this node to be found when adding from the delimited list.
         tv.Nodes.Add n, tvwChild, , r.Offset(, 1).Value
      End If
   Next
   
   UserForm1.Show
   
End Sub

Display More

This will also handle 'extended' information like

[table="class: grid"]

[tr][td][/td]

[td="bgcolor:#8E5A2E, align:center"]A[/td][td="bgcolor:#8E5A2E, align:center"]B[/td]

[/tr][tr]

[td="bgcolor:#ECF0F0, align:center"]11[/td][th]Global/North America/USA/Illinois[/th][th]Chicago[/th]

[/tr]

[/table]

Where the state is included in the delimited data. Admittedly it would look odd with just 1 state as that will appear on the same level as the other cities - just mentioned as an example.

wonka1234 · Aug 18th 2016

Re: Userform Tree Structure / Node Help

Hey, sorry just to clarify, do I have to create a userform first then run the code? Or just run the code and it should create it?

Also if I want to add addition columns in my original data such as area codes in the cities, how could I add this to the code?

**cytop** · Aug 18th 2016

Re: Userform Tree Structure / Node Help

You have to add a userform and then a TreeView control to the userform. You will probably have to Right-Click the Control Toolbox and select 'Additional Controls' and pick the TreeView from the list displayed. The Treeview should be renamed to 'tv' when added to the userform.

As I mentioned, your "post is light on details of the structure" so assumptions had to be made. If there is additional data then that really should have been made clear in your initial post but you should be able to follow the code in my previous message and figure where and how to add the additional data.

wonka1234 · Aug 18th 2016

Re: Userform Tree Structure / Node Help

Thanks so much! Is there a way to identify States or/and even cities (col b) ? perhaps a color ?

is there a search function I could add?

I want a user to be able to search a node and identify states/cites/area codes.

**cytop** · Aug 18th 2016

Re: Userform Tree Structure / Node Help

You can colour individual nodes if you want.
Any search facility would need to be coded but it relatively easy to do.

But I have a feeling a TreeView is not the best approach for what you want to do

**cytop** · Aug 19th 2016

Re: Userform Tree Structure / Node Help

Quote

Anyone have ideas of displaying this data better then just a spreadsheet

Perhaps if you explained the exact structure of the data and how this will be used then some suggestions might be possible.

wonka1234 · Aug 22nd 2016

Re: Userform Tree Structure / Node Help

Quote from cytop;775829

Perhaps if you explained the exact structure of the data and how this will be used then some suggestions might be possible.

Hi, the sample data layout is like this:

[TABLE="width: 500"]

[tr]

[td]

Path/Node

[/td]

[td]

City

[/td]

[td]

Area codes

[/td]

[td]

Population

[/td]

[td]

Governers

[/td]

[/tr]

[tr]

[td]

Global/North America/USA

[/td]

[td]

New York

[/td]

[td]

45235

[/td]

[td]

10000000

[/td]

[td]

Mike Ross

[/td]

[/tr]

[tr]

[td]

Global/North America/USA

[/td]

[td]

Miami

[/td]

[td]

235252

[/td]

[td]

2

[/td]

[td]

Louis Litt

[/td]

[/tr]

[tr]

[td]

Global/North America/Canada

[/td]

[td]

Toronto

[/td]

[td]

252452

[/td]

[td]

645

[/td]

[td]

Harvey Specter

[/td]

[/tr]

[tr]

[td]

Global/North America/Mexico

[/td]

[td]

Mexico City

[/td]

[td]

25235

[/td]

[td]

52562

[/td]

[td]

Morgan Freeman

[/td]

[/tr]

[/TABLE]

I am using it as a dashboard which will be used for different users. So them being able to view the data in an easy and pretty way is a must.
We want to see the parent and the children nodes and the corresponding city, area codes, pop, and governers data for each node. It should be rolled up into the parent node.
ie. North America node will have all the data that the countries have individually.

I need some sort of dashboard view.

is this detail that would help?

wonka1234 · Aug 24th 2016

Re: Userform Tree Structure / Node Help

bump

Userform Tree Structure / Node Help

Participate now!

Share