• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 35
  • Last Modified:

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.
0
hawkeye_zzz
Asked:
hawkeye_zzz
  • 6
  • 5
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now