I was actually looking for some code to populate the TreeView because I have never worked with a TreeView in VBA before. It can handle 1000 nodes no problem but 10k would be far too much (actually that's pushing even the aX treeview) You may or may not same level nodes sorted though the treeview could do that for you.Īctually it's not strictly necessary to sort rows as long as they are complete, it just makes it much easier to work with.įWIW there is a VBA treeview available.
With that it should be obvious how to populate the nodes. That should give you a structure like this (where the root is your CEO) Then your rows should be sorted like this However you are missing a few rows, lets sayġ4: like row 4 but with Region A in col-Cġ5: like rwo 14 but with District A in col-Dġ6: like row 9 but with District B in col-D If your data is intended to convey what I suspect it's meant to, the only redundant row I see is row-10 which duplicates row-9. Hold back from downloading the Access version until beta 024 or later (coming soon). Such a system could allow for the data to be maintained in 2 columns with "keys" in the first and value in the right.Īssuming the keys are unique, and parent nodes have been created before adding any children (sort the key column first), no recursion would be required to build a treeview. I agree maintaining such a structure is prone to errors without a well constructed data entry system. Or, the tree structure in appropriate columns with all the values in a single column to the right would be another way of arranging thingsĪlthough it's possible to construct a pseudo relational type of DB in Excel, best explained in "PED", in the main Excel is restricted to 2-D tables and such a structure is inevitably going to be recursive in nature, indeed hierarchical. Set currentRowRange = rngBC.I guess you are referring to my suggestion for how the OP might correct, complete and rearrange his data(?) His example data appeared to represent a typical organisational structure, though I can relate to your "parts-explosion" analogy.įWIW the data only needs the rightmost cells but in the correct columns.
'Set rngZones = wsZones.Range("A1:A" & lngRows) Set rngBC = wsZones.Range("B1:C" & lngRows) Also get the Range for B,C and use a counter to indicate which row you are on Private Sub TreeView_Populate()
Get the full range for columns a to loop the rows. Is this feasible? I basically need a "temporary" range containing the values from B et C columns to build the children nodes… In the vba code I added some comments to where Im failing…Īll help/suggestion will be greatly appreciated! I would like all the check boxes to be selected by default… ZonesTree.LineStyle = tvwRootLines in my form initialize sub-routine, which create check boxes for each element of the tree. Key:=CinemaName(here it will be B column), CinemaInfos(C column)
'Need the range from Columns B and C until the next Value in the A Column
'We have a group name in the A columns so we attach it to the tree Set rngZones = wsZones.Range("A1:A" & lngRows) LngRows = wsZones.Range("A65536").End(xlUp).row Set wsZones = wbBook.Worksheets("Cinemas") Here is what I have so far: Private Sub TreeView_Populate() Sorry for the representation its lame but you get the picture… The sheet references a list of cinemas, already organized in a "tree view", where the A Column refers to a cinema group name, the B and C columns refer to the particular cinema names and infos, for example: A1:Independent Cinemasįollowing this description, I want the treeview to look like: +-A1 I am trying to build a TreeView in an Excel Form from an excel Sheet with 3 columns. Sorry for my Noobness with Excel/Vba… coming from a unix world…I need help!