Populating a treeview control based on a data range on two columns

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Greetings.

    I would like to populate a treeview control with data on two colums on a range. I have never used it so im unfamiliar with its events and properties.

    I have two columns of data in columns A and B, titled "Category" and "Item".I would like to populate treeview control with UNIQUE values in the "Category" column as the parent nodes, with their corresponding values in "Item" as the child nodes. I want the code to fetch the values of each parent and its corresponding children. The data range is as:

    Category Item
    Beverage Coffee
    Fruit Apple
    Snack Bisquit
    Beverage Lemonade
    Fruit Mango

    e.t.c, such that the data on the treeview control is as:

    Node(s) Child(ren)
    Beverage - Coffee
    - Lemonade
    Fruit - Apple
    - Mango


    The data range ( "A1:B500") is not formatted in any way.I want the code to populate the control on loading a form containing the treeview control.Please comment on any code so that I understand what is going on.

    PS: I would also later want to run a procedure based on the selections.

    For the sake of clarity, Please find attatched a workbook.Thank you in anticipation.


  • Re: Populating a treeview control based on a data range on two columns

    Add the following to the UserForm

    Call the procedure from any relevant event like UserForm_Initialize.

    Some comments:

    The basic flow in the code is:

    • Loop the used cells in Col A
    • Try and add a node using the cell value as both the Key and Caption for the node
    • If an error occurs, then a node with that key already exists so set a reference to it
    • Either way, the code now has a reference to a parent node
    • Add a child node to the referenced parent
    • Next cell

    Nodes are added as follows:

    Set nParent = tv.Nodes.Add(, , c.Value, c.Value)

    That adds a node to the treeview and returns a reference to it in nParent. You will note 2 parameters are blank; these relate to the parent node for this node. There is no parent in this example so are skipped. The other 2 parameters are the node key (must be unique, if used, or can be left blank) and the node text. This example has the node text the same as the key for simplicity.

    To add a child node, you need to refer to the parent.

    Set nChild = tv.Nodes.Add(nParent, tvwChild, c.Offset(0, 1).Value, c.Offset(0, 1).Value)

    This relationship parameters are now used. nParent is the node to use as the 'reference', tvwChild says the node being added in a child of nParent. The other options for this are tvwPrevious, tvwNext, tvwFirst & tvwLast; Previous and Next add the node before/after nParent. First & Last add a node as the first or last node in the same 'branch' as nParent.

  • Re: Populating a treeview control based on a data range on two columns

    THAAAAANKSSSSSS !!! That worked absolutely PERFECTLY. Thank you, and thank you for the quick response.

    I have a few issues though,

    1. How can I have all the children visible on loading the form containing the control? The parent nodes are all collapsed.I need the children visible, since the end users do not understand the control. I do not know much about the control, but im thinking something like:

    Dim i
    For Each i In tv.nodes.count
     tv.Nodes(i).Expanded = True

    This doesnt work, please guide me further.

    2. I have added checkboxes on the nodes, so as to enhance multiple selections. I wish to run a procedure based on the selections.How can i specify the selections ? For instance,

    For i = 1 to {all parent selections}
            Run procedure
    Next i
    For j = 1 to {all children selections}
           Run procedure
    Next j

    PS: I would be more than grateful if the selections can be converted into a new range or an array.

    Thank you in anticipation.


  • Re: Populating a treeview control based on a data range on two columns

    To expand the nodes...

    Just looking at your other comments.

  • Re: Populating a treeview control based on a data range on two columns

    Changed - comments in the code but basically a new ROOT node is added. This uses the text in cell A1. The individual categories are now children of this node. Done to faciliate iterating through all the categories.

    Another change is to store the Cell address in the TAG property of each node. This can be used to construct a range using the UNION function later, but am not sure how you want that to work.

    One thing to

    Do you want to run a procedure on all selected Child nodes (remember these are the lowest 'level' now after the root node was added) or just those children for a selected category...?

  • Re: Populating a treeview control based on a data range on two columns

    Thank you for all your assistance thus far. I have been confused by the code above, especially where you mentioned tags.

    I wish to run separate procedures on the parent selections and on the child selections. That is why I hoped the selections would be converted into a range or an array. I am hoping for something like:

    For i = 1 To {all parent selections}      
    Run procedure  
    Next i

    For j = 1 To {all children selections}  
        Run procedure  
    Next j

    Please guide me further. Thank you in anticipation.


  • Re: Populating a treeview control based on a data range on two columns

    The TAG property (and just about every control has it) can be stored to store textual information for the control. In the example above, as nodes were added to the TreeView, the cells address the node comes from is stored in the TAG property.

    You mentioned you "hoped the selections would be converted into a range" - this is one way of doing it. For example, iterating all the nodes, checking it they're 'Checked'

    Not sure if I quite understand exactly what to do, so just a general example.

  • Re: Populating a treeview control based on a data range on two columns

    THANKS ! I have not yet fully figured it out, but I understand what is going on, and believe will figure it out. THANK YOU.

    However, I've been a bit confused along the way, and since I'm VERY pressed for time, I seek your indulgence to help me clear it out.

    I have designed a custom auto-filter form for a very large data range. Its rather complicated for me, but every other part is complete except for the treeview part.

    Given the initial data range (in the workbook), if user selects a specific parent node, then range (A1:B10) in sheet1 is auto-filtered by the parent selections in the treeview, with the autofilter criteria range being the selection(s). For instance, if user selected "Beverage", then only rows where the column A value is "Beverage" are visible.

    Further, If the user selects "Soda" and "Coffee" in the child nodes of the "Beverage" parent, then the autofilter should:

    1. Autofilter column A of the range, with the Parent "Beverage" as the criteria range (even if "Beverage" parent was not selected).

    2. Autofilter column B of the range, with the selections passed as the autofilter criteria range, i.e "Soda" and "Coffee".

    Such that ONLY columns where the value in column A is "Beverage" AND columns where the values in column B are "Soda" and "coffee" are visible.


    If user selects " Beverage" and "Fruits" but does not specify the items, then ALL beverages and fruits items are visible.

    It should also be flexible enough to allow multiple selections of parent and child nodes. I hope im not vague. Please ignore the root node for now.

    I understand that passing the multiple selections as auto-filter criteria might be problematic, that is why I hoped the selections would form a range or an array. Then I hoped to proceed from there.

    I really hope to complete this very soon. Thank you in anticipation.

  • Re: Populating a treeview control based on a data range on two columns

    Try the attached.

    Click the button on Sheet1 to load the userform. When the form is loaded, click the button to load the treeview. Done like that as I tested with approx 10,000 rows and it took about 3 seconds to load the treeview. Didn't want that delay on loading a userform whereas a user will probably more likely accept a slight delay while data is loaded to the treeview after clicking a button.


    • If any Category node is Checked or Unchecked then all child nodes for that category set the same
    • If any Product node is Checked, then its parent Category node is Checked.
    • If all Child nodes for a Product are unChecked then the Parent is Unchecked.

    To load the Treeview, first a unique filter is applied to Col A then the visible rows are added as the Categories. Then a unique filter applied to column B for the Products and each visible product added to its parent category. The filters take a little time to be applied (3 or so seconds) but that is much preferable to scanning an unfiltered list and trying to find/add a couple of thousand nodes (as mentioned above, tested with 10,131 rows) in the treeview.

    As items are Checked/Unchecked, the filter is updated immediately. There is no noticeable delay in applying/clearing filters so the only delay is in first loading the treeview...

  • Re: Populating a treeview control based on a data range on two columns

    Thanks alot for your assistance.

    I tried running the procedure by clicking the treeview control, and it results to an error message. Seems a certain feature is not available on my machine.

    An error states "Could not load object because it is unavailable in your machine" then another error

    "Compile Error Cannot find object or library " it also highlights the procedure "Private Sub tv_NodeCheck(ByVal Node As MSComctlLib.Node)"

    The same happened when I tried on another computer.

    Please advice.

  • Re: Populating a treeview control based on a data range on two columns

    That was developed using 2007 and the versions of the TreeView control are not compatible. I did try to keep compatibility, but it seems it references a newer version of the control.

    Try deleting the TreeView from the userform in the VBA dev environment. Then, right click on the toolbox and select 'Additional Controls'. Scroll down and find Treeview version 6 (There will probably be 2 versions listed, one with a 5, the other with a 6. Using an Android tablet now so can't check the exact name).

    Click on OK, or whatever, and then add the Treeview from your toolbox to the form. You will need to rename it to 'tv' in the properties window - press F4 to view that.

    That will probably work, but if not post back and I'll have a look when I get back on my pc.

  • Re: Populating a treeview control based on a data range on two columns

    Hi, Ive tried to figure it out but without success.

    I'm actually using Excel 2010 and have tried it in Excel 2007 and the error persists. Ive also tried re-designing the userform and inserting a new treeview and renaming it appropriately, but the end result is the same. I have the correct object references in the VBA environment, and can comfortably use other treeview controls. Seems to me that the error is in the coding.

    Please intervene.

  • Re: Populating a treeview control based on a data range on two columns

    The problem is with the TreeView - that seems to be the object that cannot be loaded which leads to the second Compile error. I would have thought that you adding a new treeview and renaming it to suit the code would have overcome that... the only thing that stands out as possibly being a problem is the declaration

    Private Sub tv_NodeCheck(ByVal Node As MSComctlLib.Node)

    If you do start a new form with a 2010 treeview, make sure you only copy code excluding the procedure declarations.

    On another tack, there does seem to be some discussions around the place regarding treeviews from earlier versions in Office 2010, including this one which includes a link to a Microsoft Hotfix download. I'm not saying it's a solution as I cannot check it, but it may be something worth investigating.

    Just to rebut.


    Seems to me that the error is in the coding

    The error is not in the coding, it works perfectly well on my machine; the error is incompatibilities between your version of Excel and mine - that's a Microsoft issue.

  • Re: Populating a treeview control based on a data range on two columns

    I have once again tried the best I can, without any success. I have even tried designing a new form interface with new controls with the same properties, reusing the same code, but keep getting error messages.

    It seems to me that the treeview object is more complicated than I thought. I think at this point, since im pressed for time and set to miss my project deadline, ill call it a wrap, though am very grateful of your assistance. I have learnt alot about the control, and I hope to try and make it work sometime later. I have understood populating the control, I believe I can make the filters work.

    Once again, THANK YOU for all your assistance.


  • Re: Populating a treeview control based on a data range on two columns

    OOPPSS !!

    Hi again, my apologies, I have a quick idea of instead using a listbox control instead of a treeview..just to complete and submit THIS project..

    Please help me populate a listbox control with the same data, with items such as:

    Beverage- Tea
    Beverage - Coffee
    Beverage - Soda
    Fruit - Mango
    Fruit - Banana


    The data is, or course in columns A for Category and columns B for the corresponding Item.

    I hope to also auto-filter the same data at the click of a button, exactly the same way I expected the treeview to work.Please also allow multiple selections.

    Thank you in anticipation.

  • Re: Populating a treeview control based on a data range on two columns

    Paste into a userform with a ListBox

    Listbox properties:
    Name: LB
    Columncount: 2
    List Sytle: 1 - frmListStyleOption
    MultiSelect: 2 - frmMultiSelectedExtended

    You might also need to set the ColumnWidths property. I used "49.95 pt;49.95 pt" (Actually it was 50,50 but Excel decided otherwise)

    Considering time constraints, not tested too exhaustively and some of the code is straight out of the macro recorder

    I would be interested in having a look at your workbook with the treeview - to keep it private would you be willing to email to me? - I'll add any comments to this thread

  • Re: Populating a treeview control based on a data range on two columns

    THANKS !! That code worked PERFECTLY.

    About the Treeview data, Im sorry I cant share it, since it is VERY sensitive and personal, and had signed a non disclosure confidential agreement. The workbook I provided should just suffice, since it is in a similar format.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!