Create macro for automatic userform/treeview creation

I'm looking for an automatic userform/treeview of data as per the attached, compiling layer by layer for visible columns, with the text taken from the columns with "Name" in Row 2. Search down for next change of "Name" to get the next node.
Ignore Index and Description columns.
Don't include the next layer if that would add more nodes than the Excel userform limit.
LVL 1
hawkeye_zzzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
No attachment.
0
hawkeye_zzzAuthor Commented:
Hmm, not sure why, here again
Excel-Hierarchy.xlsx
0
Martin LissOlder than dirtCommented:
not sure why
It's a common mistake to choose the file to upload and then not click the blue, 'Upload File' button.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Martin LissOlder than dirtCommented:
This may take a while. If there's any problem before I finish it I'll let you know.
0
hawkeye_zzzAuthor Commented:
No worries. Thanks
0
Martin LissOlder than dirtCommented:
This is proving difficult and it would be easier if you could supply we with a taxonomy consisting of
  • No more than 20 rows
  • At least two Indexes in Layer 1 and the same in at least one more layer
  • Perhaps 4 layers total including a layer with the actual name of the creature (because I assume that you want to see that)
  • And using the cells of a worksheet to imitate the indentation of the nodes of the treeview, show me what you'd like to see in the treeview.
0
hawkeye_zzzAuthor Commented:
Here is the revised file.
Note that this file has pseudo terms due to the confidential nature of the real Taxonomy.
The treeview would be exactly like the one you already did but I've put it in a separate tab
Sorry it's turning out to be hard.
Excel-Hierarchy-4-layers.xlsx
0
Martin LissOlder than dirtCommented:
Sorry this took so long. I spent a lot of time trying to write a recursive function for this (which I'm not good at) and I finally gave up and tried a completely different approach where I generate key vales for all the potential treeview nodes and place then in a worksheet called "Keys". It's then a less complicated way to go through those keys and generate nodes from them. If you have questions about the code please feel free to ask.
29085348.xlsm
0
hawkeye_zzzAuthor Commented:
Quick update, my spreadsheet has now grown to 1m rows and 18 levels. So every time I press a button I have to wait 5 min.
I tried to run it by exporting and importing the userform. When I ran it, it said it couldn't run, check permissions. I have, macros are enabled. I'm trying a few things included much reducing the file size for testing. But it's my last day before holiday so a bit hectic.
0
hawkeye_zzzAuthor Commented:
I dropped it down to one tab and 500k lines.
It paused for 6 min then came up again with:
"Cannot run the macro "E:....". The macro may not be available in this workbook or all macros may be disabled. Do you know why this would be?
I've imported the userform into that file and created a macro button linked to the macro in the same workbook
0
Martin LissOlder than dirtCommented:
I made some changes in this workbook that should make it faster, but a million rows is a lot.

  • In the workbook there's code that writes to the Keys worksheet, and that physical updating takes time so I added a one-liner that causes the screen to be updated just once at the end and that should speed things up.
  • Each node of a treeview must have a unique key and the routine I was using to do that would get random numbers from a set of 10 million and if the number was previously used it would get another one. Normally 10 million is more than enough but with a million rows and 18 levels it would soon run out of unique numbers and loop continuously. I changed it to 10 billion.
  • I added a constant in the code that represents the last column. Currently it says Const LAST_COL = "BB" and if the number of columns increases or decreases you just need to change that value.

If you run into any problems, please don't wait to contact me. I may not be able to help since our environment are different but try me anyhow.
29085348a.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.