If you have ever used the Treeview control from the "Additional controls" section, then you know what a versatile control this is to show hierarchically organized data. There are a couple of problems with this Treeview control:
Especially the second point convinced me to develop a custom-made Treeview "control", that only uses the native Office forms controls. A couple of weeks after I started building this I tricked Peter Thornton into helping me with it.
The screenshot below shows both our new Treeview (left) and the Windows one (right) side-by-side in their simplest display mode (read on, there are even prettier screenshots further down the page):
Two treeviews, left: VBA tree, right: Common controls tree
In the following pages I'll show what our treeview can do and explain how to put it to use in your own VBA project.
Windows screenshot:
Treeview demo on Windows Excel
Mac screenshot:
Screenshot of treeview on Mac Excel 2011
The basic plumbing and code structure of this treeview control was devised by me. However, without the help of my friend and fellow (former) Excel MVP Peter Thornton, lots of functionality would not have been available now. For that I sincerely thank Peter!
Furthermore, Access MVP Ben Clothier was kind enough to make the necessary adjustments to incorporate the treeview in an Access form
Also: Fellow Excel MVP Ron De Bruin ensured the treeview also works on MAC Office 2011, Thanks Ron!
All code in the treeview is (c) JKP Application Development Services and Peter Thornton (the Authors). It remains our sole intellectual property.
However, we're offering this treeview to you at no cost. You get an unrestricted license for use in any VBA project you like. You're free to modify any part of the code at will.
We do request that you:
And please mention the source of the treeview (including a link to this page) in your helpfile, manual and/or on your about screen.
We're always interested to see how people have implemented the VBA Treeview. So please feel free to send a screenshot with a brief description or relevant details.
You use this control at your own risk: The authors accept no liability whatsoever for any damages which may arise due to the use of our treeview.
Many, many hours were spent developing this treeview. Although we developed it for use in our own projects, we are giving it away for free!
Nevertheless, we would really be pleased if you actually express your appreciation in a more "tangible" form. So here is a paypal donation button at your disposal:
The Excel workbook contains most of the documentation (on the tabs of the workbook), so I recommend you to at least download the Excel version. The Access version has instructions on its main form (click the "How do I...?" button) on how to implement the treeview in your own projects.
Download the treeview sample Excel workbook (including documentation) (build 026.5, 10 Jan 2023, downloaded 46.792 times)
Download the treeview sample Word document (build 026.5, 10 Jan 2023, downloaded 15.343 times)
Download the treeview sample Access database (build 026.5, 10 Jan 2023, downloaded 38.302 times)
If your project needs more features and/or better performance than our free treeview, or if you need a listgrid control you've come to the right place as well.
The professional version of the treeview control has exceptional performance. Even with tens of thousands of nodes it will load quickly and remain highly responsive. It also has several new features including drag and drop. Timing experiments have shown that the pro version of our treeview outperforms the common controls treeview.
Our new ListGrid combines most of the functionality of the ActiveX Listview and Flexgrid controls with many extra useful features. It is the result of extensive beta testing by some of our treeview users, thanks guys!
The screenshot of the demo below gives an idea of just some of its capability.
The Pro Treeview and ListGrid are available for 32/64 bit Excel and Access. The Excel version will also work in Mac, one or two features are disabled for Mac but we’re working on it. Unlike our free treeview they are self contained in their own files and designed to work more like real controls.
For more details and if interested in a trial license to try either of these ‘controls’ please contact us:
Pro Treeview enquiry
Pro Listgrid enquiry
Please note that the pro versions are paid versions. Pricing available upon request by using the links above (The download files above contain a Word document which also gives more details about the pro controls).
Another often used control is the calendar control. This control has the added problem that it has been deprecated with Office 2010 (where we're supposed to use the date picker control). Frankens Team created an all-vba alternative using very similar techniques to what we've done here.
Ron de Bruin created a Date Picker control for MAC Excel.
All comments about this page:
Comment by: Benzadeus (22-2-2013 23:12:43) deeplink to this comment
That's amazing! I'm already testing it and works great. You guys created a control better than the original TreeView.
Comment by: Jeff Weir (23-2-2013 11:09:01) deeplink to this comment
This is cool. Note i'm getting an "Application-defined or object-defined" error when I hit the Dump Data button in the GetData2 sub, with rng(i, .Level) = .Caption highlighted. Problem is .Level is set to zero. Haven't attempted to walk through the code yet to get my head around what's happening here.
Comment by: jeff weir (23-2-2013 11:52:15) deeplink to this comment
This is freakin genius! Some questions and thoughts off the top of my head, none of which diminish the fact in any way that this is freakin genius: 1. I take it you can't set showmodal = false? 2. Would be cool if you could delete a mode using the delete key on keyboard, especially given you allow the use of copy and paste shortcuts. 3. Would be cool if you could export a picture of the treemap to the spreadsheet. 4. Would be cool if this could be built in the spreadsheet itself, rather than being tied to a userform. 5. Wouldn't it be cool if you could view pivottables like this! Nodes reflect the hierarchy of the pivot, plus each node display the aggregated data measure, and the more you drill down, the lower and lower aggregation you get.
Comment by: Jan Karel Pieterse (23-2-2013 15:29:20) deeplink to this comment
Hi Jeff, Benzadeus, Thanks! We'll take note of the comments and bugs reported here and use them for future updates!
Comment by: Peter Thornton (23-2-2013 19:19:09) deeplink to this comment
Sorry about the error in GetData2, something got changed at the last moment! It will be fixed in the next version but in the meantime - In Sub GetData2() change Rng(I, .Level) = .Caption to Rng(I, .Level + 1) = .Caption Re 1#, There is no problem as far as the treeview is concerned to show the form as vbMmodal or vbModeless Re 2# Key events are exposed and a node remove (delete) method is built in, so it's very easy to include your key-delete - In the demo form, in mcTree_KeyDown add the following between Select..EndSelect Case vbKeyDelete mcTree.NodeRemove cNode mcTree.Refresh Note that will also delete the active node's child-nodes if any. You might want to include some code to validate OK to delete before calling NodeRemove. re 3# Do you mean literally and image of the TreeView? It's simple to copy a screenshot of the form to a sheet with APIs, but you'd need to crop the treeview (actually that would also be possible with APIs but a lot more work). But I would have thought the data dump example could be adapted much more simply to your requirements. Re 4# Not quite sure what you mean, view the treeview embedded on the sheet sheet, like an ActiveX control? Re 5# <Smile> It ought to be possible to load a pivot-table view into the Treeview
Comment by: Jeff Weir (23-2-2013 19:59:14) deeplink to this comment
Thanks Peter. ALso posted this over at the DDOE thread, but my comment is still in moderation there. One thing a added to that comment is that a search method would be way cool, where it searches for a text string, identifies which nodes contain it, and collapses any downstream nodes that don't contain the search term I'd be interested in hearing about what kinds of things people use treeview for, too. Thanks again.
Comment by: Jeff Weir (23-2-2013 20:44:25) deeplink to this comment
In terms of #3, adapting the datadump is simple enough: For Each cNode In mcTree.Nodes <b>If cNode.ParentNode.Expanded And cNode.Level > 0 Then </b> i = i + 1 With cNode rng(i, .Level + 1) = .Caption End With End If And I guess it's even possible to replicate a static picture of the treemap on the sheet using the SmartArt hierarchy. For instance, there' some code at http://www.mrexcel.com/forum/excel-questions/554224-using-visual-basic-applications-smart-art.html But in terms of #4 I was thinking more about a user-configurable treemap - not a static picture - using shapes, or even the Hierarchy WordArt functionality. (Aside: I see that the macro recorder doesn't work with SmartArt shapes in Excel 2013)
Comment by: Peter Thornton (25-2-2013 11:04:22) deeplink to this comment
Jeff, # Re your search and collapse combination. All of that's straightforward with what's already built in. Briefly, start by fully collapsing the tree with "mcTree.ExpandToLevel 0". Then recursively loop 'branches' of child-nodes, if a match is found, call "mcTree.ScrollToView myNode". ScrollToView was designed to display a particular node. In a large tree it would be better to roll your own code to expand as the same way ScrollToView does (just the While/Wend bit), then call .Refresh when done. re #3 Excellent. It needs a little bit more, this should dump a snapshot of the currently displayed (ie expanded) nodes to a sheet.
Comment by: Carlos Manteigas (1-3-2013 00:02:09) deeplink to this comment
I entirely agree with Jeff on the use of the word genius. This was exactly what i've been searching for. Thanks! I think it would most interesting if the treeview could retrieve a previously Dumped data also. Thanks again.
Comment by: Jan Karel Pieterse (1-3-2013 15:33:40) deeplink to this comment
Hi Carlos, Thanks for the compliment! Just to be clear: We created the treeview control, and we created an example file so you can see how to use it. However, implementing the control in your project is something that is up to you, which includes writing code to populate the control from whichever source you have. The current download has a sample project which populates the tree from an Access database.
Comment by: Lisa Green (3-3-2013 17:57:38) deeplink to this comment
Stupendously excellent ultra mega hyper Good!! Love it! Lisa
Comment by: Jan Karel Pieterse (3-3-2013 19:37:51) deeplink to this comment
Hi Lisa, Thanks! :-)
Comment by: Stefan Hellwig (6-3-2013 15:26:48) deeplink to this comment
Lisa expressed it very well, this is brilliant work! Thank you very much for this great piece of software! It works extremely well. I did not observe any problems while using it. Since I am using it with Word instead of Excel I made a few (minor) changes to get it to work there (mainly the cursor). I also implemented the "EnsureVisible" behavior of the original MSComCtl. My code also raises a double-click event when editing is disabled. I will be sending you all my modifications directly by e-mail so that you can have a look and/or incorporate them as you please. Again, thank you very very much! Best Regards, Stefan.
Comment by: Jan Karel Pieterse (6-3-2013 19:31:54) deeplink to this comment
Hi Stefan, Thanks for your compliments! We'll certainly look at your modifications and take from it what we might be able to use!
Comment by: Hermanito (7-3-2013 23:31:21) deeplink to this comment
Amazing piece of work, I will certainly dig deeper into the code to learn how you did it. While playing with the Demo in Excel 2013, 64-bit, I did notice a bug (I think). Not sure if it also does that in other versions or bitness. Reproducable as follows: - start the demo - click the Demo Treeview button to fill the treeview - uncheck the Full width checkbox - uncheck the Show lines checkbox - check the Full width checkbox - ERROR: clsTreeView Refresh: Error in BuildRoot: Object variable or With block variable not set Apparently, both checking or unchecking the Full width with the Show lines unchecked, will trigger the error. The weird part is that resuming after the break on 'Stop' works, but then a lot of the other interactions start causing the code to break into debugmode at the same point Also, something weird is going on with a horizontal scrollbar appearing for no reason...
Comment by: Jan Karel Pieterse (8-3-2013 13:20:21) deeplink to this comment
Hi Hermanito, Thanks for reporting!
Comment by: Peter Thornton (8-3-2013 17:10:03) deeplink to this comment
Hi Hermanito, Well spotted, I was hoping nobody would until the next version! It "crept" into v019, if you want to fix yours in advance of v020, in Let ShowLines(), move the "If bOrig <> mbShowLines..Refresh" above the preceding "End If"
Comment by: Comet (14-3-2013 04:12:17) deeplink to this comment
Hi Jan Karel Pieterse, Thanks for your product. It's great! I got mistake when I created some Root Node (or one Root node) without branch. When I created the Root Node without branch then left click once then double Enter, the program show "run time error 91" How to fix it? Again, thank you very very much! Best Regards, Comet.!
Comment by: Jan Karel Pieterse (14-3-2013 08:59:06) deeplink to this comment
Hi Comet, Thanks for reporting, we'll fix that for sure!
Comment by: conim (2-4-2013 12:04:45) deeplink to this comment
Great project, indeed! Two things that would make it even better would be: 1) Possibility to use dashed lines instead of solid 2) Expanders to look more like original ones and not like icons/images. In other words, if MSForms treeview could look more like Common controls treeview, would be fantastic.
Comment by: Jan Karel Pieterse (2-4-2013 13:54:31) deeplink to this comment
Hi Conim, Thanks for your feedback. Unfortunately, 1 is hard to implement, as there are no controls with dashed borders. 2 has already been implemented, you can choose whether to use image expanders or non-image expanders, which do look more like the original one (see first screenshot on this very page).
Comment by: Evelyn (3-4-2013 10:19:08) deeplink to this comment
Thank you for this nice TreeView. Really great work! In the MS TreeView it is possible (with relative and tvwChild) to add a child-nodes to a specific parent at any time. Are you planning to include something like that in your Code? Best regards, Evelyn
Comment by: Jan Karel Pieterse (3-4-2013 12:01:23) deeplink to this comment
Hi Evelyn, Thank you for the compliments! With regards to adding childnodes to existing nodes, our treeview works slightly different from the Common Controls treeview, I agree. It functions similar to the VB.NET treeview; you add children to a node by using the addChild method of a node. However, you can step through the nodes of the tree by using the clsNode's method called GetChild recusively, provided you have given your nodes unique keys. You could use a function like this to return a specific node by its key:
Comment by: Peter Thornton (3-4-2013 13:29:40) deeplink to this comment
Hi Evelyn, Just to add to Jan Karel's comments, although we think the clsNode.AddChild() method is more efficient, for "backwards compatability" with the the old treeview we have already included almost exactly what you ask. In the documentation (clsTreeView sheet) check out the .NodeAdd() method. This is very similar to the original's Nodes.Add() method (note the difference with the dots). Two small differences, our constants are named like "tvChild" vs "tvwChild" (but you change those). Also, although you can identify the "Relative" with its string key (as in the original), as a bonus you can simply pass a reference to the node, eg
Comment by: Peter Thornton (3-4-2013 13:36:44) deeplink to this comment
Hi Conim, As Jan Karel explained we can only do continuous lines (or no lines), though they could be any colour you want. Concerning the expander icons, you can make whatever icons you want. Take screen shots of any icons you prefer, size and save them to file, then load into the relavant image controls. Do similar for checkboxes.
Comment by: Alex (3-4-2013 20:13:56) deeplink to this comment
Hi, Superb component! Works great. Some small changes I had to add: 1/ When you start without an active node, and then click on a node, the nodeClick Event is not fired, so I added it in the mctlControl_Click: Private Sub mctlControl_Click() If moTree.ActiveNode Is Nothing Then Set moTree.ActiveNode = Me moTree.NodeClick Control, Me 'AVDV End If 2/ When using the arrow keys left & right, we are not notified when a node has changed. This was also easily fixed by adding the NodeClick in the right places. Example: Set ActiveNode = ActiveNode.ParentNode ScrollToView , 1 NodeClick ActiveNode.Control, ActiveNode 'AVDV 3/ Sometimes the active node is not visible, but I need to investigate this more to know what is really happening. Already a big thanks, this control is really something we can all learn from. A decent listview control based on these principles would also be awesome. Kr, Alex
Comment by: Jan Karel Pieterse (4-4-2013 09:08:55) deeplink to this comment
Hi Alex, Thanks a lot for your comments, much appreciated!
Comment by: Peter Thornton (4-4-2013 13:04:01) deeplink to this comment
Hi Alex, #1. In theory this modification shouldn't be necessary, the new line would result in moTree.NodeClick called twice. #2. Good point! I don't think the ScrollToView line should be necessary though, however NodeClick will only need to be called for the specific scenario so we'll look into the best way to handle it. #3. Off the top of my head the only time the active node would be hidden is if changing the vertical scroll bar moves it out of sight. Ah, in v021 the active node wasn't always repositioned correctly after Move/Copy, but hopefully all working as expected in v022. If I'm missing something re #1 do contact me privately (email in the headers) and I'll be pleased to look into it.
Comment by: Jeff Maultby (17-4-2013 12:41:52) deeplink to this comment
Perhaps enhance to allow each node and root node to have several columns where each column may also be a checkbox type column. Then we can use your control to replace the (MSComCtl.ocx) ListView control on 64bit Office VBA.
Comment by: Jan Karel Pieterse (17-4-2013 13:43:18) deeplink to this comment
Hi Jeff, Thanks for your comment! I guess even better would be to have someone step up and create a listview just like we did with the treeview. I think combining the two into one might make it too complex.
Comment by: Billy Hamilton (20-4-2013 10:24:29) deeplink to this comment
I love your control, but can't seem to find consistent code on how to set the ActiveNode on the way INTO the form. I want to set a default value. I see code that is commented out in the Demo:
Comment by: Jan Karel Pieterse (20-4-2013 21:13:06) deeplink to this comment
Hi Billy, Can you perhaps send the file to me? Note that you will only actually see the active node when the treeview has the focus.
Comment by: Peter Thornton (21-4-2013 21:10:11) deeplink to this comment
Hi Billy, To set the Activenode do literaly simply that. Eg referring to the demo form, towards the end of InitializeDemo1(), after creating the treeview with Refresh -
Comment by: Thomas Koester (25-4-2013 13:01:37) deeplink to this comment
Thanks a lot for making this available. That's what I need to make my tool work under 64 bit. I need to incorporate that in Access. Do I assume correctly that the Acess version is six builds behind? Another question: The HostProject constant is gone in build 23. Have you dropped the approach for a universal control (Excel and Access)?
Comment by: Jan Karel Pieterse (25-4-2013 14:12:55) deeplink to this comment
Hi Thomas, We're still making lots of changes to make the treeview work seemlessly in Access. Especially optimising the tool for performance proved necessary. The Access version (017)available for dowload today is significantly slower in loading the same # of nodes than it is in Excel. The version we're working on is equally fast, but slightly more complicated to implement (it is housed on a userform). The method displayed in the current Access download is being changed significantly. One of the things we're trying to keep is a single version for the different applications.
Comment by: Thomas Koester (25-4-2013 16:12:46) deeplink to this comment
Hi Jan, thank you for the quick answer. Right now I tweak the Excel build 23 to make it work in my Access app. Should I better stop and wait for the updated Access release or will the interface be more or less the same than in Excel(23)?
Comment by: Jan Karel Pieterse (25-4-2013 16:14:39) deeplink to this comment
Yes, it is better to stop. I'll ask Ben to send you a copy.
Comment by: A Programmer (29-4-2013 07:27:43) deeplink to this comment
Sorry bad English. In Access DEMO file your treeView can not be scrolled by mouse or by keys UP and DOWN. Clicking mouse on a scroll bar does not affect at all. When press DOWN get an error message: "Run-Time Error 438 Object does not support this property or a method." module: clsTreeView method: Public Sub ScrollToView(Optional cNode As clsNode, Optional Top1Bottom2 As Long) line:
Comment by: Mike (29-4-2013 09:44:14) deeplink to this comment
Hi, the treeview works great but the scrollbars doesn't work :-) How can i fix this problem? Thank you very much. Mike
Comment by: Jan Karel Pieterse (29-4-2013 14:12:43) deeplink to this comment
@A Programmer: We're working on an update whish should work a lot better in Access. Stay tuned! @Mike: What do you mean, they don't scroll using the mouse wheel? or do they fail when you drag them? Getting the mouse wheel to work is possible, though cumbersome, which is why we did not include it (yet?).
Comment by: Steve (29-4-2013 22:23:30) deeplink to this comment
Hi, I am eagerly awaiting your release of the Access version of the Treeview "control". Am desperate to overcome limitations of Office 64-bit with Windows Common Controls. Is it best to monitor this website, or can you notify by email? Do you have any plans for a Listview Common Control replacement?
Comment by: Jan Karel Pieterse (1-5-2013 13:40:30) deeplink to this comment
Hi Steve, I'd probably forget to do the notifications, so best is to watch this spot indeed. We're not planning to do a listview, no. At least not yet :-) But the techniques we used here may very well be used in a similar fashion to create a listview.
Comment by: mGRIFO (19-5-2013 03:56:20) deeplink to this comment
First of all, sorry for my worst english I' d like to now if you are think creating a treeview in a listbox
Comment by: Jan Karel Pieterse (20-5-2013 12:19:11) deeplink to this comment
Hi mGRIFO, Can you perhaps try to explain why you would want that?
Comment by: Roy Lasris (22-5-2013 14:26:06) deeplink to this comment
I tried to assign a double click action, but was not successful. I would be using this for navigating down folder trees and a double click would result (hopefully) in a redraw of the screen with a new top level. Possible? (There is a board dedicated to 'slamming' Microsoft from not supplying on its own a replacement for TreeView (comctrls). Is it okay to post a link to your solution there? I didn't see it there last time I looked.)
Comment by: Jan Karel Pieterse (22-5-2013 17:07:09) deeplink to this comment
Hi Roy, I guess what you need should be possible indeed. We have currently used the dblclick event in the clsNode class to enable editing of a node. You would have to change that logic there. Then you can use logic similar to the event "mctlControl_Click" in that same class to create an event that responds to a doubleclick. Could you please share the link to that board you refer to?
Comment by: Roy Lasris (22-5-2013 17:24:09) deeplink to this comment
Could you please share the link to that board you refer to? Here is one of the links, but there are others. Will you post, or do you want me to? http://qa.social.technet.microsoft.com/forums/en-US/officesetupdeployprevious/thread/f3a71d69-580b-4cdb-ae0b-d8189e6cfd40
Comment by: Jan Karel Pieterse (22-5-2013 17:45:17) deeplink to this comment
Hi Roy, Thanks for the link. If you could please go ahead and post a message there that would be great!
Comment by: Roy Lasris (24-5-2013 11:45:46) deeplink to this comment
Great VBA project! Thanks. Still a little hung up on creating a 'personalized' DblClick event on the node. I have set EnableLabelEdit = False. When I double click a node, the program goes to the class routine for double-click. I cannot find a way to program anything for the node at the module (as opposed to class) level. I can probably add the desired 'dblclick' action (if EnableLabelEdit is false) at the class level, but (1) I really shouldn't be programming at the class level because (2) the double click action in one user form is not going to be the same action from one user form to the next. Is there a way to trigger a node DblClick event (and respond to module level coding) at the module/form level?
Comment by: Roy Lasris (24-5-2013 12:42:56) deeplink to this comment
I found a solution. I edited the class module so that if EnableEditLabel is false, a specific routine within the userform is called. That keeps editing in the class to a minimum.
Comment by: Jan Karel Pieterse (24-5-2013 12:55:38) deeplink to this comment
Hi Roy, To add a doubleclick event to the project you can do the following: 1. Edit mctlControl_DblClick in clsNode. Add a line like this one:
Comment by: Claude Berman (27-5-2013 05:17:23) deeplink to this comment
Thanks for posting this, I'll give it a try. In return, if you'd like it, I'll share code I developed that allows one to overcome the Access limitation that attachments cannot be appended (moved) from one table to another. It does it by looping through the recordset, then looping through the attachment field in the "donating" table and if it finds and attachment, it saves to disk, then loads from disk into the other table. I needed it to allow synchronization of inspection records which are collected off-line on slate or tablet and then moved to the main database during sync.
Comment by: Roy Lasris (27-5-2013 20:32:24) deeplink to this comment
Jan, I added the below line in the declaration section at the top of clsTreeview (and followed your other instructions). Event DoubleClick(cNode As clsNode) The new doubleclick operation worked great in Word2003. In Word2010 and Word2013, I got a critical error that did not go away until I added 'Public' in front of Event: Public Event DoubleClick(cNode As clsNode) Of course, Event is by default 'public' (or so say the Help systems), but I thought I would report the 'failure' anyway. (I don't know what happens in Word2007. That computer is elsewhere.) Everything is up and running PERFECTLY. Thanks for your guidance, and for a wonderful program.
Comment by: Jan Karel Pieterse (27-5-2013 21:00:10) deeplink to this comment
Hi Roy, Thanks for letting me know (and you're welcome!)
Comment by: Jan Karel Pieterse (28-5-2013 15:49:59) deeplink to this comment
Hi Roy, Regarding Roy's post -Roy Lasris (5/27/2013 8:32:24 PM)- : Roy discovered that the problem is not whether or not the Event line is made Public, but rather that all lines declaring Events MUST come AFTER all lines declaring variables WithEvent.
Comment by: Greg Naxet (30-5-2013 23:34:14) deeplink to this comment
Hi, I'm attempting to utilized (my head is still spinning ;-)) your custom treeview control in a project that I need to use in Word 64 byte. I'm getting close thanks so much. One question for now: I my old code I could programatically select a treeview item: Me.TreeView1.Nodes(4).Selected = True I can't figure out how that is done using your control. Can it be? If so, can you tell me how? Thanks and the head spinning remark was meant to be complimentary!!
Comment by: Jan Karel Pieterse (31-5-2013 12:09:50) deeplink to this comment
Hi Greg, You can set the Checked property of a node. The Excel download of the treeview contains a lot more documentation than the Word example. You may want to download that one.
Comment by: Peter Thornton (2-6-2013 13:29:44) deeplink to this comment
Hi Greg, Just to add to what Jan Karel suggested, the equivalent of
Comment by: Jan Karel Pieterse (5-6-2013 08:01:37) deeplink to this comment
Hi Billy, Thanks, problem solved.
Comment by: Steve (13-6-2013 18:41:35) deeplink to this comment
Hi I've been monitoring your site for some time now, with hopes of an Access Treeview release. Is this still being developed? Thanks, Steve
Comment by: Jan Karel Pieterse (14-6-2013 15:01:35) deeplink to this comment
Hi Steve, Third download link above perhaps?
Comment by: Andy Litchfield (16-6-2013 14:37:58) deeplink to this comment
Perfect! I'll be a treeview expert by the end of the day... Thanks very much
Comment by: Jeff Silcock (20-6-2013 15:18:13) deeplink to this comment
I have a project in Access 2010 which uses treeview to display a list of nested reports. This, of course, no longer displays in Access 2013, so am very interested in your solution. However tried importing the various modules and the subTreeView form into a new DB, set references to Forms 2.0 Object Library, but am getting compile error on
Comment by: Jan Karel Pieterse (20-6-2013 16:34:51) deeplink to this comment
Hi Jeff, Did you press the "How Do I?" button on the form of the demo mdb? It describes how to use the treeview in Access better than this page does. You can insert the userform by running some code that is in the ModStartup module
Comment by: Jeff Silcock (20-6-2013 17:02:12) deeplink to this comment
Ah! yes, sorry I missed that bit of info. The form is now in, so I will continue with my test. Thanks I will let you know how it goes.
Comment by: Rasmus (26-6-2013 18:44:20) deeplink to this comment
Hi and thanks for creating this great 'add-in' :), I was worrying about the forward compatibility with the control i newer versions of Excel so this is spot on. I would like to use your code to build af treeview based on a hierarchy, but I'm having trouble converting your original code to a 'variable' based one. I have X columns (hierarchy level) with lots of duplicate values in the Y rows (persons). I sort the table (if there are three columns, I sort all three), so I can loop through each column and add nodes accordingly, without adding duplicates and getting runtime errors. I can add the root node and the first set of branches, but I fail to add level 2 and below. I need to refer to the former created branches but I cannot create n variables to store the keys - so I need something else. I tried referring to the cell value to the left, but that doesn't work. Would you help me out with a tip to get me started adapting the code to my needs? The data is arranged, so a person has hierarchy lvl1 in the first column, lvl2 in the second, e.g. "Sales" and "Sales East", and so on. Many thanks in advance :)
Comment by: Jan Karel Pieterse (27-6-2013 07:33:24) deeplink to this comment
Hi Rasmus, Check out the NodeAdd method of the Tree class, it allows you to specify the key of the parentnode of a node you are adding.
Comment by: Rasmus (27-6-2013 16:24:58) deeplink to this comment
Just a comment with a big THANK YOU to Jan for helping me adjust the excellent Treeview code to my exact needs. It works perfectly now and I can tailor it do much more than what was possible with the MS treeview control. Class act providing such an extensive resource free of charge to everyone, highly appreciated. Respect and gratitude.
Comment by: Jeff Silcock (27-6-2013 17:43:20) deeplink to this comment
Just to say this is a brilliant replacement for treeview. I have it working with 4 node levels, and drawing all the structure from one table. This table has parent and child fields to enable you to select each level as you populate the tree. One question though is how to initially display the tree in the collapsed state. I will be glad of help on this. Regards Jeff
Comment by: Jan Karel Pieterse (28-6-2013 11:50:39) deeplink to this comment
@Rasmus: Thanks! @Jeff: You can use the ExpandToLevel method of the clsTreeview class instance to force the tree to expand to a certain level. Also you can change the expanded property of an individual node:
Comment by: Jeff Silcock (28-6-2013 17:15:52) deeplink to this comment
Yes that's done it. Added
Comment by: José (8-7-2013 14:31:58) deeplink to this comment
Dear Sir I am a curious interested in Access. I installed your TreeView demo. It aborts with a message : Type incompatibility. Note that I use a French version of Office. Regards José
Comment by: Jan Karel Pieterse (8-7-2013 17:30:58) deeplink to this comment
Hi José, I'll ask my colleague who has more knowledge of Access to contact you.
Comment by: Rasmus (10-7-2013 10:08:37) deeplink to this comment
Hi Jan, Is it possible to access the treeview on a multipage control in a userform? I have tried the standard syntax: Me.multiPage1.Page(1).frTreeview - but nothing happens. Regards Rasmus
Comment by: Rasmus (10-7-2013 16:15:19) deeplink to this comment
Hi again Jan, Regarding my comment today on Multipage - I'm really sorry, but the error was not caused by the Multipage, but strangely a lack of using "Thisworkbook." in first in a range reference. I apologize for the inconvenience! Regards Rasmus
Comment by: John (26-7-2013 14:44:04) deeplink to this comment
Hi Jan. I am implementing your excellent TreeView in Word 2010 with some success and I wanted to thank you. I was also wondering if you have any news on drag and drop functionality or any quick tips to point me in the right direction. I can drag a node's key out of the tree (e.g. to a textbox) but I cannot see how to drop it to another node in tree. I am missing something obvious... Any help would be very much appreciated. Regards, John.
Comment by: Sherry Massey (30-7-2013 20:29:05) deeplink to this comment
Jan, I built an Access application that is driven entirely by a treeview interface. It worked well on my 64-bit workstation and died a swift death on the 32-bit tablets it is being deployed on. While drying my tears and searching for an alternative, I found your site. Your TreeView has worked perfectly, so far. I cannot thank you enough. With gratitude, Sherry
Comment by: Peter Thornton (1-8-2013 16:57:08) deeplink to this comment
Drag & Drop can certainly and effectively be implemented in the Treeview. We thought best not to include it (yet) in the beta demos, partly as it’s quite tricky and updating to work with other changes in new versions was time consuming, and partly as ideally it’s best tailored to the particular application, though we’re happy to adapt it for clients. However John did contact me off-line and I understand he has now got Drag & Drop working for his needs.
Comment by: Fernando Santos (7-8-2013 16:51:56) deeplink to this comment
I was wondering if you plan to do the same for listview control...
Comment by: Jan Karel Pieterse (8-8-2013 20:57:04) deeplink to this comment
@Fernando: No, there are no plans for a listview control (though I have made some feeble steps in that direction, but I lack the time currently). @Sherry: Thanks!
Comment by: Dieter Samel (14-8-2013 16:08:45) deeplink to this comment
Hi Jan, I need urgently a way for drag and drop in the tree. Can you show me a useful approach. Sorry, my english is not the best. But I could German well :)
Comment by: Tony Matyas (19-8-2013 16:48:23) deeplink to this comment
Hi Jan, a wonderful piece of work. Couple of Questions: a) What can I do in the class modules to show the UserForm in a modal way - the DemoNow procedure results in an error message, when I simply try to change '.Show' to -> '.Show (Modal)'. b) Is there any limitation to the number of TV nodes? Suggestion for a possibly new release: Multicolumn treeview, e.g. Tag | Key | Value, editable by default: only Value (DblClick/F2). Would be a nice XML-Editor, for instance! Thank you in advance for any helpful info TM
Comment by: Don Maskew (20-8-2013 02:13:15) deeplink to this comment
Hi, I'm using your treeview in Access 2013 32/64 bit. Great to get around the 32/64 bit problem. But...I can't get the ActivateNode to work. I want to activate a node given it's key. Is that possible, if so, can you give me an example syntax? Thanks Don
Comment by: Jan Karel Pieterse (20-8-2013 08:52:58) deeplink to this comment
Hi Don, I guess you could add this to the clsTree class:
Comment by: Jan Karel Pieterse (20-8-2013 08:53:50) deeplink to this comment
Hi Tony, The userform is already shown modal, so I guess I nmay not grasp what you are trying to do?
Comment by: Mark Scicluna (1-9-2013 11:21:41) deeplink to this comment
Hello Jan This is just what I have been looking for. I ran into a lot of problems with our current application to deploy on multiple Windows and Office versions because of the MSCOMCTL.OCX. Your code works brilliantly. I have to now integrate this code into our .ADP application. This uses Access 2002 - 2003 format. When I converted the downloaded Access Demo from 2000 to Access 2002 - 2003, the form did not load :(. I got a runtime error 2147286785 - Invalid flag error. The line of code on which this error was produced is:
Comment by: Jan Karel Pieterse (1-9-2013 20:12:22) deeplink to this comment
Hi Mark, You probably have to remove the userform altogether and use the code in the example file to re-create it using Access 2000/2002. Not sure though, I did not create the Access demo myself.
Comment by: Richard Tawn (11-9-2013 12:10:30) deeplink to this comment
Hi, the treeview is great, I have made a few minor adjusts to the behavior to suit my needs, couple of questions, could you help I am trying to pass the mouse button to the Click event so "mcTree_Click(cNode As clsNode)" would be "mcTree_Click(cNode As clsNode, byval Button as integer)" also is there method to set checkboxes ?
Comment by: Jan Karel Pieterse (11-9-2013 16:30:46) deeplink to this comment
Hi Richard, To change the signature of the Click event, find this line in the clsTreeview class:
Comment by: Justin Glasgow (11-9-2013 22:52:33) deeplink to this comment
Hello, I had a suggestion. One addition that I made was the "ControlTipText" property to clsNode. This would allow the user to add text that would appear while hovering over a node. -Justin Glasgow
Comment by: Jan Karel Pieterse (12-9-2013 10:44:04) deeplink to this comment
Hi Justin, Good point!
Comment by: Richard Tawn (12-9-2013 11:01:46) deeplink to this comment
Jan, Thank you very much for the response and your time, I am pretty much at the same point I was at yesterday, things halt in "Private Sub mctlControl_Click()" in clsNode with "Variable not defined" at moTree.NodeClick Control, Me, Button Adding Button as integer to declarations returns "0" to the event Regards, Richard
Comment by: Jan Karel Pieterse (12-9-2013 11:11:34) deeplink to this comment
Hi Richard, The point is, that the click event does not give you the mousebutton. You need the MouseUp or MouseDown event of the control (which you can add by clicking inside the click event in the clsNode class and then clicking the right-hand dropdown at the top of the code window). From there you can call an event in the clsTreeview class whilst passing the button property.
Comment by: Richard Tawn (12-9-2013 14:55:00) deeplink to this comment
Jan, got it ! declared "private btn as integer" in clsNode, added "btn = button" to mctlControl_MouseDown, finally "moTree.NodeClick Control, Me, Button" becomes "moTree.NodeClick Control, Me, Btn" in mctlControl_Click()" . voila for anyone else after the same functionality Regards Richard
Comment by: Jan Karel Pieterse (12-9-2013 16:29:25) deeplink to this comment
Hi Richard, That is one way of doing it indeed. Note though that when one is using the keyboard to navigate the tree, the click event may also fire, which might mean the value of btn might be off.
Comment by: Greg Maxey (13-9-2013 19:50:49) deeplink to this comment
Hi, I've recently encountered a problem using your treeview control. The problem results in a run time error duplicate key when the code hits the third line shown below: In your Public Function AddChild of your clsNode
Comment by: Adrian Price (15-9-2013 14:34:47) deeplink to this comment
I want to add my name to the numbers of people that would like you to produce a ListView control :) I do hope that you find the time to produce it - you must have spent hours on the TreeView control which I would like to use. Many thanks for the work you have done. Adrian
Comment by: Jan Karel Pieterse (16-9-2013 06:37:39) deeplink to this comment
Hi Greg, I'm afraid not much can be done, since we're using the add method of a collection with a key if you provide one. The add method is case insensitive unfortunately. You can try changing the code in the NodeAdd sub in clsTreeview so it does not use the key (linenumber is indicated and equals 100) when adding the new node to the collection, it will still add the key to the clsNode class instance.
Comment by: Peter Thornton (16-9-2013 16:07:01) deeplink to this comment
@ Justin Simulating a ControlTipText window is not simple, if you mean say to show a borderless/captionless form near the node or mouse. That said it can be done with APIs. A different approach to force user to select from a choice might be to show a dropdown (combobox) over the node, populated with choices. Much of the code to do that can be adapted from the “edit-box” code (the textbox that appears if you double click or press F2). It would be straightforward to add a node property to store an array of choices for the combo or pick up from elsewhere depending on selected node. @Richard Instead of working with the click event could you just simply use the mouse-up event for your purposes. As Jan Karel explained the mouse-up exposes the button directly (as does mouse-down). Typically there’s little effective difference in timing between click & mouse-up events, but generally better to use mouse-up. Reason, mouse-down “provisionally” selects the new node, but it's only fully activated if mouse is released while still over the node, IOW if user drags mouse off the node and releases mouse the previous active node is restored without exposing an unwanted click event. As Jan Karel flagged consider use of the keyboard and equivalent events. @Greg If you don’t particularly need Keys there’s no need to add them (they're optional), and best not if there’s a risk of attempting to add duplicates (unless deliberately testing). However if you want to include your Apples & APPLES as additional data you could use the node’s Tag property. Thereafter you could loop and search (case sensitive) all nodes for a given case sensitive Tag and in turn retrieve your node.
Comment by: Jan Karel Pieterse (16-9-2013 18:33:32) deeplink to this comment
@Peter: You misunderstood :-) Adding a controltiptext is straightforward, we're working on it.
Comment by: Peter Maclaurin (23-9-2013 00:54:22) deeplink to this comment
Hi, Just found your control and it looks just what I am looking for to replace the Microsoft Treeview in my Access application. I have got the code to work in Access 2013 and managed to get it to read Major and Minor Categories from tables in the database (just two levels) with checkboxes. I'm now trying to set the checkboxes by code (ie. reading in from another table which were previously selected and saved - saves the unique key of the Minor Categories) and then testing the checkboxes on exit to see if any have changed and saving the changes (if necessary). I just can't figure out how to do this. I'm sure it's simple to a lot of people but I'm brain dead on this. Great control by the way! Peter
Comment by: Jan Karel Pieterse (23-9-2013 06:37:39) deeplink to this comment
Hi Peter, Not sure if this is what you're after, but the clsNode class has a Checked property you can interrogate. Also, the clsTreeview class has a nodes collection. So:
Comment by: Peter Maclaurin (23-9-2013 10:48:40) deeplink to this comment
Hi Jan, Not sure what I'm doing wrong. I have attached the code to the click event of a button on the form and added: Dim cNode as clsNode What I get is Run-time error '438' Object doesn't support this property or method
Comment by: Jan Karel Pieterse (23-9-2013 11:25:19) deeplink to this comment
Hi Peter, Hmm, my code should have read:
Comment by: Peter Maclaurin (23-9-2013 11:33:37) deeplink to this comment
Hi Jan, Works perfectly in my test database! Now to try to incorporate it into my database. Thanks for everything. Peter
Comment by: Richard (30-9-2013 12:18:08) deeplink to this comment
Might be a big ask but... is there any possibility to add functionality to clsNode to enable/dissable a check box for the particular set node ? Regards, Richard.
Comment by: Jan Karel Pieterse (30-9-2013 13:47:14) deeplink to this comment
Hi Richard, You're right, it is quite a bit of work, as it involves: - Adding the property - changing the checkbox color to denote a disabled checkbox (if you're not using icons) - Adding pictures for a disabled checkbox so it is visible to a user that checkbox is unavailable (if you're using icons) and making sure the correct picture is shown. - Adding a reference to that new picture - Checking for the property in the click event of the checkbox so we can cancel the click where needed
Comment by: Richard (30-9-2013 14:42:01) deeplink to this comment
Actually all im after is to set a property during populate that determines before adding a node weather a checkbox is visible or not for said node, as I am not using images im not bothered about that side of things, what im saying is i want the checkbox completely absent for certain nodes and visible for others, for example setting or forgetting msngChkBoxPad before setting a node ? not sure if im in the correct place or not ? Regards, Richard.
Comment by: Richard (30-9-2013 17:45:23) deeplink to this comment
Done it ! I added a property to clsnode called exp changed the node poplulate if statement to if exp etc I removed the checkbox function from root as i did not require it Cheers for your work sir ! Richard
Comment by: Peter Maclaurin (1-10-2013 13:30:18) deeplink to this comment
Hi Jan, I have successfully incorporated the treeview into my database but I have one form that used to have both a treeview and a listview (both with checkboxes). I have replaced the treeview with your one but the listview is now the problem. I could also replace it with your treeview but I'm not sure if it is possible to have two on the same form? Regards Peter
Comment by: Jan Karel Pieterse (1-10-2013 15:19:48) deeplink to this comment
Hi Peter, Yes, having two should be possible. Probably best to put them both on the userform, but I have not enough Access forms knowledge to know this. I'll ask Ben, who enabled the treeview to work on Access.
Comment by: Ben (1-10-2013 15:24:40) deeplink to this comment
Peter - Yes, it is possible - you would simply put down two subform controls, and set each subform's SoureObject = "subTreeView". Though this would point to single form named "subTreeView", it will be instantiated as a 2nd object and you can manage each subform and therefore the treeview independently of each other.
Comment by: Peter Maclaurin (2-10-2013 11:24:05) deeplink to this comment
Hi Jan and Ben, Took me a little while to figure out how to initialize both Treeviews on the same form but I can report it does work. Many thanks for that.
Comment by: Peter Maclaurin (7-10-2013 12:50:00) deeplink to this comment
Hi Ben, One further question. The tree is on one form and I need to be able to read through the nodes, getting their description if their checkbox is checked, from another form, which then adds these node description into a listbox on it. I do check to see if the form with the tree on it is open prior to trying to read the tree. This is the Listview control that I have replaced with your Treeview control. The old code used to read: If IsLoaded("frmSales") Then For i = 1 To Forms!frmsales!ListView0.ListItems.count Me.lstFields.AddItem Forms!frmsales!ListView0.ListItems(i).Text _ & ";" & Forms!frmsales!ListView0.ListItems.Item(i).SubItems(1) Next i Hope that makes sense. Regards Peter
Comment by: Jan Karel Pieterse (7-10-2013 14:56:10) deeplink to this comment
Hi Peter, You should be traversing the Nodes collection of the treeview on the other form. They have a Checked property.
Comment by: Peter Maclaurin (8-10-2013 10:35:57) deeplink to this comment
Hi Jan, Yes, I knew that but was having trouble working out how to get to the treeview from another form. I eventually managed to get a few brain cells to work and figured it out. In case anyone else has this problem the code looks like: If IsLoaded("frmSales") Then For Each cNode In Forms!frmSales.mcTreeSalesFields.Nodes If cNode.Checked Then Where IsLoaded is a function that checks if frmSales is loaded. Many thanks for all your help. Peter
Comment by: Jan Karel Pieterse (8-10-2013 11:47:50) deeplink to this comment
Hi Peter, Thanks. I assume this means mcTreeSalesFields should be declared as a public variable?
Comment by: Peter Maclaurin (9-10-2013 03:15:23) deeplink to this comment
Hi Jan, Yes. If use the coding in the example you provided the TreeView is declared as a Public Variable in the declarations section. I just modified the name as I actually have two treeviews on that form. Regards Peter
Comment by: Ben (10-10-2013 13:43:43) deeplink to this comment
Peter, Just as a FYI - If you want to "early bind" your treeview variables, you could do so like this: <code> Dim frm As Form_NameOfMyForm If IsLoaded("NameOfMyForm") Then Set frm = Forms("NameOfMyForm") For Each cNode In frm.mcTreeSalesFields.Nodes ... Next End If </code> The advantage is that you have assurance from VBA compiler that the form's property exists and can be validated. This coding pattern is effective in a larger VB project where you might have many forms and treeviews to keep track of and don't want to get runtime errors due to form being not loaded or change to the forms. You also can 'early-bind' a subform in like manner: <code> Dim sfrm As Form_NameOfMySubForm Set sfrm = frm.MySubFormControlName.Form </code> You can then reference sfrm.mcTreeview and this also enable you to disambiguate two things that would from same subform loaded twice such as sfrm1.mcTreeview and sfrm2.mcTreeview. You then don't have to change name on account of loading it more than once. I hope that helps.
Comment by: Peter Maclaurin (11-10-2013 11:11:54) deeplink to this comment
Thank you Ben. I agree that it is probably better to early bind. Regards Peter
Comment by: Claude (20-10-2013 13:48:59) deeplink to this comment
Hi, and thanks for publishing this, it's worked beautifully for managing a large space inventory (customers > buildings > floors > rooms). I didn't notice the recent update and am experiencing some corruption issues that may be caused by an old common controls treeview I haven't gotten around to fixing. It may also be related to the sheer size of the application, and it may be hitting the limits, just haven't figured it out yet. I do have one silly question, or at least I feel that way about my inability to figure it out. I'd like to load the tree (4 levels) but initially just have the upper three nodes expanded. I've searched on code for the word "expand", but can't figure out the syntax to collapse the a node level for initial presentation. Is there a way to do this?
Comment by: Jan Karel Pieterse (21-10-2013 09:33:23) deeplink to this comment
Hi Clause, It is simple really. Nodes are expanded by default, so just for the nodes that you want not to be expanded:
Comment by: Claude Berman (21-10-2013 13:18:11) deeplink to this comment
Thanks Jan, I tried that: cNode4.Expanded = False in various places within the procedure that builds the tree. I tried it: 1. Before the .refresh at the end 2. After the .refresh at the end 3. Immediately after the set cnode4= statement And just can't get it to work in the Access code. Is there somewhere else I can try?
Comment by: Peter Thornton (21-10-2013 15:20:15) deeplink to this comment
Hi Claude, Curiosity, could you give an idea of what you mean by "the sheer size of the application". How many nodes and how many controls are being created. If you have the "DebugMode = 1" compile constant in place (in project properties) the stats will appear on the form's caption.
Comment by: Peter Thornotn (22-10-2013 12:29:21) deeplink to this comment
Hi Claude, About your expansion problem, if you want to show only the first three levels you need to set the expansion property of all nodes in the 3rd level, like when you click an expander on the 3rd level you show or hide its parents in the 4th level. So instead of cNode4 try
Comment by: Claude Berman (31-10-2013 15:07:03) deeplink to this comment
Thanks Peter, I'm having all kinds of fun with this thing, such as setting the colors of the last (detail) nodes according to state (green=inspection passed, etc.). Now, I'm wondering if there is a way to control the expansion level from outside the tree without actually rebuilding it. I'd like to have an option group with three or four toggle buttons meaning (account, building, floor, all) corresponding to the four nodes in the tree (disregarding the root). Thus, if the user clicks the toggle "building", the tree would expand all nodes higher and collapse all nodes lower. I could easily do this via rebuilding the tree (now that you showed the way), but in working with large data sets, it would help if there is a more efficient way. Thanks for all your free help, you really should sell this thing, it would be worth a reasonable sum.
Comment by: Jan Karel Pieterse (31-10-2013 16:34:16) deeplink to this comment
Hi Claude, Thanks! Not sure I understand what you're after though. Regarding selling the tool: you can always make a donation :-)
Comment by: Peter Thornton (31-10-2013 17:31:44) deeplink to this comment
Hi Claude, If I follow you want to collapse all to level 0 (if you have multiple root nodes) or level 1, then you want all child and grandchild nodes of say "building" expanded. There are several approaches but try something like this, in your form code -
Comment by: Claude Berman (31-10-2013 21:36:21) deeplink to this comment
Thanks Peter, and Jan I sent you guys a pitcher or two of Ale since you'll probably need it dealing with all my questions. Ok, how do I pass a reference to a particular node to this function? I have a tree (mcTree) sitting on the form. Sorry, I really need an Ale too.
Comment by: Claude Berman (31-10-2013 21:58:39) deeplink to this comment
Peter, I forgot to answer your question concerning size vs. performance. I have a test database with 8000 rooms (showing in the tree) and performance really isn't bad. This corresponds to a very large hospital or a mid-size university. But, I'm not sure how it run on a normal user's machine since I run a very fast workstation with solid state drives in a stripped array (mozy.com backs it up nightly since this is dangerous). It slowed down a bit when I added some formatting via conditions to change the final node color, but I think it's more the impact of the if/then/else statements to determine the formatting. To further confuse the issue, each room can have multiple inspections and I created a "demo" tool for distributors that generates any number of randomly scored inspection records. These are then used prior to processing the tree to determine which space inventory to show. In other words, select a particular inspector, then I've got to run the tree with a different set of queries. I'm also doing similar stuff to generate a chart on the same form. It takes a couple of seconds on my machine usually, worst case 5-10 seconds to rebuild the tree and paint the chart. I really don't think I'd need or want a ground-up rebuild since the real work and time is mostly in the pre-processing I have to do. And, blush, when I started 20 years ago my profession was mechanical engineering, so I didn't completely normalize the data, this propagated over the years as this app was developed, so I have to waste some CPU cycles normalizing the data on the fly.
Comment by: Peter Thornton (1-11-2013 15:33:50) deeplink to this comment
Claude, Re "how do I pass a reference to a particular node" This is the type of thing you really need to know how to do:-) There are various ways, eg if you are using keys you could do this
Comment by: Claude Berman (8-11-2013 13:34:29) deeplink to this comment
Hi and thanks Peter. I'm still stuck on this. In your second example, which is what I need since I'm trying to collapse the tree to a user-selected level (i.e. all node levels below a certain one - pick building from the option group and all buildings collapse so the only account and building nodes show: My question is what is the syntax for reference the tree from outside the tree? I've tried everything I could think of, such as mcTree.nodes.cnode1, etc.? I'd composed a note the other day in which I asked if you would do this as an example for a fee. Jan has the email sent with download instructions and install instructions if you want to take a whack at it. But, perhaps I got distracted and failed to hit the submit button. Otherwise, assume a reasonable price on an enhanced (pro) version, and you've got at least one customer. The tree itself seems to handle a large number of nodes quickly, I've slowed it down a bit with conditional expressions to determine formatting, and some expressions to calculate values (displayed in the tree itself), but even then, it's pretty quick. I have a partner application running on a lame Atom processor device used for inspections (an Acer W510 tablet). It synchronizes data and displayed the same inventory in your tree for selection of the room to inspect. It's the slowest device I have and, without any internal tree enhancements, such as expressions running during the tree-build loops, it displays the entire data set it a second or two.
Comment by: Dieter Samel (13-11-2013 18:16:48) deeplink to this comment
Hi Jan, My problem is the same as: Comment by Mark Scicluna (01/09/2013 11:21:41 AM) Unfortunately, your recommendation does not: You probably have to remove the userform altogether and use the code in the example file to re-create it using Access 2000/2002. Could you send Mark my email address or post the solution?
Comment by: Jan Karel Pieterse (14-11-2013 06:34:37) deeplink to this comment
Hi Dieter, I suggest you to open the Access demo file and click the "How do I...?" button on its main form. It has a lot of information on how to implement the treeview.
Comment by: Peter Thornton (14-11-2013 13:51:01) deeplink to this comment
@Claude I understand Jan Karel has contacted you and hopefully all is working for you now. I'm surprised you can load and display 8000 nodes so quickly, particularly if lines are shown. But if so the current version should be fine for your needs and no need to 'pay' for our faster version! (still testing it) @Dieter & Mark We haven't tested in Access 2002 but I couldn't get it to work in 2000 even in a new project, at least not with the current internal arrangement. It works fine in 2003 but it seems only if built in 2003 (which is what the demo is compiled in). IOW, you can distribute from compiled in 2003 to 2007+ but not (necessarily) from 2007+ to 2003. Not sure why but it seems to vary with different systems. There might be a simple trick for 2000/2 so if you or anyone figures it please let us know! It is written up top only tested in Access 2003 and later. (Excel & Word all since 2000, Mac only tested in 2011)
Comment by: Dieter Samel (14-11-2013 15:04:05) deeplink to this comment
my approach : System : Win 7 with off 2003 SP3 An empty 2003 mdb created. All objects without the forms subTreeview , ufTreeView imported. Then the following references to FM20.DLL and it created the new ufTreeView . Then I also have the subTreeView imported. The same error ! Analogous to : Set mfrTreeControl = mUF.Controls.Add ( " Forms.Frame.1 ", " frTreeControl ", True ) I have manually tried to insert a frame in the ufTreeView . I got an error message: Microsoft Visual Basic ! not implemented The VBA code throws the following error: -2147286785 Invalid option This works in all other development environments ! Appears frequently in conjunction with the FM20.dll a fault : Not enough memory ! I put on the line during debugging Set mfrTreeControl = mUF.Controls.Add ( " Forms.Frame.1 ", " frTreeControl ", True ) a different value for the number 1 a , the error changes to : Invalid class string It seems as if in this particular installation , the frame is not supported! Translation with Google from the German
Comment by: Dieter Samel (15-11-2013 10:05:02) deeplink to this comment
Hello again, A new installation with Win XP and Off2003 was created by me. Now I'm sure the UserForm does not support FRAMES in MDB format MS Access 2002-2003. In a 2002-2003-MDB I can not create a frame, and when I put it in a 2000 MDB, and then convert to the 2002-2003 MDB, leads the appeal or change the Frams errors! As long as I'm on the same system using the same MDB than 2000 everything works. Unfortunately, I have but the 2003 format change to create an MDE!
Comment by: Peter Thornton (15-11-2013 11:28:08) deeplink to this comment
Hi Dieter, Does your system with Access 2003 also include multiple and later versions of Office. If so there might be some sort of conflict.
Comment by: Dieter Samel (15-11-2013 13:16:51) deeplink to this comment
Hi Peter, No my systems are pure virtual Fittings are installed with exactly 1 Office. With friends adding frames is not even in the UserForm with a 2003 mdb with Off2003! Incidentally created on this system 2003 MDE (yes, it is created) does not work there. But they can be used on any newer MS-Access without problems! I have my clients now told him to install the runtime Acc2010! Thus I will not provide support for this application in Off2003 first on 8 April ;o)
Comment by: TheSmileyCoder (18-11-2013 10:30:20) deeplink to this comment
Very impressive piece of work. Would it be possible to make the node click event handler include arguments on which mousebutton was clicked? I would also hope to see a guide on how to add my own icons to the treeview. Great job! Thank you all for sharing.
Comment by: Jan Karel Pieterse (18-11-2013 13:20:18) deeplink to this comment
Hi TheSmileyCoder, The default click event does not pass along mouse button states. Instead you should use either the MouseUp or the MouseDown event. As it is now, in the MouseUp event we call the click event, which as I said does not pass mousebutton information. However, you can simply change that implementation by adding your own mouseUp event to the clsTree class and call that. To find out how to add events, check out the clsTree class declaration section, there are 4 events set up there. Each one has a corresponding RaiseEvent codeline somewhere in the class. This raiseevent line should be within a public sub in the class which in turn is called from the appropriate control event in clsNode. Clear as mud? Adding icons is very simple: add image controls to the (hidden) frame with icons, name them uniquely and use that same name when creating the nodes.
Comment by: TheSmileyCoder (18-11-2013 14:05:14) deeplink to this comment
Hi Jan Thank you for your swift reply. I must admit, at present I have only been playing with your treeview, to see if it is a potential solution for when/if my organisation switches to Access 2013 or office 64bit. I run alot of applications that use the treeview as a main component. I shall try to play with both the icons and the events. Do you have any idea on the long term availability of the MS form? My fear would be spending lots of time implementing your treeview as part of my app, only to see MS forms be removed in OfficeNext.
Comment by: Jan Karel Pieterse (18-11-2013 14:36:06) deeplink to this comment
Hi TheSmileyCoder, I don't think anytime soon. msforms getting removed will only happen if VBA is omitted from Office. That is highly unlikely to happen soon.
Comment by: Sujoy (19-11-2013 09:27:21) deeplink to this comment
Hi jan, It seems that the 'control' property of node has not been implemented properly. I cannot access any properties of 'node.control' like "MyNode.control.backcolor" etc. Kindly suggest a way to do it. I am working upon a wonderful VBA Code Review tool. I may share it in this site after completing. Regards, Sujoy (+91 8010189196)
Comment by: Jan Karel Pieterse (19-11-2013 11:09:59) deeplink to this comment
Hi Sujoy, Timing is important. Before the treeview has been refreshed, none of the controls exist so the Control's properties can only be accessed After the Refresh command was done. To set properties of a control when you are building the nodes, you need a different syntax. Instead of:
Comment by: Duke Carey (26-11-2013 16:13:36) deeplink to this comment
Jan/Peter - After years & years of using the MS common controls tree view, I slapped together a quick Excel file this AM using yours and have to say I'm very impressed. Performance while loading and then traversing a nearly 1,000 node tree was pretty darned good. I'll be using it a lot more in the future. Kudos to you both for the quality of this utility and for freely sharing the work product of what had to be many, many development hours.
Comment by: Jan Karel Pieterse (26-11-2013 17:30:42) deeplink to this comment
Hi Duke, Thanks!
Comment by: The Only Sheet (30-11-2013 18:16:07) deeplink to this comment
This control is awesome to replace the annoying version from Microsoft! There are two functionality that the original control has, that I have not seen (please correct me if I am wrong - and please let me know where to look to to find this!) A) Is it possible to have the Mouse Wheel 'scroll' the scroll bar? Is there an option to turn this ON? B) The original control allows the used to type part of an word to 'jump' to the first occurrence (if any) of this word. This is VERY useful for long lists. Is this doable? Is it available already? MANY THANKS for your work on this!!
Comment by: Jan Karel Pieterse (1-12-2013 19:41:48) deeplink to this comment
Hi The Only Street, Very useful suggestions. Unfortunately, mousewheel scroll is quite hard to add. The other options shouldn't be too difficult.
Comment by: Seth (2-12-2013 07:42:23) deeplink to this comment
I tried this with Windows 8.1 and Access 2013 64-bit and it gets run-time errors. I know you haven't certified it for this, but I am wondering if you are working on getting it running in this environment. The first runtime error occurs in Form_subTreeView in the Form Load. I have a comment below on the line that gets the run-time error (there is no TabIndex property for ctl). If Not moSubControl Is Nothing Then moSubControl.OnEnter = "[Event Procedure]" moSubControl.OnExit = "[Event Procedure]" Set mcolTabControls = New VBA.Collection On Error Resume Next For Each ctl In moSubControl.Parent.Section(moSubControl.Section).Controls lngIndex = ctl.TabIndex ' RUN-TIME ERROR OCCURS HERE If Err.Number = 0 Then If ctl.TabIndex > mlMaxTabIndex Then mlMaxTabIndex = ctl.TabIndex End If mcolTabControls.Add ctl.Name, CStr(ctl.TabIndex) Else Err.Clear End If Next
Comment by: Jan Karel Pieterse (2-12-2013 16:26:43) deeplink to this comment
Hi Seth, I'll ping Ben Clothier (he has done the Access integration work on the control), see if he knows what is going on (I don't have a 64 bit 2013 installation).
Comment by: Ben Clothier (4-12-2013 15:47:51) deeplink to this comment
Thanks for the comment, Seth. There is something off here because if you look at the code, it's in a "On Error Resume Next" -- the fact you are getting a runtime error inside a Resume Next block suggests to me that there is something off with the VBE setting -- check your setting in the VBA editor: Tools -> Options -> General tab Ensure you've selected "Break on Unhandled Errors" That should then let the code run through the Resume Next -- the error you saw is expected because not all controls have the TabIndex property which is why we're using the Resume Next & Err.Number = 0 to determine if we're going to add that control to the collection or not. HTH.
Comment by: Paul Sondervan (17-12-2013 17:17:41) deeplink to this comment
Is it possible to change add more space between the icon an the text?
Comment by: Jan Karel Pieterse (17-12-2013 18:13:58) deeplink to this comment
Hi Paul, Sure, in clsTreeView, at the top there is a number of constants which control the looks, this one seems to be what you need: Const mcIconPad As Single = 14.25
Comment by: Paul Sondervan (18-12-2013 09:08:03) deeplink to this comment
Hi Jan Karel, I've adjusted the mcIconPad constant, even to a large value, but I didn't see any change. After I changed FullWidth to true, it worked. I would like to have it work when FullWidth = False.
Comment by: Paul Sondervan (18-12-2013 09:47:36) deeplink to this comment
Hi Jan Karel, I have got a suggestion. In clsTreeView the SpecialEffect is set to sunken.
Comment by: Jan Karel Pieterse (18-12-2013 11:50:21) deeplink to this comment
Hi Paul, Thanks for the comments and compliments, much appreciated. Of course you can implement the suggested improvements yourself :-)
Comment by: Peter Thornton (18-12-2013 15:37:33) deeplink to this comment
Hi Paul, Re Icon spacing: With the FullWidth option we're obliged to use a separate control for the icon, but it gives the advantage of being able to place the icon wherever we want. So for padding simply change the constant as Jan Karel suggested. But with larger treeviews with icons it's best not to use the fullwidth option, then the icon can 'share' the same control as the text, but we can't pad it. A workaround might be to add extra spaces before the text. However, if treeview size and overheads is not the issue but you simply don't want fullwidth, continue to use the fullwidth option but comment anything that applies that full width value, eg
Comment by: David Cushing (20-12-2013 08:14:29) deeplink to this comment
I have been in the IT industry for almost 40 years and have designed many databases using Access and several mainframe DBMs. Since my semi-retirement this year, I have not been doing much coding and I found I missed it. All of that is a verbose explanation of my reason for developing a genealogy database for my family :) I was using Microsoft's Treeview control and I got it working reasonably well but while I was browsing for help with making it work really well I tripped across the discussion of the Treeview control at eileenslounge and that led me to your Treeview replacement. Downloaded it and spent a couple of hours earlier tonight getting it to work in my database. Probably the easiest re-write I have ever done; your code is clean, clear and intuitively elegant. Great Job!!!! My questions are: 1. Can I trigger a refresh of your embedded subform from another subform in the same parent? It was easy to use the Click event in your subform to modify the data in my subform but now I want to click a button in my subform and force an event in your so I can rebuild the tree from a new starting point. I feel like this should be relatively easy but I have been up too long and it is escaping me. 2. Can I get the control to work in a report? Thanks for developing this control and for any help you may be able to offer. Dave
Comment by: Peter Thornton (21-12-2013 12:29:36) deeplink to this comment
Hi David, Thanks for our comments. In answer to your questions - 1. All you need in your second form is a reference to the treeview, then you can control it exactly the same as in the main form. One way would be to start with this property in the main form
Comment by: Helen Feddema (28-12-2013 00:22:12) deeplink to this comment
It took a lot of work to convert my old Treeview code to work with yours, but I got it working in 64-bit Access, so thank you for making this possible! Since I have a number of forms with similar Treeview controls in my database, I had planned to make a public Sub called from the forms' Load event which would build the Treeview, with arguments for the queries to use (the only thing that differs between forms). But when I placed the Public WithEvents mcTree As clsTreeview in a standard module, it turned red. I tried putting it into the clsTreeview class module, and it didn't turn red, but the code didn't run. Does this feature only work in a form module?
Comment by: Jan Karel Pieterse (28-12-2013 09:59:53) deeplink to this comment
Hi Helen, Events can only be declared in class modules and a userform is a class too. Modules are not.
Comment by: Helen Feddema (1-1-2014 23:02:50) deeplink to this comment
I have tested my sample database that uses your Treeview alternative in 32-bit Access 2013 running on 64-bit Windows 7, and it works fine in that environment. I may be able to test it in 64-bit Access 2013 soon, and I will post a message about that test.
Comment by: Jan Karel Pieterse (2-1-2014 06:46:00) deeplink to this comment
Hi Helen, Thanks for letting us know!
Comment by: Ian (6-1-2014 18:22:21) deeplink to this comment
Jan, this is awesome. It would be amazing if you are ever able to find the time to create a version of the listview control.
Comment by: Duncan Broom (9-1-2014 21:22:14) deeplink to this comment
This is great. We're starting to have new machines introduced with Access 2013 (which doesn't work with Treeviews, presumably a mscomctl issue...). The test file works great, so I'm very hopeful! I'm surprised the NodesAdd functions were housed in the treeview class, rather than creating a custom Nodes class (Collection wrapper) and storing the Add/Remove/Clear/etc methods to that class.
Comment by: Jan Karel Pieterse (10-1-2014 11:16:59) deeplink to this comment
Hi Duncan, Perhaps splitting this into three classes might have been neater, I don't know. As a side effect, it currently only takes copying two classes into your project to make it work :-) I would expect the old treeview to work in Access 2013, as long as you install the 32 bit version.
Comment by: Thanapol Rojanapanpat (17-1-2014 02:45:10) deeplink to this comment
It is easy to integrate and works in both 32bits and 64bits MS Word 2010 on WinXP and Win8.
Comment by: Tony Matyas (23-1-2014 09:04:17) deeplink to this comment
Hi Jan Karel, I succeeded in filling your Demo TV with XML Elements. In order to show further details on the Form, I tried to create another TreeView in the same UserForm of your Demo Version 'ufDemo' by means of a second Frame ('Me.frTreeControl2') with additional TreeView Nodes. I added the following code, but didn't succeed at the moment when I want to add Root/Nodes to the second Frame without subsequent error already in class clsTreeView's 'BuildRoot' procedure . - What can I do using your Demo code as far as possible? Thanks in advance
Comment by: Jan Karel Pieterse (23-1-2014 10:08:00) deeplink to this comment
Hi Tony, Am I right that you now have two subs called "InitializeDemoIcons"? That is not allowed. You should combine the code in both subs into one.
Comment by: Tony (23-1-2014 19:25:55) deeplink to this comment
Hi Karel, no, of course I did name the second sub differently InitializeDemoIcons2(), just omitted the '2' in my posting. I would appreciate testing your ufDemo Code with this small modifications.
Comment by: Jan Karel Pieterse (24-1-2014 07:06:48) deeplink to this comment
Hi Tony, If you can send your modified demo to me I'll have a look.
Comment by: Steve (14-2-2014 21:14:15) deeplink to this comment
Getting your Treeview control working in my Access application was straightforward. Just took a few edits to replace the ActiveX Treeview control properties and methods. Thank you so much for this wonderful work. My application dynamically populates the child nodes when the parent nodes are expanded, instead of populating the entire tree at once. For this strategy to work, I rely on the Expand and Collapse Events of the existing ActiveX control. How could I trap these events using your control?
Comment by: Jan Karel Pieterse (17-2-2014 06:58:40) deeplink to this comment
Hi Steve, All clicks on the tree are handled by this routine:
Comment by: Daniel (19-2-2014 22:31:58) deeplink to this comment
Hi there, great tool. Is is possible to store this as a dll file or something similiar so that I can load it as a library (Tools => References). You know, like the old form was stored in the "Microsoft Windows Common Controls 6.0" if i"m not mistaken. Thanks a lot! Daniel
Comment by: Jan Karel Pieterse (20-2-2014 09:20:09) deeplink to this comment
Hi Daniel, Well, not easily that's for sure. We tried to package this as much as we could so implementing it is as easy as possible. One great advantage of having to include it in your VBA project is that it makes your solution self-contained. No need to ensure your users have any special dll's installed.
Comment by: Steve (21-2-2014 03:49:20) deeplink to this comment
Wanted to share my experience after experimenting with the Treeview control for about a week. Consider the scenario where the Treeview Userform is embedded on a form (just like frmDemo in the sample file), call this Form A. Form B is a data entry form bound to a table. Both Form A and Form B are subForms on a Master form. The Treeview nodes are populated on the load event of the Master form. If the user first clicks on a field in Form B, everything functions as expected. If the user first clicks on a node on the Treeview in Form A, the fields on Form B become locked. More accurately, the fields on Form B can receive focus, but they cannot be updated. This behaviour has something to do with the Treeview container receiving focus before any other field does. It behaves as if it won't release the focus to any other control. I've developed a bit of an exotic workaround, but was wondering if anyone has seen this strange scenario?
Comment by: Peter Thornton (24-2-2014 15:32:00) deeplink to this comment
Hi Steve, I'm not sure what's going on there wrt "the fields on Form B can receive focus, but they cannot be updated" or "It [the treeview container] behaves as if it won't release the focus to any other control". We deliberately include stuff related to focus for two reasons, if I briefly explain maybe that might shed some light on your situation: 1 When the treeview container is clicked that does not of itself set focus to its subform host, hence moSubControl.SetFocus in the container's click event. 2 To enable keyboard navigation with tab. When user tabs to the subform and it receives focus the userfom window is brought to the top so it can receive the keyboard. Then if user keys tab (on the treeview) focus will be passed to the next control in the "tab order" in the main form. See moSubControl.Parent.SetFocus (switch focus to the main form) and ctl.SetFocus (activate the adjacent control in the tab order) If user with the mouse activates a different subform, or any control on the main form, it should gain focus in the normal way (the subform's exit event lets the container know its lost focus but only to give the active node a different highlight). It's possible to have multiple subforms each with a treeview and switch focus between them, either with the mouse or tab. IOW there should be no problem with the treeview container or its subform releasing focus. If the above does not lead to an obvious fix by all means send a file that replicates the problem you described, but please give full and clear instructions.
Comment by: Dennis (12-3-2014 13:39:17) deeplink to this comment
I am running Access 2003 on a Windows 7 64bit machine. When I try to open one of the treeview forms I get the error "Invalid flag error" and the code stops at "Set mfrTreeControl = mUF.Controls.Add("Forms.Frame.1", "frTreeControl", True)". When I reset in the code window the form opens showing just the icons in the treeview area. When I close the form I get another error "Object variable or With block variable not set". When I click debug the code stops at the form "fsubTreeview" Close procedure at "Unload mUF". Any ideas would be greatly appreciated.
Comment by: Jan Karel Pieterse (12-3-2014 13:57:56) deeplink to this comment
Hi Dennis, Is this with an mdb of yourself or with the download example mdb? If you open the demo mdb, press the "How do I?" button to get an elaborate explanation on how to implement the treeview in your projects.
Comment by: Diego F. Pereira-Perdomo (15-3-2014 17:42:04) deeplink to this comment
Hi Jan, Forget my previous comments. Something happened with my Office, and after using the repair tool everything started working again as expected. Regards, Diego
Comment by: Jan Karel Pieterse (15-3-2014 17:46:03) deeplink to this comment
Hi Diego, Glad you could sort it out!
Comment by: Hakan (19-3-2014 20:10:43) deeplink to this comment
Hi, How can I select any node exist in tree with code ? I used
Comment by: Hakan (19-3-2014 20:23:31) deeplink to this comment
Hi, Forgot my last question. I set focus to subform of treeview berfore set activenode. It's fixed Thanx Hakan
Comment by: Paul Sondervan (20-3-2014 09:59:28) deeplink to this comment
The "download the treeview sample Access database" indicates that its build 025, 16 Oct 2013. When I download this version it turns out to be build 024, 20 May 2013. Is it possible to put the right download on the website? Is there a history with the changes in build 025? Regards, Paul Sondervan
Comment by: Ermanno (25-3-2014 12:08:09) deeplink to this comment
Hi and good morning. First of all I'd like to thank you for your outstanding job converting mscomctlib.treeview into pure vba code. It's saving my life in converting MS TreeView vba code able to run in Access64 (Office 2010) environment. I've only added node_MouseEvent to best cover my needs. I've only a question conerning a weirtd behaviour when node_MouseUp should fired after loading the treeview. To be more precise, I load a bunch of nodes from an ADO recordset. At the end of the reading cycle, if a certain condition is matched, a single node has to be selected and viewable (former treeviewNdoe.ensureVisible equivalent to mcTree.ScrollToView cNode) Everything works fine meaning the node is regularly selected when condition matches but if I right-click, no MouseUp event is fired but If, for example, I select the previous node using mouse right-click the event is fired up. This behaviour happens only once and just after the recordset has been loaded into treeview control. Here is the affected code
Comment by: Jan Karel Pieterse (25-3-2014 12:23:15) deeplink to this comment
Hi Ermanno, Thanks! I suspect if you put the Refresh command BEFORE setting the activenode it should work, but I'm not sure :-)
Comment by: Ermanno (25-3-2014 15:20:57) deeplink to this comment
Thank you Jan for your prompt reply and I apologize for my delay in answering. Unfortunately the proposed solution does not work. I've realized that if I use the following code
Comment by: Peter Thornton (26-3-2014 14:29:49) deeplink to this comment
Hello Ermanno, You've have identified a small bug or rather a little detail that somehow got removed in a recent version. What should happen is at startup the activenode should be highlighted depending on whether the treeview is in focus (backcolor) or not in focus (only the border). As a temporary fix *all* users can add the line that simulates the treeview gaining or losing focus and in turn apply the relevant highlight
Comment by: Peter Thornton (26-3-2014 16:47:49) deeplink to this comment
Further to my previous comments about highlighting the activenode at startup, there is no bug but to highlight correctly at startup the following can be included after calling .Refresh when building the treeview for the first time - For Excel and Word (not Access)
Comment by: Chris (9-4-2014 06:39:07) deeplink to this comment
Last year there was a comment about you working on a version with ControlTipText. Is there any update? Has anyone else got a working example with the ControlTipText property?
Comment by: Jan Karel Pieterse (9-4-2014 07:14:31) deeplink to this comment
Hi Chris, The current build 025 has a controltipText property in the clsNode class.
Comment by: alberto (10-4-2014 17:10:24) deeplink to this comment
Hi! how to populate the tree with a recursive function? Using traditional ms tree control I use
Comment by: Charles Sestrem (10-4-2014 21:05:34) deeplink to this comment
Hello Guys, congratulations class, is very functional. I am using the Treeview class, and am having difficulty changing the icon image selected at runtime. For the name I am updating DESTS way. Set cNode = mcTree.ActiveNode cNode.Caption = "Charles". And for image I'm using as well; cNode.ImageMain = "My Icon" but does not update Can anyone help me ?
Comment by: Chris (10-4-2014 22:41:32) deeplink to this comment
Yes, I found it in the Excel project - thank you. I have it working fine now. BTW, The Access example database doesn't contain it and is still on build 024.
Comment by: Jan Karel Pieterse (11-4-2014 11:46:47) deeplink to this comment
Hi Charles, You probably have to refresh the tree after changing the icon: mcTree.Refresh
Comment by: Jan Karel Pieterse (11-4-2014 11:47:40) deeplink to this comment
Hi Alberto, I see no reason why that shouldn't work with our treeview?
Comment by: Charles Sestrem (11-4-2014 16:01:40) deeplink to this comment
Thanks for the feedback Jan Karel. I'm using the mcTree.Refresh line, just had not posted the previous comment. is a pity unable to update the icon. My project need to use dynamic icons when the user makes a change of status. example: Status = Free -----> green ball Status = Closed -----> red ball My Code Example:
Comment by: Jan Karel Pieterse (11-4-2014 16:35:10) deeplink to this comment
Hi Charles, Actually, you can update the icon, it just takes a bit of convincing to have the tree update it. This seems to work:
Comment by: Greg Maxey (18-4-2014 15:37:21) deeplink to this comment
I have used your tree view control in a utility for mapping content controls in Word. Mapped controls (their XML) can then be displayed in a userform using your control. I am getting an error "duplicate key" and the source is in your clsNode Public Function AddChild procedure whenever my XML contains nodes with "similar" but "different" base names. For example the following XML will cause the error: <?xml version="1.0"?> <CCInstaMap xmlns="http://USAOCCInstaMap/GKM"> <Name xmlns=""/> <name xmlns=""/> </CCInstaMap> Is their any way to make the key in your moTree.Nodes collection case sensitive? Currently it treats "/ns0:CCInstaMap[1]/Name[1]" and "/ns0:CCInstaMap[1]/name[1]" as the same key when in fact they are different. I'll be happy to share the utility with you if you need to conduct any test. Thanks.
Comment by: Jan Karel Pieterse (18-4-2014 19:24:22) deeplink to this comment
Hi Greg, Do you happen to have "Break in class modules" turned on?
Comment by: Peter Thornton (19-4-2014 12:01:16) deeplink to this comment
Hello Greg, When a node is added the Key (if included) is added as the Collection item's key; if a duplicate is attempted the error is trapped and referred back to tell you "don't do that"! Collection keys are not case sensitive, normally that's useful but in your case unfortunately it isn't. To demonstrate -
Comment by: David (12-5-2014 03:10:22) deeplink to this comment
This functionality you have created is fantastic. For what I need, I would also need a 64 bit grid control with hierarchy capability (I currently use the iGrid). Is this functionality available by any chance? Thanks
Comment by: Jan Karel Pieterse (12-5-2014 10:03:31) deeplink to this comment
Hi David, Thanks! I have no real plans to create such a control, it is a heck of a lot of work. However, I've recently seen a chap who has managed to use (heaps and heaps of) API functions to backdoor himself into the Windows common controls after all, even with 64 bit Office: http://homepage.bluewin.ch/submax/5201.html
Comment by: Eduardo (17-5-2014 22:51:15) deeplink to this comment
Hi, sorry by my english, I speak spanish. First,thanks you for sharing your knowledge. I have a question: How can I add an event by e.g (open a new form) making double click on a last child child node or on a check box (but the check box appears at all nodes, not only at last child node) Thanks in advance Regards Eduardo
Comment by: Jan Karel Pieterse (18-5-2014 20:51:29) deeplink to this comment
Hi Eduardo, Do you mean have something respond to a click on a checkbox in the treeview?
Comment by: Eduardo (19-5-2014 14:07:56) deeplink to this comment
Hi Jan, Yes, e.g open a form or file after select a check box o double click at node. The form has a reference to the node or after select the node, this node refer to a path file Thansks Eduardo
Comment by: Jan Karel Pieterse (19-5-2014 14:34:25) deeplink to this comment
Hi Eduardo, Please see this comment: Comment by: Jan Karel Pieterse (5/24/2013 12:55:38 PM) To view the comment, click: https://jkp-ads.com/Articles/treeview.asp?AllComments=True
Comment by: Jaap van der Sijp (3-6-2014 16:22:12) deeplink to this comment
I have found an issue with the Treeview, which I have been able to replicate in your demo. The issue is that if you leave a control (say a frame with some entry fields) and click on the treeview, first an frame_exit event is raised. I use this to check if the data in the fields has been saved. If not, then a messagebox comes up to ask if the user wants to save the changes. Then the focus is transferred to the treeview and the correct node is highlighted. However, the node_Click event is not firing, ie it only superficially looks asif the node has been clicked. Your demo shows this very nicely in that the details of the clicked node do not appear below the treeview. It seems that the messagebox highjacks some of the eventsmeant for the treeview. To replicate this: create a frame on the userform. I added a textbox and a label. Then I added the following 3 subs:
Comment by: Jan Karel Pieterse (4-6-2014 07:57:33) deeplink to this comment
Hi Jaap, If I understand correctly, indeed setting the ActiveNode property does not fire a click event. You can do that by adding the raiseEvent line to the appropriate property in clsTree:
Comment by: Jaap van der Sijp (4-6-2014 13:24:57) deeplink to this comment
No, you understood wrong. But with a bit more testing I have found that it is a general issue and not related to TreeView particular: When the user clicks on a control, and this first triggers a messagebox or dialog (for instance when an onExit check is performed for the previous control), then Windows only sets the focus of the control clicked, by raising a MouseDown event, but does not raise a MouseUp event for the control. The user will have to click again to get the proper event chain going. So to get the behaviour I want I can call the MouseUp event from the Mousedown event sub, but in the (less likely) case that the user didn't want to click and is trying to move the mouse away, (s)he gets the wrong behaviour (The onExit event is raised before the MouseDown event anyway). Consider this topic closed/solved as it is a Windows issue, not a treeView issue
Comment by: Jan Karel Pieterse (4-6-2014 14:48:12) deeplink to this comment
Hi Jaap, Form events can be challenging indeed :-)
Comment by: Gilad (26-6-2014 06:12:38) deeplink to this comment
This is so great. But is it safe to assume that the required references will work under any system? Different versions of Windows or of Office? Thanks Gilad
Comment by: Jan Karel Pieterse (26-6-2014 07:04:13) deeplink to this comment
Hi Gilad, Yes, because there are no references to external libraries involved! Just the default MSForms, Office and Excel
Comment by: Stephen Campain (1-7-2014 03:32:45) deeplink to this comment
This is a fantastic piece of work. Congratulations I have been trying to modify the cmdGetData sub in Form_frmDemo so that it will get data from a table containing some 30k records of hierarchial data identified with parent child and a level identifier i.e. 1, 2,..12. I wish to loop through the records one level at a time and use the mcTree.nodeadd method to build the tree. However, as a noob I am having a problem passing a variable to the SQL statement. At the start of the cmdGetData sub I inserted
Comment by: Jan Karel Pieterse (1-7-2014 07:09:02) deeplink to this comment
Hi Stephen, The proper syntax is:
Comment by: Jaap van der Sijp (1-7-2014 16:08:04) deeplink to this comment
Jan Karel, as promised a donation. I have a good working version. image: https://lh5.googleusercontent.com/rjLHo-6sqzkzml1QDAm0aESxfzzRyypRb3Wqw9tCFi4=w333-h226-p-no Any pointers as how to make the nodes mouse drag-able for move or copy? The Office VBA TreeView has a OLEDrag/Drop property, but simple controls don't have this.
Comment by: Jan Karel Pieterse (2-7-2014 11:26:52) deeplink to this comment
Hi Jaap, Thanks for the screenshot, looking good. Peter Thornton has been working on a pro version of the treeview which would include drag&drop, but I'm not sure of the status.
Comment by: Peter Thornton (3-7-2014 09:47:26) deeplink to this comment
Hi Jaap, Drag and drop can work well with the treeview but have held back from making a demo for various reasons, partly as it's difficult to make a generic demo which typically will need considerable adapting for specific applications. In the meantime we'd be happy to help implement D&D in your app on a consultancy basis. If that might be of interest please contact either of us (emails inside the demo).
Comment by: luke (9-7-2014 08:10:30) deeplink to this comment
hi, I am trying to use the MSAccess version; this is/was an MDB. Whenever I tried to Design the main form frmDemo I get the error 'You do not have exclusive access to the database at this time'. I am the only one opening it, is only open once on my local drive. I tried upgrading it to Access2010 accdb , still same problem. Then tried to build the accdb from scratch and import all the forms and code (added the FM20.dll reference). Everything compiles ok, but still same message ;do not have exclusive access...' I am using Win7 Pro on a 64bit machine, with MSAccess 2010 32bit
Comment by: Jan Karel Pieterse (9-7-2014 10:35:01) deeplink to this comment
Hi Luke, I'm no Access expert, but the Access demofile contains specific instructions on how exactly to implement the treeview in your own Access database. Perhaps that description gives you some pointers?
Comment by: Davide (18-7-2014 22:46:21) deeplink to this comment
I cannot change or insert icons in the access version. I can select the properties and try to change the image by selecting "..." but the new image is not loaded and the icon doesn't change. Ther's a way to do that? Bye
Comment by: Muneeb Mansoor (21-7-2014 09:04:26) deeplink to this comment
Hi Guys, First of all, amazing work on this. Exactly what I was looking for! I have one question though. Is it possible to use a drop down menu as a child instead of static values? For example, I would have a parent node called Attributes and then multiple child nodes with the options to select values from a drop down. Possible? Thank you.
Comment by: Jan Karel Pieterse (21-7-2014 21:01:38) deeplink to this comment
Hi Davide, I'm sorry, I am not sure how you are trying to add pictures, can you detail out the steps?
Comment by: Jan Karel Pieterse (21-7-2014 21:02:33) deeplink to this comment
Hi Muneeb, You could use the click event of a node to add new nodes to it at runtime if that is what you mean?
Comment by: Muneeb Mansoor (21-7-2014 21:29:52) deeplink to this comment
Let me try to demonstrate what I'm try to say: + Parent 1 | | - - - - Child 1 <-- I want this to be a drop down menu.
Comment by: Steven Elliott (28-7-2014 06:23:00) deeplink to this comment
Thank you so much for this awesome treeview control! I have a VBA app I developed in AutoCAD and I needed to update it for the 64-bit version. It is a block librarian that made extensive use of the MS Treeview control, which is not an option for 64-bit, as you well know. Your all VBA Treeview is a blessing. It works great in AutoCAD. The only place I had any problem (so far -lol) is where you use the variable 'HostProject' such as Private Sub BuildRoot. That seems to be an MS Office variable that is not available in AutoCAD. I simply REMmed out the offending lines and hard-coded what I needed there, since this app won't run on other host programs anyway. Great job on this accomplishment! And many, many thanks for making this available to others! Sincerely, Steven Elliott
Comment by: Hannu Metso (5-8-2014 03:21:54) deeplink to this comment
How to make initalisation from worksheet data same data structure as dumped data in excel demo? I want to builsd some default parts to make treeview Thanks Hannu
Comment by: Jan Karel Pieterse (12-8-2014 10:40:13) deeplink to this comment
Hi Hannu, The demo already shows how to add items to the tree. It is a matter of cleverly reading the worksheet and adding the items in a similar fashion. Something like this:
Comment by: Jan Karel Pieterse (12-8-2014 10:54:24) deeplink to this comment
Hi Steven, Thanks and you're welcome!!
Comment by: Jan Karel Pieterse (12-8-2014 11:17:29) deeplink to this comment
Hi Muneeb, That would require modification of the control. It can be done, but we would have to start a (paid) project if you want assistance with that.
Comment by: Hannu Metso (14-8-2014 08:22:45) deeplink to this comment
Thanks for code Comment by: Muneeb Mansoor (7/21/2014 9:29:52 PM) Let me try to demonstrate what I'm try to say: + Parent 1 | | - - - - Child 1 <-- I want this to be a drop down menu I am also keen on that
Comment by: Jan Karel Pieterse (14-8-2014 11:40:23) deeplink to this comment
Hi Hannu, Noted. But as I replied above, this would need to be a paid project as it deviates significantly from a normal treeview control.
Comment by: Siegfried Wieser (18-8-2014 08:08:34) deeplink to this comment
Thank you very much for this awesome treeview control! Is there a way to make the expander to "+" without children? At the moment i make "fakechildren" and remove them, when i expand to add other nodes. Or maybe im completly wrong how i do this? Sincerely Siegfried Wieser
Comment by: Jan Karel Pieterse (18-8-2014 09:50:13) deeplink to this comment
Hi Siegfried, I'm not sure what you need; are you saying you'd like to have expanders regardless of whether the node has childnodes? I think that isn't how the Common controls treeview works, hence it isn't the way ours works.
Comment by: Siegfried Wieser (18-8-2014 11:38:46) deeplink to this comment
Thank you for your quick response! Yes, I need expanders regardless of whether the node has childnodes. Thats because i want to make the childnodes before the node expands. The Node_click event does a different job in my treeview. I have a lot of nodes and the childnodes are only needed when you expand them. Thank you again for your awful treeview control! Siegfried Wieser
Comment by: Jan Karel Pieterse (18-8-2014 11:41:37) deeplink to this comment
Hi Siegfried, You can set a nodes Expanded property to control whether or not it's childnodes are visible, is that what you are after perhaps? Indeed, it is an awfully nice control isn't it? :-)
Comment by: Siegfried Wieser (18-8-2014 12:04:41) deeplink to this comment
Hi Jan Karel, if there is no Expander, the user has no Control to click at for expanding. As i mentioned, the Node_click event does a different job in my treeview. So how can the user expand the node - so that i can make the children? Thank you very much for your quick Answers! Siegfried Wieser
Comment by: Jan Karel Pieterse (18-8-2014 14:21:23) deeplink to this comment
Hi Siegfried, Ah, the treeview expects the childnodes to already be there. The code is currently in a state that that is not easy to change. This is the routine where the expander is added: Private Sub BuildTree(..... It is only called when a node has childnodes.
Comment by: Peter Thornton (20-8-2014 12:14:14) deeplink to this comment
Hi Siedried, I don't follow if the objective is to make nodes at design but not expanded, or "to make children"..? A method to add new nodes during runtime is already built-in to the treeview, see the buttons on the demo form "Add Sibling" and "Add Child". There are several ways you can provide on the UI for the user to add new nodes, eg a button on the form (as in the demo but with more details), a right click context menu on node, press a shortcut key on the activenode, etc.
Comment by: Frans Truyens (20-8-2014 18:38:09) deeplink to this comment
Is it possible to use a double-click event in the treeview? I would like the user to be able to double-click on a node, and intercept that event
Comment by: Jan Karel Pieterse (20-8-2014 19:02:12) deeplink to this comment
Hi Frans, There already is a double-click event, but it is currently in use for node editing. You can change that relatively easy though.
Comment by: Miguel Mezquita (26-8-2014 21:05:38) deeplink to this comment
Hi Jan, Before anything, let me say thanks for sharing such an amazing piece of work. I am working in a project where the user needs to compare three similar products and as the products are not identical, I think that I will show three trees at the same time. Also, every node has a picture. Do you think that your component could implement these requirements? Thanks again and look forward to hearing from you. Cheers!
Comment by: Jan Karel Pieterse (27-8-2014 10:22:55) deeplink to this comment
Hi Miguel, You're welcome! Nice to hear that people enjoy our work. Yes, you can have as many treeviews side-by-side as you like, as long as you do not exceed whatever it is a userform can handle regarding the total number of controls (which limit I do not really know). Three should not be a problem at all. To do that, each tree needs its own frame (e.g. frmTree1, frmTree2, frmTree3) and its own module level variable, e.g. mcTree1, mcTree2, mcTree3. The documentation describes how to handle icons.
Comment by: Hannu (29-8-2014 04:45:10) deeplink to this comment
I modify little dump data so can insert to cells also node`s key, because node`s captions can be same and you get error when read data back to treeview control. Maybe useful someone else too...
Comment by: Jan Karel Pieterse (30-8-2014 11:47:39) deeplink to this comment
HI Hannu, You should not be getting duplicate key errors, they are triggered by a setting in your VBA editor "Break in class modules" is not the usual setting it should be "Break on unhandled errors".
Comment by: Tobias Johnson (2-9-2014 18:08:10) deeplink to this comment
Dear Jan, first off all great, no outstanding work!! One question. I would like to use it as a hierarchie for different countries within a region. Is there a way that a user can just douple click on an element ( Sibling or Child) and this will be lets say linked to a certain cell within Excel so I can perform some other calculations? Thank you in advance for your feedback. Tobias Johnson
Comment by: Jan Karel Pieterse (3-9-2014 11:51:08) deeplink to this comment
Hi Tobias, Right now the double-click event is used to enable editing a node in the treeview. This is handled in the clsNode class by this event sub:
Comment by: Andy Barber (9-9-2014 13:03:11) deeplink to this comment
the 'How Do I' section for the MS Access version of this states we need the MS Forms 2.0 library, however this is not installed on some of the machines I need to run this code on and Microsopft states the the FM20.dll file is not a re-distributable file. Any ideas how I can get round this. your tool is excelent and I would really like to use it. Please reply to attached email
Comment by: Yves (9-9-2014 15:32:58) deeplink to this comment
Hi Jan, Using your treeview to prototype a development in access... Do you see a way to use images from files instead of having to glue them in form control?
Comment by: Jan Karel Pieterse (9-9-2014 21:24:12) deeplink to this comment
Hi Yves, You should be able to load an image from a file, but you would have to do the plumbing yourself:
Comment by: Jan Karel Pieterse (9-9-2014 21:25:21) deeplink to this comment
Hi Lex, Odd, any Office installation should have the Forms dll installed. If you insert a userform in e.g. Excel VBA, does the reference not turn up?
Comment by: Yves (10-9-2014 10:29:47) deeplink to this comment
Hi Jan, thanks for your reply. Do you mean image should first be added to form control? Assigning image in tree don't seems to be accepted... I wrote
Comment by: Jan Karel Pieterse (10-9-2014 11:16:40) deeplink to this comment
Hi Yves, I mean that the control currently does not support adding images from file, but that you can add that support yourself :-)
Comment by: Yves (10-9-2014 11:38:37) deeplink to this comment
OK, will do if/when I have time to do :-) Your tree code is already big help for me and I understand he can not do everything!
Comment by: Alex (24-9-2014 13:45:16) deeplink to this comment
Hi Jan, I just discovered your Treeview, and it's really great. I'm playing around with it, to find out how it works, and have just one question : I can't figure out how to display treelines in the treeview. Whether I check the 'Show lines' box in the demo application or not, no line is displayed between nodes... Using build 025 under Access 2010 64 bits. Any idea where this can come from ?
Comment by: Jan Karel Pieterse (24-9-2014 13:58:26) deeplink to this comment
Hi Alex, If you expect to see horizontal lines between the nodes, then you are right, those are not in the treview (nor are they in the original AFAIK). The LInes checkbox controls whether or not the tree connection lines display.
Comment by: Alex (24-9-2014 15:27:08) deeplink to this comment
I was talking about the lines that links one node to an other, like in the screenshots at the top of this page. So, something like that :
Comment by: Jan Karel Pieterse (24-9-2014 15:54:56) deeplink to this comment
Hi Alex, Odd, they should show. They do on my system. What Excel version and Windows version are you on?
Comment by: Alex (24-9-2014 16:10:48) deeplink to this comment
I'm working on Windows 7 64 bits, Access 2010 64 bits. But it works fine on Windows XP 32 bits with Access 2010 32 bits. Oh, by the way, there are two small bugs (at least under Access) : Application.Version is as String value ("14.0"), so Application.Version >= 12# throws an error in frmFelp.Form_Load and in the AutoExec macro.
Comment by: Jan Karel Pieterse (24-9-2014 16:51:12) deeplink to this comment
Thanks for letting me know about the bug. The tree shows up just fine on my system, I'll ask Ben to take a look.
Comment by: Dale Fye (28-9-2014 15:14:53) deeplink to this comment
Peter, In your response at:Peter Thornton (8/20/2014 12:14:14 PM) you mentioned "right click context menus". How would you implement these? I've got my menus built, use them all the time with the ActiveX treeview control, but now that I need something that works in 64 bit Office. Unfortunately, I have not figured out how to test for the MouseUp event and detect whether the mouse button that was clicked is the left or right button.
Comment by: Peter Thornton (29-9-2014 13:10:23) deeplink to this comment
Hi Dale, MouseDown/Up node events were added in v023 so they should exist in your version. FWIW they were included to enable the previous activenode to be semi highlighted until user releases mouse on the newly selected node, or reactivate the previous node if user moves the mouse off the new node before releasing, in effect cancel selection. Trust you appreciate the attention to detail, but I digress <g> In the mctlControl_MouseUp add the two new lines as indicated
Comment by: WOJTEK (30-9-2014 17:30:13) deeplink to this comment
It's very usefull tool for programmer. I must admit it is the best solution found by myself in this area. I'd like to use the solution in my job. But my question is: what do you mean by statement "The code may not be republished without prior written consent of the authors."?
Comment by: Jan Karel Pieterse (30-9-2014 19:38:11) deeplink to this comment
It just means that if you want to make the code publicly available by writing about it (for instance an article on your blog) and including code from it, you need our permission. You can use the tool in your projects without having to ask permission. But if you add useful things to it, we ask you to send them to us.
Comment by: Andy Barber (5-10-2014 11:42:42) deeplink to this comment
Has anyone used drag and drop with the tree view to add/Move nodes? If so can you point me in the right direction to implement in my system. I have a list of entries which I wish to drag to a node in the tree and add the items as children of that node Many thanks Andy
Comment by: Alex (8-10-2014 09:47:09) deeplink to this comment
Hi Jan, I think I found it, concerning my problem with tree lines not showing up. When drawing the lines, you use this code :
Comment by: Jan Karel Pieterse (8-10-2014 10:23:12) deeplink to this comment
Hi Alex, Thanks for bringing that to our attention!
Comment by: Peter Thornton (9-10-2014 13:35:45) deeplink to this comment
@ Andy Barker We've held back from posting a generic Drag & Drop example as it's best adapted to individual projects and can be a bit complicated, we could spend a long time fielding questions! However we can probably put D&D together for your project privately but it would depend on what and where your "list of entries" is that you want to drag from. @ Alex My thanks too for flagging your line colour issue. I think normally a lighter colour than black is more appropriate for lines, but clearly the scrollbar colour is not good if almost the same as the background colour in some user's schemes. Can you see another system colour in your scheme that would be suitable and also likely work well in more typical setups. Feel free to email me privately.
Comment by: Alex (9-10-2014 16:52:14) deeplink to this comment
Hi Peter, Well, I'm afraid there is no safe choice apart from vbWindowText, assuming that nobody will set the same color for text and background... If I choose another system color based on my color scheme, it will probably fail with other schemes... The only workaround I could see would be to check the scrollbar color against the windows background color, and, if they are too close, then apply another color. And choosing this 'other' color could also be tricky... Or, possibly, falling back to a vbWindowText dotted line when scrollbar color is too close to window background (if the dotted line attribute can be set for a label) ?
Comment by: Andy Barber (10-10-2014 16:00:30) deeplink to this comment
Thanks Peter, I'm using the Treeview in MSAccess. On the left of my screen I have the tree view and on the right I have a list box which allows multiple selections. I need to be able to drag the information in the list box (will be the key and caption info) and drop it into a node on the tree. Many thanks for you assistance with this and I hope the above is enough info for you. Andy
Comment by: Peter Thornton (10-10-2014 16:19:19) deeplink to this comment
Hi Alex, No we can't do dotted lines. But we can compare the normal default colour for lines to the background, and if fairly similar change to a new default of black (or white if a dark background), all automatically. In addition include a new exposed "LineColor" property to override. We'll (probably) include this in the next version (026), not sure when that'll be so in the meantime contact me if anyone needs this.
Comment by: Alex (13-10-2014 13:19:11) deeplink to this comment
Hi Peter, Yes, I think it's the only way to do it. And the override property will help to get rid of the most difficult cases. I also saw that you have a fallback to RGB(180, 180, 180) for the checkboxes ForeColor in some cases. This could lead to the same problem if the background nears this color. If you need some more testing or information, just drop me a mail. Thanks !
Comment by: Peter Thornotn (13-10-2014 16:49:26) deeplink to this comment
@Andy, Although the Access Listbox doesn't support D&D it does seem to respond to the built-in MSForms approach and in turn the DragOver and Drop events on individual nodes can be trapped. Alternatively, can simulate D&D which would need to include the equivalent a "HitTest" (eg to toggle cursor and/or highlight when over a suitable node and then to id the drop target). However with this approach MultiSelect=Extended doesn't look viable but "Simple" seems OK. @Alex, Well spotted! The RGB(180, 180, 180) forecolor would only affect "textbox" style checkboxes (on a similar grey background), which we kept for compatibility before figuring the icons not only look better but counter intuitively don't use more resources. I suspect less important to do as I described for Lines, not sure, maybe we'll wait for complaints before deciding <g>
Comment by: Alex (13-10-2014 19:21:17) deeplink to this comment
Yes, that sounds quite reasonnable. No reason to fix it if nobody finds it broken. :)
Comment by: Barry C (17-10-2014 00:30:46) deeplink to this comment
First of all - Amazing piece of work! I'm in the middle of working this into a project and I'm loving it. But... I'm having the same issue as Davide (a couple of months back). Using Access (tried it in versions 2k, 2k3 and 2010) I can not add a new image or change an existing image in the image frame. If I select an existing image or add a new one, the "Picture" property in the property sheet is read only. Other properties (name, size etc) are fine. Works fine in Excel (all versions), but not in Access. Any ideas? In the meantime, I'll try a work around - do that work in Excel, then export it to Access. Thanks BC
Comment by: Peter Thornton (19-10-2014 18:22:47) deeplink to this comment
Hi Barry, Strange, I can't load a new picture to an image control in MSForm frame container either, thought I could! OK, rather than "export" you can add the image controls to a form in Excel (or Word) and simply copy the control with it's picture to the Frame container in Access (can copy/paste multiple image controls at the same time). The Frame in the demo was added as an ActiveX "Microsoft Forms 2.0 Frame". Note you can also pass a Collection of picture 'handles' as an alternative to passing the Frame control if more convenient to store your images elsewhere. You say you've tried it in Access 2000 - interesting, does the demo actually work for you? In light testing although in 2k it all compiles OK trying to load in resulted in a bunch of "Object of class does not support the set of events" errors, no idea why! Untested in 2002. 2003 should be fine but it seems only if compiled in 2003 (may get similar error in 2003 if compiled in 2007 or later), again no idea why. (For anyone else reading this concerns Access only.)
Comment by: Dale Fye (21-10-2014 14:09:37) deeplink to this comment
Have been playing with the treeview for several days now. Was able to add code on the MouseUp event to display a popup menu, that worked great. One of the items on my popup menu is to expand or collapse the currently selected node and all of its children. The default, when you collapse the root node appears to collapse that node, but to leave all other expanded nodes expanded. Since my tree can 4 or 5 levels deep, I want to be able to right click on the node and select Expand All or Collapse all. Right now, my Collapse code looks similar to:
Comment by: Dale Fye (21-10-2014 14:51:30) deeplink to this comment
Is there a way to implement the MouseUp event in the main form? I notice in the frmDemo that there are Click, KeyDown, and NodeCheck events listed, but don't see the MouseUp event. I've added code to the mctlControl_MouseUp subroutine in clsNode, but would like to leave that code unchanged and simply be able to refer to the mcTree_MouseUp event in my main forms code module. Is there something special that I need to do to make that event visible in the main form?
Comment by: Jan Karel Pieterse (21-10-2014 15:30:58) deeplink to this comment
Hi Dale, The easiest way is to call the mcTree.NodeClick method with the Node's Expander control as the first argument. Like so:
Comment by: Dale Fye (21-10-2014 16:10:34) deeplink to this comment
That seems to have done it. Thanks.
Comment by: Dale Fye (22-10-2014 16:05:38) deeplink to this comment
Guys, I'm trying to add some images to the image frImages in Access 2007. I was able to drill down to the image control on the form, and then select properties, but am unable to change the actual image. I noticed a thread above that you cannot currently edit the images in Access, but I'm the first to admit that I'm an idiot when it comes to working with Excel UserForms. I added a user form to an Excel file, added an image control to it, but cannot figure out how to actually assign an image (bmp, png, ico) to that image control.
Comment by: Jan Karel Pieterse (23-10-2014 07:07:56) deeplink to this comment
Hi Dale, There are various ways to add an image to an image control on a VBA Userform: - At runtime you can use the LoadPicture method - At design time: Copy the picture and paste it into the picture property of the image control Click the three-dotted button next to the image property and select an image file
Comment by: Isabella Tsoglin (27-10-2014 14:25:49) deeplink to this comment
Thanks for your amazing work. Works good with Access 2010. I'll try to add "dblclick" event, not yet done.
Comment by: Jan Karel Pieterse (27-10-2014 14:46:29) deeplink to this comment
Hi Isabella, You're welcome!
Comment by: Jon (1-11-2014 16:33:55) deeplink to this comment
Regarding the comments from Barry C (10/17/2014) and earlier from Davide (7/18/2014) I too, just now, am not able to modify the Picture property in an existing Image control, or set the Picture property of a new Image control. In fact I added 4 new Image controls and was able to set the Picture property just 3 days ago. My steps: Open the subTreeView form, display the Toolbox and Properties sheet for the MSForms Frame, add a Image control to the Frame and attempt to set the Picture property. The Picture property does not get set. Nor can I change the Picture property of the existing Images. I can however still do this successfully in a copy of your demo database that I used to learn with. To my knowledge nothing has changed on my computer (Windows updates, etc), but I wonder if it is some sort of Access/Current database security issue. BTW this is an excellent piece of work and I hope to use it more. Thanks, Jon
Comment by: Jon (3-11-2014 16:39:54) deeplink to this comment
To followup my last comment: I actually remember, now, that I added the images to the frame in a copy of the Access demo database and imported it to my application. I have tried to add/edit image pictures to 2 different Access 2007 databases on 2 different machines. There must be some setting in the demo database that makes this possible. It is possible, as mentioned in a different comment by Dale to change the image picture property in code with LoadPicture. This might actually be a better way because it adds some flexibility when initializing your app.
Comment by: Francisco Sciorilli Jr (6-11-2014 20:44:41) deeplink to this comment
Great work !
Comment by: Nicole (7-11-2014 17:00:36) deeplink to this comment
I have the Access build 25 treeview control and everything has been working very smoothly. For our mixed 32 and 64 bit environment this tool is perfect! The information being loaded into the tree view is at a level that a search feature would be helpful to the end user. With that in mind I would like to search and set the active node based on the unique key value or caption assigned when loading. I can set the active node based on the item number but can't figure out how to set the active node based on the unique key. Based on all the features in this tool it is probably a simple task but any feedback would be appreciated. Thanks!
Comment by: Nicole (7-11-2014 17:18:42) deeplink to this comment
Comment by: Michael (7-11-2014 20:15:01) deeplink to this comment
I have added this control to my workbook, but I am being prompted for the VBA project password when Excel quits. I checked Microsoft support, and they say (Q280454) the cause is I am "running a macro that passes a reference for a workbook containing a password-protected VBA project to an ActiveX dynamic-link library (DLL), and the ActiveX DLL does not properly release the reference to the workbook that contains the password-protected VBA project." Has anyone else experienced this? Can you help me fix it? I'm pretty sure it has something to do with how I coded my function to use these classes. Any help is appreciated.
Comment by: Kurt Bergman (7-11-2014 21:37:15) deeplink to this comment
Re: Collapsing all nodes... In the populate Tree Procedure I set cnode1.Expanded to false. This add-in rocks!!
Comment by: Peter Thornton (8-11-2014 12:15:28) deeplink to this comment
Hi Nicole, If you are using unique keys it's easy to activate the node, also scroll it into view and expand it if necessary, eg
Comment by: Jan Karel Pieterse (10-11-2014 11:32:05) deeplink to this comment
Hi Michael, This is a problem that may be caused by an add-in loaded on your system. Adobe has a reputation causing this for example.
Comment by: Nikola Petanjko (10-11-2014 15:44:12) deeplink to this comment
Hi, Thanks ! thanks ! Thanks for your wonderfull treview I need to add 3 features - collapse all from root (or from selected node) - expand all from root (or from selected node) - collapse all + expand up to deep level N keeping collapse since any branch from level N I will start them now, if you already have such features, thanks for sharing. Regards Nikola Petanjko Switzerland
Comment by: Jan Karel Pieterse (10-11-2014 16:04:30) deeplink to this comment
Hi Nikolai, Yes, such functionality is already present. Check out the ExpandToLevel method of the tree class.
Comment by: Lance Duvenhage (14-11-2014 11:31:53) deeplink to this comment
Hi Nicole, not sure if you found a solution for your problem. I used the code to read from different tables with one to many relationships. And can do the surge function you need, use the unique ID to run a query based on the selected node. May be along way of doing things but it works very well. Can share if it needed. What I would like to be able to do is when you rename the a node, how can this be saved in the table? My second question will be almost the same, If I add a new node how can this be saved in the table. My last question will be, the tag function, how to use multi select in to run the query. (Have an idea but have not tried it) Any advice will be helpful.
Comment by: Ake (14-11-2014 23:14:39) deeplink to this comment
Excellent job!
Comment by: Peter Thornton (17-11-2014 11:30:47) deeplink to this comment
Hi Lance, 1. By "rename a node" I assume you mean if the user manually edits a node with double-click or F2. Look at the AfterLabelEdit Event, slso see the example in the demo if you try and edit the caption to include "123" or "456". 2. You didn't explain if adding a new node is instigated by the user or forced by your code. Either way you should be able to know the details of any new node and update your table, either in real time or perhaps after user presses an update button. 3. "MultiSelect" is not implemented. Suggest use checkboxes, depending on your scenario the TriState option might be useful (see the demo).
Comment by: Aaron (10-12-2014 00:42:32) deeplink to this comment
Was this control written in VB.Net? I have created a ActiveX Control in C# and successfully made the dll, which I am able to bring into a VB form, but would like to use it directly on a excel worksheet. When I select the ActiveX control and try putting it on the worksheet is gives the error message "cannot insert object". Do you know how to get ActiveX objects to work on worksheets?
Comment by: Jan Karel Pieterse (10-12-2014 09:24:38) deeplink to this comment
Hi Aaron, As the page title says: it is entirely done in VBA. In fact, it may even be possible to update the code so it works on a worksheet as well.
Comment by: GotQuestion (6-1-2015 15:52:54) deeplink to this comment
After looking through the code, I notice you are not storing node references (parent/child) as pointers. Doesn't storing them as objects create the potential for memory leaks? (See Rob Bruce's comment on this page: http://dailydoseofexcel.com/archives/2007/12/28/terminating-dependent-classes/ )
Comment by: Jan Karel Pieterse (6-1-2015 16:10:49) deeplink to this comment
Hi GotQuestion, We spent quite some time figuring out how to oproperly terminate all objects and in fact (in the demo) even have some checking code in place that keeps score of all objects (as long as you are in debug mode). The tool currently does the right stuff: all objects are properly terminated at the right moment as far as we know.
Comment by: Paul Sondervan (21-1-2015 20:30:31) deeplink to this comment
I have a dropdown menu with recent choices. When the user selects one of the recent items, I would like to set the corresponding menu choice in the Treeview. Is it possible to set this menu choice by using vba?
Comment by: Jan Karel Pieterse (22-1-2015 07:01:46) deeplink to this comment
Hi Paul, You can set the active node by using code similar to:
Comment by: Hesham (22-1-2015 12:25:16) deeplink to this comment
I added the subform to mail form the displays detail infor on students, but How to use the tree control to filter the main form?
Comment by: Lucio (23-2-2015 17:20:03) deeplink to this comment
Ciao Ho visto il Treeview e mi interessa nella versione full.. quanto costa?
Comment by: Jan Karel Pieterse (23-2-2015 17:35:44) deeplink to this comment
Hi Lucio, Can you please explain a little bit more? The download already contains the full version? NB: I'm afraid I do not speak Italian :-)
Comment by: Jerzy Orysiak (24-2-2015 19:52:12) deeplink to this comment
Hi, Great piece of code, congratulations. It is a pity "drag and drop" functionality is not included (anyway I'll try to add this). I'd be grateful for hints how to handle context menu (declaring in standard module public variable containing ObjPtr? to clsTreeview object?) What is a simplest approach? Also, I've spotted weird behaviour of checkboxes (Build number 25). Is it the last version? 1) On preloaded nodes checking node causes checking this node and all subtree "hanging" on that node and "Third state" for all direct ancestors (not sure why) and it is OK. 2) After adding some nodes, checks made on new ones goes crazy! All added ancestors of checked node are checked as well which I suppouse is not correct. 3) "checks" without icons appears as "a" letter. The second question is: is there any property like "that node cannot be used as a parent"? And last at this moment. I havn't found public types for export/import data for populating treeview. One for treeview object (1 data record only) and array of variables containg available nodes data (key, parent?, text, tag, font , Path?, etc). More of that it also tells which properties might be manipulated (background colors for instance should be used with care since they show for instance "selected node" , "cut subtrees") WITHOUT digging deeply inside your code. brgrds
Comment by: Peter Thornton (25-2-2015 13:11:37) deeplink to this comment
Hi Jerzy, lots of points! Drag & Drop is not builtin, we had to stop somewhere but it’s possible to implement. There are two approaches: you can work with ‘BeforeDragOver’ and ‘BeforeDropOrPaste’ events or “simulate” D&D which is more complicated but can use custom icons. The hard work is already included in the treeview, your D&D will end up calling the builtin Copy or Move methods. A right click context menu is more straightforward. Trap node’s MouseDown (or Up) with Button=2 and “Raise” it as an Event similar to the other events. Validate as required then create a normal CommandBars popup menu (don’t forget to destroy it). Buttons in the popup can 'OnAction' ordinary macros in a normal module, you may need to temporarily pass node objects to the normal module before showing the popup but be sure to destroy any additional treeview/node references. I don’t follow what you are saying in 1) and 2), but if it’s about TriState checkboxes some changes to the logic when nodes are added and events for ‘indirectly’ changed checkboxes will be included in the next version (026). Re 3) If you are not using checkbox icons the checkbox is simulated with Label controls to look like a small square, and with the Marlett font the “a” should appear as a tick. Is Marlett is installed in your system? To retrieve data see the code in the demo behind the button “Dump Data”, cmdGetData_Click, GetData1 and GetData2. These are only examples, adapt to selectively retrieve the data you want. Indeed colours are temporarily changed with Selection, Move & Copy, but the default or your applied colours should be automatically restored, ie after changing the selection or done with Move or Copy. Play with the 'Icons' demo and in particular the alternate shaded nodes.
Comment by: Jerzy Orysiak (26-2-2015 00:57:12) deeplink to this comment
Hi Peter, Many thanks for a prompt reply. 1) I do have Marlett installed and when I inserted a label to userform with caption "a" with Marlott font I got correct "tick". Also, I traced the line 1017 (clsNode)
Comment by: Peter Thornton (26-2-2015 12:20:23) deeplink to this comment
I replicate your "a" in Excel 2000. Strange, I'm sure we tested in 2000/2003 but it seems when the Label is added at runtime, although Marlett is correctly applied the default font continues to display, hence "a" not a tick. Normally this VBA Treeview should work in all Excel & Word versions since 2000 and Mac 2011 (though Access only since 2003). However for your 2003 I suggest you use checkbox icons, see the demo code and documentation. There are two sets of checkbox images bundled in the demo or you can find your own. Yes there is a difference in the way the checkboxes work in the two demos, with TriState and not with TriState.
Comment by: Jerzy Orysiak (26-2-2015 18:12:50) deeplink to this comment
I found the reason for "a" tick. Setting font to "Marlett" is not enough. lines 1626, 2053(about)
Comment by: Peter Thornton (27-2-2015 11:04:15) deeplink to this comment
Thanks Jerzy. Font.Charset = SYMBOL_CHARSET or 2 also fixes the Mareltt tick for me in old Office versions. Not sure why it's not necessary in 2007 and newer, at least not for me and I assume not for most users or it would have been flagged here before!
Comment by: Thomas magdahl (6-3-2015 19:46:47) deeplink to this comment
Hi guys, Nice work and I am definitely using it. I am checking the AfterLabelEdit subrutine, simply overriding the edited text. The routine is at the end of this text. My problem is that on the node "Hello" is diplayed but the node Caption keeps the value that was on NewString before the override (modified version of what was before editing). In your demo code 456 is replaced with 789 and both the visual text and the Caption are the same. Any clue of what I am doing wrong? Regards Thomas Magdahl
Comment by: Thomas Magdahl (6-3-2015 22:06:50) deeplink to this comment
Hi, As a matter of fact the untouched demo is now behaving in my machine with the same problem. In my previous posting I siad the problem was only on my code but not on the demo and now I realize they both have this same apparent inconsitency. Not sure if it is an erratic behaviour or I am going nuts! When you edit a Node in the demo and add 456 at the end, it gets replaced by 789 but the text on the node in the tree window does not match with the text displayed in the labInfo label. The problem will not disapear with a Refresh or by selecting different nodes and returning to the appatently "offending" one. Not sure if this is of any help! Thomas
Comment by: Peter Thornton (7-3-2015 13:09:02) deeplink to this comment
Hi Thomas, If I follow you are saying everything works except the labInfo does not update with thew new Edit. That's because in the demo labInfo is only updated in the node Click event. Next time the node is activated labInfo will update with the new edit. One of the uses of the AfterLabelEdit event, apart from "validating" an edit along the lines of the 123/456 examples, is to trap a user edit and record for your own purposes as required. Add the following to the AfterLabelEdit in the demo
Comment by: Thomas Magdahl (9-3-2015 13:38:38) deeplink to this comment
Hi Peter, Sorry that I comming back with this same issue, but I think there are two posibillities: 1) I still do not get something that might be abvious 2) Your code behaves in my machine in a different way From your explanation, after I edit a label and the AfterLabelEdit routine makes a change to the string (for example changes 456 to 789) and I keep on traversing and editing other labels/nodes, when I return or select the label/node whose string was chenged by AfterLabelEdit, I should see the same string in the display and in the Caption property. That is not happening in my machine. A more detailed explanation follows. Regards Thomas
Comment by: Thomas Magdahl (9-3-2015 13:39:21) deeplink to this comment
Hi Peter, I just unpacked the file again from the original downloaded ZIP file, enabeled macros in EXCEL and run both demos. I double click on the first non root node 1.A and add at the end 456 and press return. The text visualized in the tree node, displayed in the frame (just after pressing return) effectively displays 1.A789, and labInfo displays 1.A456. Based on your explanation this is explained because the labInfo label is updated with the Click routine before 456 was changed to 789. So I understand that if I move to another node and regardles if I did an edit or not in that other node (for example 2.B), when I return to 1.A789, labInfo will display 1.A789 too. That is not happening on my machine. I still get 1.A456. If after moving through a bunch of the remaining nodes I stop the VBA code and inspect mcTree, and look into mcTree->Nodes->Item2->Caption, 1.A456 is stored (the screen is still showing 1.A789 as expected). If I look at mcTree->Nodes->Item2->Control->_Value, 1.A789 is stored. Not sure if _Value is of any relevance, but at some place in the structure 1.A789 is stored and displayed correctly on screen, but the Caption property is still showing 1.A456 which in my oppinion not correct, because AfterLabelEdit made the change. I made the decription as thorough as possible so you might better diagnose if this is case 1) or 2). I hope I am not making a fool of myself :-) Regards Thomas
Comment by: Peter Thornton (9-3-2015 15:32:06) deeplink to this comment
Hi Thomas, You are right! I don't know how that crept it, it's not in the version I'm working with or most others. Please edit clsNode as indicated
Comment by: Thomas Magdahl (9-3-2015 16:59:57) deeplink to this comment
Hi Peter, Worked fine! Thanks for your help! Thomas
Comment by: Thomas Magdahl (11-3-2015 17:21:36) deeplink to this comment
Hi Peter, Besides the node editing capabilities of the TreeView, I am providing in my application a form to set additional node info. So when the user selects a node and depresses an EDIT button, a form is opened where he can not only change/edit what will be displayed in the Caption of the node (active node), but other info stored in an array (indexed by the Tag property). After the unload of that form I set the new Caption, overriding the old value and do a tree refresh. My problem is that when no horizontal scrollbar is present, because all nodes have short texts, and the new text of the edited node is very long, I am not able to get the horizontal scroll bar to appear. You can duplicate this behavior in your demo doing this two modifications. 1) In the InitializeDemo1 routine, shorten the text of this node so that when initializing the TreeView, only a vertical scrollbar is present.
Comment by: Peter Thornton (11-3-2015 21:00:15) deeplink to this comment
Hi Thomas, A method to fully update scrollbars has already been developed for the next version 026. For your purposes after changing the caption you will simply be able to call
Comment by: Frank Russell (19-3-2015 17:18:21) deeplink to this comment
I think that what you have done is great for the MS Access user/developer. There appears to be only one thing missing that I would greatly appreciate if you could incorporate and that is the ability to Drag and Drop between nodes and another treeview. I have tried, unsuccessfully to develop my own drag and drop routines. Thanks again for a great product. Frank
Comment by: Jan Karel Pieterse (19-3-2015 20:15:11) deeplink to this comment
Hi Frank, Thanks! There is some plumbing in there already to handle drag and drop if I recall correctly. Peter Thornton has been investing quite some time in that feature, but I'm not entirely sure of the current status. Watch this place, as Peter does visit here now and then to post comments too.
Comment by: tino (20-3-2015 03:39:47) deeplink to this comment
I can't open any of the files with Microsoft Office 2003. Excel 2003, gives... invalid file format Word 2003.. nothing happens when you click the button. I think it gets converted to a doc with no macros. The only one I could open is the Access file, but it gives a code error when the demo starts to run upon opening the file. I can see the code though in here.
Comment by: Jan Karel Pieterse (20-3-2015 13:42:16) deeplink to this comment
Hi Tino, Try reinstalling Office.
Comment by: Peter Thornton (23-3-2015 11:07:38) deeplink to this comment
@Frank, As Jan Karel mentioned quite a lot of work has been done with Drag & Drop. It's not included in the demo as some of the code is external to the intrinsic treeview and how best to adapt depends of the individual project. We'd be happy to help implement D&D privately if that's an option for you. Alternatively, to point you in the right direction, there are two approaches: using the built-in D&D MSForms events or "simulating", as Jan Karel also said some of the "plumbing" is included. @tino, I've just double checked and demo v026 works fine for me in Excel 2003 and 2000, albeit after saving the file as an .xls. There can be problems with the Access 2003 if the file was not compiled in 2003, eg if used in 2007+ then attempted in 2003. I wonder if the Dec-2014 Windows update that affects MSForms controls has hit you. With Office apps closed try deleting or renaming all MSForms.exd files in your system, restart windows.
Comment by: Jerzy Orysiak (22-4-2015 16:46:34) deeplink to this comment
Hi, good to see the next version... I work on excel 2003. I've spotted the problem using combo boxes in documantation worksheets (don't work). It's caused by the line 28 (modDocNav)
Comment by: Jerzy Orysiak (23-4-2015 03:22:29) deeplink to this comment
Hi, As I've noticed before documentation worksheets clsTreeview and clsNode with comboboxes do not work for excel 2003. The similar situation is as far as sort "commandbutton" are concerned. RefersToRange method doesn't work with name defined as formulas. I had to change 2lines of code of modDocNav (line28)
Comment by: Peter Thornton (25-4-2015 12:33:03) deeplink to this comment
Hi Jerzy, Set rng = .Names.RefersToRange(name) should work in all versions since Excel 97, I've just double checked the demo file in 2000 and 2003, all working fine. I'm not sure why you need the Evaluate workaround in your system, have you tried the example in VBA help for RefersToRange. If there really does appear to be an issue contact me privately. D&D can be configured to work across multiple treeviews and you can use the built-in Move or Copy methods, if indeed the objective is to Cut or Copy a node and its children. The intrinsic class objects are the same in both treeviews so "incompatibility" doesn't apply. At least not as far as the treeview is concerned though of course most likely you'd need appropriate validation to ensure a node can only be dropped on a suitable target. The logic will be specific to your project. Without knowing quite a lot about your project it's hard to give code examples, there are different approaches and unfortunately not straightforward! For a right click context you can use the Office Commandbars popup which does not require any APIs, plenty of examples out there. You can also create a Windows popup which requires eight APIs including CreatePopupMenu, look at CommandBars!
Comment by: Jerzy Orysiak (26-4-2015 17:51:31) deeplink to this comment
Hi Peter, 1) Set rng = .Names.RefersToRange(name)works fine but only when name is defined as range directly, i.e. not like in this case formula "=OFFSET(...)". This is not a problem for me and I think it's not worth to examine that further. 2) D&D Thanks for tips. The main reason I need multitreeview D&D is to create "commandbar manager" which consists of 3 treeviews: i) target treeview - for keeping current application user commandbars, references and basic call function information (short help) ii) source treeview - excel "Worksheet Menu Bar" commandbar iii) source/target my "universal" fully tested functions (placed in commandbars) from other excel applications 3) Right click context menu. It's not a problem to create popup commandbar. Problem is: how to avoid public functions/variables. Regards Jerzy
Comment by: Peter Thornton (27-4-2015 19:33:14) deeplink to this comment
1) RefersToRange should also work in all versions since '97 with a Dynamic name. 2) Sounds like an interesting project! 3) You can avoid public functions in normal modules by prefixing the functions and variables with Private. There's also Option Private Module. Alternatively put everything relating to the context menu in a Class, including object variables and action code. Instead of OnAction macros trap respective button click events with WithEvents.
Comment by: Jerzy Orysiak (29-4-2015 01:32:34) deeplink to this comment
Hi, 1) I didn't express myself correctly what I meant "public". Commanbarcontrol .OnAction requires PUBLIC function/sub placed in standard module. I want to avoid this. I want to take all actions in class module either in userform or another "wrapping" withevents class. What I do NOT KNOW is how to trap popup menu choice (item selected) whithin class ... 2) I want to keep tree in a recordset (sheet) (RecID, ParentID, field3, field2...) of simple data type (images represented by keys). I need to update image frame each session with userform (pictures can be added/removed. It requires VB project Designer action (I don't know any other solution). Once I execute designer's images updating, I can also save current treevieew settings like "showlines", "Show expanders" in for instance in userform "tag". Is this acceptable approach?
Comment by: Peter Thornton (29-4-2015 11:38:30) deeplink to this comment
1) The OnAction macro in a normal module can be Private. Or, in a class module when adding the buttons assign then to 'WithEvents' CommandBarButton object variables, which will expose trappable Click events. 2) Icon images do not need to be hard coded, and no need to use the Designer object. Although the simple way is to pass the frame of images, you can also create and pass a 'collection' of images. The collection could include a combination of 'picture' handles from existing image controls, from file with LoadPicture, or created in various other ways, eg with APIs. Set mcTree.Images = colIcons See "Collection Method" and "Frame Method" in the demo form. New in v026 are Treeview.ImageAdd and Node.ImageUpate methods. You can now also add a new images to the treeview's internal collection and update or change node icons while the treeview is running. The new images could be sourced from anywhere with a variety of methods.
Comment by: Jerzy Orysiak (30-4-2015 19:01:07) deeplink to this comment
1) Thanks as lot!. It's exactly what I want! (although I've substituted WithEvents commandbarbutton declaration for each commandbarbutton in shortcut menu with a collection of objects - class designed to trap commandbarbutton click event and returning to the parent userform the "clicked" commandbarbutton caption) 2) That solution is fine until I have to transfer workbook to another PC... To avoid transfering icon files along with workbook I need one way or another to "save" them in a file. The only way I know is to keep them "hard way" is: either using Designer or keeping them in Shapes collection. Also, I have developed fine "Iconpicker" from office icons and spent quite a lot of time to create db table with fields(faceid, cathegory, tag) for few hundreds of icons out of more then 7000. Sooner or later I will have to migrate to the newer windows and office. I don't know whether next versions of office keep icons faceid the same way and I am afraid of loosing everything.
Comment by: Peter Thornton (4-5-2015 09:29:54) deeplink to this comment
You can maintain and distribute your icon images any way that's convenient. That might be in a say a separate folder of image files and get with LoadPicture, probably better with a large number. With a smaller number you could store them in a workbook in ActiveX Image controls on a sheet, or in say a second userform. You can also store as hex "blobs" in cells (text) though it requires a fair amount of API stuff to convert them. No point to store as Shapes, not simple to convert to StdPicture objects, use aX images.
Comment by: Jerzy Orysiak (6-5-2015 14:59:47) deeplink to this comment
Hi, 1) I've tried to use standalone Move method (for changing order of nodes having the same parent in this case) with Node.key as a vAfter /vBefore parameter. It seems it doesn't work that way. It does work when vBefore /vAfter is set to childnodes collection index of destination parent node, available through not included in documentation ChildIndex function. Am I missing something? Move sub can be easily modified but I don't want to make any changes in your code. 2) There is no "public" way to change order of root nodes in multiroot treeview. There is no "public" way to add new root before/after (if I have to do it: rewriting roonodes collection with desired order is a solution?) 3) There is no "public" way to cut node and paste as a new root - child of "RootHolder" (?)
Comment by: Peter Thornton (7-5-2015 13:22:11) deeplink to this comment
Hi Jerzy, First, adding and "positioning" a new node can be done with the tree.NodeAdd method, eg
Comment by: Juan (7-5-2015 16:43:30) deeplink to this comment
Hi! Your class is great! Thanks for share it!! How can i get the caption of each node? i've tried using for i=1 to mcTree.nodes.count var=mcTree.nodes.item(i) next i but it doesn't works...... Thanks a lot!!!!
Comment by: Jan Karel Pieterse (7-5-2015 17:47:21) deeplink to this comment
Hi Juan, Something like:
Comment by: jerz (8-5-2015 00:51:38) deeplink to this comment
Hi Peter, 1) I was wrong. Move method works fine with root nodes (however, looses highlight with shortcuts or command button and activenode is almost invisible). Anyway, I have added DropHighlight sub to my auxiliary standard module . 2) I am unable to make NodeAdd work with root nodes. First:
Comment by: Peter Thornton (9-5-2015 12:20:50) deeplink to this comment
Hi Jerzy, I don't follow all of what you're doing there but it sounds like you've got it all working. Indeed the activenode's highlight formats change as the treecontrol gains or looses focus if you included the frame's enter & exit events in the main form (see the demo) to make it consistent with Window's behaviour, it's a feature! However you can easily adapt or override the these highlight changes.
Comment by: Jerzy Orysiak (12-5-2015 13:47:02) deeplink to this comment
Hi Peter, below is the part of cmdAddSibling_Click() of your demo:
Comment by: Peter Thornton (13-5-2015 13:06:47) deeplink to this comment
Hi Jerzy, If I follow you want to add a new root node and at the same time position it before or after some other root node. To use the NodeAdd method for this the clsTreeView code will need changing: In Function NodeAdd comment and add these lines respectively
Comment by: Jerzy Orysiak (14-5-2015 01:15:55) deeplink to this comment
Hi Peter, I have changed mentioned line, however the first line after declaration in NodeAdd should be changed as well (at least I think so). It is not neccesary but if a tree is empty (ActiveNode is Nothing) it prevents from error and/or additional condition checking. Is:
Comment by: Peter Thornton (14-5-2015 20:54:01) deeplink to this comment
Hi Jerzy, I don't think we ever considered .NodeAdd for adding root nodes. Although it works with the change I mentioned for subsequent root nodes, I think at least for the first node it can be added with .AddRoot, and then never necessary to check if mcolNodes Is Nothing with all other nodes, but no problem to do that. It looks like you are giving the treeview a very thorough work out! Indeed please send your remarks when done.
Comment by: Chris (15-5-2015 13:39:13) deeplink to this comment
I'm wondering if you have a vba listview alternative that work both on 32 bit and 64 bit office?
Comment by: Edgar (1-6-2015 11:36:09) deeplink to this comment
Hi JKP, Will this treeview also work in Access 2013 32/64? Thanks, Edgar
Comment by: Jan Karel Pieterse (1-6-2015 12:34:26) deeplink to this comment
Hi Edgar, Yes it should work on Access 2013 as well. If you could download the Access sample and try it on Access 2013, that'd be great!
Comment by: Edgar (1-6-2015 13:28:55) deeplink to this comment
Hi JKP, Tested on Access 2013 64 bit... Works like a charm... Many thanks! Edgar
Comment by: Chris (2-6-2015 11:55:15) deeplink to this comment
Steve wrote a comment back on (2/21/2014 3:49:20 AM) saying "If the user first clicks on a node on the Treeview in Form A, the fields on Form B become locked. More accurately, the fields on Form B can receive focus, but they cannot be updated." Peter Thornton came back on the (2/24/2014 3:32:00 PM) and replied. Well, I'm experiencing a similar problem where the focus seems to be stuck on the Treeview. I can click on an unbound field on Form B (the cursor goes there) but its acting like the field is locked. Was there any further communication with Steve? Did he get his problem resolved?
Comment by: Eduardo (3-6-2015 20:05:41) deeplink to this comment
Hi. I'm really having a lot of trouble in populating the tree Maybe you can make an entire instruction or tutorial: I need to create this nods por example: 1. Item 1 2. Item 2 3. Item 3 Then I need to add the followings: 1.1. Item 11 1.2. Item 12 2.1. Item 21 3.1. Item 31 And then: 2.1.1 Item 211 ................................. So the tree must be: 1. Item 1 1.1. Item 11 1.2. Item 12 2. Item 2 2.1. Item 21 2.1.1 Item 211 3. Item 3 3.1. Item 31 How can i locate a specific node (index) so i can add a new child into it? Thanks
Comment by: Chris (3-6-2015 22:57:42) deeplink to this comment
Just as a follow-up answer to my question about focus sticking on the Treeview and for anyone else that might experience this behaviour. I have resolved the problem by simply setting "Tab Stop = No" on the Treeview subform. Everything now works! Interestingly, the same behaviour can be replicated with the demo project in Access 2000. Just place an unbound text control on frmDemo, press "Demo Treeview", click on any node on the Tree, click in the unbound text control and try and type anything – unbound text control acts if its locked (even though it's not). The same example running under Access 2013 with "Tab Stop = Yes or No" works fine. I haven't tested it under other Access versions so it could well be an Access 2000 special. I know Access 2000 - its still being used! Just thought I'd pass on my findings...
Comment by: Jan Karel Pieterse (4-6-2015 07:29:00) deeplink to this comment
Hi Eduardo, You can use this function traverse the nodes and check for their key (if you gave them one) or their Caption:
Comment by: Peter Thornton (4-6-2015 12:21:21) deeplink to this comment
Hi Chris, I couldn't replicate the problem in Access Steve and now you described. But I wonder if your "Tab Stop = No" workaround prevents key tab into the treeview? Ideally we'd want to prevent what you describe and allow tab in and out, and 'notify' the treeview so it can change the active highlight as I described (2/24/2014) You say it works in Access 2000, we didn't know that! The earliest we thought was 2003, and only if compiled in 2003 but in a system without a later version. It's why we're slow to update the Access demo with the latest version without such a system to hand. A belated response to your Q (5/15/2015) about do we have a listview, as the saying goes - watch this space!
Comment by: Chris (15-6-2015 07:29:24) deeplink to this comment
Hi Peter, Yes, that is the downside of setting “TAB Stop = No”, but I can live with that given the alternative is that every control on the form is locked! Apart from that little glitch it works perfectly fine under Access 2000 and Access 2000 runtime :) Thanks for your response re: listview, say something was in the works... any chance of an idea of what a time frame could be? 2015, 2016? ;)
Comment by: Eric (15-6-2015 17:00:32) deeplink to this comment
Hiya, I've got an odd issue that crops up on form resize, specifically if the use minimizes the form and the form's detail height or width is updated. On detail size update the treeview will terminate itself. The other sub form doesn't appear to be affected, and if I disable the detail update the treeview doesn't terminate itself. So, the logical thing would be to disable the detail update on (max|min)imize, however that doesn't appear to be possible. I thought this might be an issue with the treeview subform size dipping down to 0, but it never reaches that point. None of the form elements get below 300 twips. The treeview also doesn't terminate until the detail size update event. The code:
Comment by: Peter Thornton (18-6-2015 10:04:40) deeplink to this comment
@Chris, Pleased if not surprised to hear it works in Access 2000! Could you clarify the tab issue you mentioned is only in 2000, or if not which versions? The listview alternative is still coming soon, as it has been for a little while! If you drop me a line privately (see a module header) I'll keep you updated. @Eric, I couldn't replicate your resize problem in our demo. You say the "treeview terminates itself" - is that getting done 'nicely' because something's gone wrong? If not sure put a break in clsTreeview.TerminateTree and see what called it. I take it your Form_Resize() is in the main form, wonder if that conflicts with the Sub ResizeUF() in the subTreeView, try putting a break in it and step through. Another thing you could try: in Project Properties / Conditional Comp' Arg's DebugMode = 1 In the VBE, tools/options/general - break on all errors If there's any error in the class modules when you resize the code should break, step through and 'Resume' to the error line. Be sure to remove or reset DebugMode = 0 before distributing. Report back if not fixed.
Comment by: Chris (19-6-2015 07:20:08) deeplink to this comment
Hi Peter, Just confirming its only happening in Access 2000. It's a strange one but it can be replicated using your demo access project under Access 2000 easily. It would be great to know what the problem is but I can understand if you can't lay your hands on a copy off Access 2000 anymore! I've also just emailed you privately regarding getting updates from you on the listview alternative when there is an update :) Thanks for all your responses and indeed getting back to me – your great work is appreciated!!
Comment by: Andrew Glasgow (22-6-2015 16:43:32) deeplink to this comment
How can I download the treeview control. There's no link associated with "Download". Do I have to donate first?
Comment by: Jan Karel Pieterse (22-6-2015 17:01:36) deeplink to this comment
Hi Andrew, "Download" is just a header, the download links are below that heading. There are three of them: One for Excel, one for Word and one for Access.
Comment by: Eliton Elias (23-6-2015 19:21:27) deeplink to this comment
Hi, I'm struggling to create a macro to populate the treeview with data from the spreadsheet. Thanks in advance
Comment by: Peter Thornton (24-6-2015 18:09:39) deeplink to this comment
Hi Eliton, Without knowing anything about how your data is arranged it's hard to suggest a macro. There are several approaches.
Comment by: Niels de Nies (29-6-2015 13:44:44) deeplink to this comment
Jan Karel, Ik heb -als prototype- in Access een database gebouwd waarmee een product breakdown van een te ontwerpen installatie gemaakt kan worden. Jullie Treeview werkt daarin prachtig. Nu wordt mij gevraagd het prototype op te schalen naar SQL. Ik zou Access kunnen handhaven als client, maar mijn klant heeft dat liever niet. Mijn vraag is: kan jullie Treeview ook in een VB omgeving draaien. De code zelf lijkt me niet zo'n probleem, maar ik zou zelf niet weten hoe ik met het ufTreeview scherm uit de voeten moet. Kan je me adviseren? Vriendelijke groet, Niels de Nies
Comment by: Jan Karel Pieterse (29-6-2015 14:43:18) deeplink to this comment
Hallo Niels, VB heeft een eigen treeview die je kunt gebruiken voor zover ik weet. Ik neem overigens aan VB.NET?
Comment by: Niels de Nies (29-6-2015 15:14:24) deeplink to this comment
Klopt: VB.NET. Ik had er nog niet naar gezocht. Op is het zich prettig dat er een treeview in VB.NET is, maar ik ben erg tevreden met jullie Treeview. Die ken ik nu en doet alles waar de gebruiker behoefte aan heeft. Maar goed, ik snap dat het niet opportuun is om naast de standaard van .NET nog een andere treeview te gaan maken. Dank je wel. En nogmaals, voor Access is die van jullie TOP.
Comment by: Jonathan Brown (2-8-2015 08:57:17) deeplink to this comment
Is there a way to programmatically change the state of a node without triggering the nodecheck event again? This may not be necessary as I really just want the treeview to behave the way you have it in the tristate mode. Specifically when I click a parent node, all of it's childnodes update accordingly. But then I want it to execute some code after all the nodes are finished updating. I've found that when I put code into the mctree_nodecheck event it repeats that code for every child node. Following the debug I can see that it raises the nodecheck event each time a checkbox changes its state. I've tried using application.enableevents = false to prevent it from retriggering the nodecheck event when a checkbox's state changes but it doesn't seem to work the way I want it to. Would I have to create something like a new node_aftercheck(cNode as clsNode) event that'll run after the nodecheck event? Is that possible?
Comment by: Jan Karel Pieterse (3-8-2015 16:23:54) deeplink to this comment
Hi Jonathan, That is the beauty of this control: you can massage it to do exactly what you want. One way to have this work is by adding a public boolean to the clsTreeView class (at the top): Public NoEvents As Boolean Then in your userform you can temporarily switch off events by setting:
Comment by: Ken (6-8-2015 03:41:14) deeplink to this comment
Sorry to be dense, but I am not seeing where I can locate some array or collection or similar from which I can extract a list of checked boxes? For example, I checked 4.0003 and 4.0005, which also selects the parent node...How can I iterate through and use these 3 checked items in my next operation? Thanks!
Comment by: Jan Karel Pieterse (6-8-2015 11:04:45) deeplink to this comment
Hi Ken, You would traverse the nodes of the tree like so:
Comment by: Anthony Powers (17-8-2015 15:28:59) deeplink to this comment
I have studied your example for many hours. I am amazed at your work! May I ask a question concerning the Access database? If I add more columns to a table, just as an example, “Gender” to tblStudents, how can I change the forecolor of student names (nodes) based on their gender criteria, and without this criteria being in the treeview. Or if I add a true/false column to tblClasses, and do not want this to show up in the treeview, but want to change the forecore of “Class” nodes based on true/false criteria how can I do this? I’ve tried everything but can’t figure it out. Thank you very much for your assistance. Kind regards, Anthony
Comment by: Jan Karel Pieterse (17-8-2015 17:30:33) deeplink to this comment
Hi Anthony, You would set the ForeColor property of the node in question when you are loading the nodes into the tree. If you change any formatting of a node once the tree is already displaying, you need to refresh the tree:
Comment by: Anthony Powers (18-8-2015 00:29:35) deeplink to this comment
Hi Jan, Thank you for your response. Much appreciated! My problem is with the criteria. How can I refer to information in the tables? For example: If Gender is Male then node text changes color. Can you provide a simple example? Thank you for your support and kindness, Anthony
Comment by: Jan Karel Pieterse (18-8-2015 10:05:17) deeplink to this comment
Hi Anthony, You would read the information of tblStudents and extract the gender information belonging to each node and color the nodes accordingly. Some air code:
Comment by: Anthony Powers (18-8-2015 10:31:41) deeplink to this comment
Hi Jan, This is what I'm looking for. Thank you so very much! You are wonderful! Anthony
Comment by: Josh Feuerstein (18-8-2015 19:53:10) deeplink to this comment
I've been emailing with the devs about some questions but I thought this one might get a more generally useful answer to all: If my control is in TriState mode, and I check a box, is there any way to detect which checkbox was *actually* clicked by the user? The
Comment by: Peter Thornton (19-8-2015 13:45:14) deeplink to this comment
Events to trap dependent TriState changes were added in v026, actually on request (see the Versions sheet in the Excel demo). In typical scenarios which particular checkbox triggered the change is not important, but knowing all changes that have occured as a result is. The current arrangement can also help the developer, eg after programatically changing just one checkbox, all parents to the root and child checkboxes that may have changed as a result are returned in the chain of events. In turn a DB can be simply and fully updated in real time if required. Without that the developer would need to calculate the logic (not easy) or compare against record of previous checked nodes in the the entire treeview. If the events are counter productive for your purposes (I'd be curious to know why) you could customize the treeview to only trigger the event for a single manually changed checkbox. Or, add an additional flag in the event to indicate it was the one that triggered the chain (possible but complicated). Alternatively in your own form record the time of the event but only if the time is more than say 0.2sec since the previous change; if more than 0.2sec assume the event is teh one clicked by the user as it will be the first event in the chain, otherwise it's dependent event. A third approach would be to use v025!
Comment by: Josh Feuerstein (19-8-2015 17:38:33) deeplink to this comment
The simple reason why is that the ultimate state is good, but I might want to provide some feedback to the user, or log which node was clicked for debugging purposes, etc.
Comment by: Peter Thornton (20-8-2015 18:33:17) deeplink to this comment
Typically (but see below) the node receiving user's checkbox change is the ActiveNode. So you can determine if a checkbox was changed directly by the user or due to its TriState dependency
Comment by: McPegasus (2-9-2015 08:57:02) deeplink to this comment
Hello How can I change the font size? Thank you.
Comment by: Jan Karel Pieterse (2-9-2015 10:22:24) deeplink to this comment
Hi McPegasus, The Excel demo contains a Font size control, check out the control's event to find out how to change the font size.
Comment by: McPegasus (2-9-2015 12:06:59) deeplink to this comment
Fantastic Jan! Thank you very much. I am looking for a substitute to the TreeView and now, I prefer your system ;). Just missing OLEDragDrop, OLECompleteDrag, etc.), but we will know how to live without it. Built-in example: Treeview-25 compiled in 2003 Private Sub InitializeDemo1() ... With mcTree Call .NodesClear ' if reusing an exisiting treeclass .AppName = AppName .TreeControl.Font.Size = 16 McPegasus Valencia Spain
Comment by: John Bingham (7-9-2015 05:35:39) deeplink to this comment
Hi, Nice work! In design view the tree sub-form insists on being at the front; This can be very inconvenient when the tree is located on a tab control, and many other controls are actually sharing the same piece of screen real-estate. Is there any way to control/prevent this? Thanks John
Comment by: Peter Thornton (7-9-2015 13:22:12) deeplink to this comment
Hi John, In a quick look it seems it's not possible to prevent the subForm from being at the front in design view. As a workaround for use in design, try this in a normal module and run from the VBE
Comment by: Tom (11-9-2015 22:25:11) deeplink to this comment
Great job guys! I was looking for a small treeview example due to the 32/64 'broken' dll stuff. This is not a small example, this is great work! Chapeau! Tom
Comment by: David Reid (18-9-2015 02:26:39) deeplink to this comment
Could you please help me to understand why your Access Treeview sample is not loading properly? I'm getting the error message 438 (Ojbect doesn't support this property or method) at the line: lngIndex = ctl.TabIndex in the subTreeView module. That problem then cascades into other errors. I just downloaded the file a couple of minutes ago and my best guess is that a reference is not set--but I don't see that any are missing. Thank you.
Comment by: Peter Thornton (18-9-2015 13:16:36) deeplink to this comment
David, Strange the code breaks there as that line is under 'On Error Resume Next'. In your VBE Tools/Options/General do you have 'Break on all errors' checked? By default that should be 'Break on unhandled errors'.
Comment by: David Reid (18-9-2015 18:16:22) deeplink to this comment
You're right, Peter. All is good after I set the default back to 'Break on Unhandled Errors". Thank you.
Comment by: H.KILIÇ (4-10-2015 04:02:57) deeplink to this comment
JKP you are real web-hero. I exported your codes to AutoDesk Inventor 15, and willing to try making a VBA TreeList Control; something like treelist in order to specially duplicate my large project's part&assm files; on Win8-64, need some suggestions. Thanks for the treeview codes. Teşekkürler.
Comment by: Simon (14-10-2015 16:48:56) deeplink to this comment
In the "Instructions" tab of the Excel files it has the following instruction: Add a reference for the Treeview Frame container
Comment by: Thomas Koester (15-10-2015 13:55:08) deeplink to this comment
Hello, I would like to scroll the treeview with the mouse wheel (I am on Access). Do you think that it would be possible? How would you do it? It would be sufficient if you could get me started with some hooks and hints. I am already fairly familiar with your code due to some other custom patches. Thanks Thomas
Comment by: Jan Karel Pieterse (15-10-2015 14:49:22) deeplink to this comment
Hi Thomas, Have a look at this thread on MrExcel: http://www.ozgrid.com/forum/showthread.php?t=184493
Comment by: Thomas Koester (19-10-2015 08:11:32) deeplink to this comment
Hi Jan, thanks a lot. That's what I was looking for. Thomas
Comment by: Jaap van der Sijp (20-10-2015 09:18:39) deeplink to this comment
Tip: If you use icons in the tree view and FullWidth=False, then the icons are placed in the label control. When clicking the label the background colour gets changed (default blue), but the all white pixels in the icon also become blue. Going to FullWidth=True solves that , but may not be what you want. An alternative is to edit the image and change all white pixels (FFFFFF) to "nearly" white (FEFEFE). Then only the text part background will turn blue. this looks a lot cleaner.
Comment by: Peter Thornton (20-10-2015 21:00:06) deeplink to this comment
@Simon, Thanks for flagging the typo in the instructions sheet, the demo has been updated. @Jaap, As you say with fullwidth=true icons appear in their own image controls, but with fullwidth=false icons are embedded in the node label. With fullwidth=false, normally which RGB pixels in the icon adopt label's backcolor (eg when active and given vbHighlight) depend on which, if any, RGB was defined as the mask colour when the image was saved. It seems the demo icons were given a white mask, I'm sure there was a good reason at the time!
Comment by: Paul Katz (24-10-2015 22:27:05) deeplink to this comment
Hi - thank you for developing this control and making it publicly available. It seems that I have a good business need for it. I'm hoping you can answer a couple of questions before I dive in to decided if it's the right control to use. I'm developing an Excel app, and have data which will source the TreeView control in a worksheet. The size of the table is about 7 columns x 4000 rows. The data are sorted left to right (root, lvl1, ..., lvl6). Users will be expected to navigate the tree and drag/drop different node selections to list boxes on my form. (1) Will the control reasonably manage data of this size? (2) Do you have a BuildTreeFromTable() type routine to automatically populate the control from this Excel range (or would I need to program that myself)? Thanks again for your time, Paul
Comment by: Jan Karel Pieterse (25-10-2015 14:24:49) deeplink to this comment
Hi Paul, That number of nodes should not be a problem for the control. But it is easy enough to test by just adding them all to a single rootnode. Then if you're happy with the performance, writing a bit of code that reads your table and builds the tree is fairly straightforward. Perhaps if you expand all comments on this page you'll find some sample code? See: https://jkp-ads.com/articles/treeview.asp?AllComments=True
Comment by: Pavlo (12-11-2015 21:20:02) deeplink to this comment
Does this tree view allows word wrap, if node text is too long? If not, are there any chances to add such feature?
Comment by: Peter Thornton (16-11-2015 12:56:55) deeplink to this comment
Hi Pavlo, Normally treeviews do not display word-wrap or multi-line text, however the horizontal scrollbar should automatically adjust to accommodate the longest text in any node. If your source text includes vbLf and/or vbCrLf (or in Mac vbCr), replace these with "¶ " (Chr(182) + space) before populating, and convert back if reading. Try manually editing a node (EnableLabelEdit=true, then dbl-click or F2 on a node) and entering or pasting some long multi-line text. Then F2 again and have second look. We've done a lot of work to try and handle this nicely!
Comment by: Brain S. (28-11-2015 03:20:07) deeplink to this comment
First, thanks for your great control and alternative to the common controls mess. I'm trying to use your treeview in Access 2013 and it works fine with one exception. I have it inside a tab control page. When I select a node all other controls within the tab page flicker like they're being repainted a few times. It's very distracting. There is no flickering if the treeview is placed outside the tab control. I really need to place the treeview inside the tab control so moving it out isn't an option. Any suggestions on how to eliminate the flicker? Thanks
Comment by: Peter Thornton (2-12-2015 10:32:24) deeplink to this comment
Hi Brian, If you want to send me (or put on a file sharing site) a non sensitive file that replicates what you describe I'll have a look. My address is in the headers.
Comment by: Jeff Silcock (9-12-2015 15:17:50) deeplink to this comment
Been using your TreeView control for a while now and it is brilliant! - thanks very much. Wanting to add a second control, and with this one would like to be able to expand/collapse all the nodes with one click. I presume this must be possible, but would be glad of some steer how to do it please.
Comment by: Jan Karel Pieterse (9-12-2015 16:42:44) deeplink to this comment
Hi Jeff, I think what you need is the ExpandToLevel method of the clsTreeview class, which you can call from within your userform.
Comment by: Joel (9-12-2015 19:51:26) deeplink to this comment
Thank you so much for developing this tool. I only just learned that the ActiveX treeview control is not supported in 64 bit Access when a user of one my applications found that it was no longer working. This is a real help. I can see that you can have multiple instances across multiple forms, however is it possible to have multiple instances in the same form? I typically use a single master form with a tab control. I would like to place the subTreeView form in multiple tabs on the same form but so far haven't been able to.
Comment by: Joel (9-12-2015 21:06:46) deeplink to this comment
Just to follow up from my previous question re multiple treeviews in a tab control... I can get two treeviews to load in the same form, however the click event (mcTree_Click) is only applied to one of the controls. How does the mcTree_Click event become associated with the control? I would like to associate a different click event with the second control. Many thanks if you are able to help.
Comment by: Jan Karel Pieterse (9-12-2015 21:15:25) deeplink to this comment
Hi Joel, You will have to define another variable for the second treeview, e.g. mcTree_2 and add it's click event (and any other events you need) to the userform's code.
Comment by: Joel (9-12-2015 21:46:10) deeplink to this comment
Many thanks Jan. Your solution worked perfectly.
Comment by: Jeff Silcock (10-12-2015 12:29:41) deeplink to this comment
Hello Jan that only works so far. Yes if the tree is closed and you call ExpandToLevel(2), the + gets changed to - on each top level, but you have to then click on the first node which closes that node but leaves all the others expanded. If you then issue a ExpandToLevel(0) all - get changed to +, but the lower levels on node remain visible until you again click the first one, which closes all other but expands that one!! I must be missing something if you can help please
Comment by: Jan Karel Pieterse (10-12-2015 22:59:18) deeplink to this comment
Hi Jeff, Apologies for misguiding you a bit. After the expandtolevel you must force the tree to reload by using
Comment by: Jeff Silcock (10-12-2015 23:08:35) deeplink to this comment
Jan - Thanks, spot on, except that I had to make the BuildRoot routine Public before I could refer to it
Comment by: Jan Karel Pieterse (11-12-2015 12:01:10) deeplink to this comment
Hi Jeff, Sorry about that, I forgot that BuildRoot is private and that I should have advised to use the Refresh method.
Comment by: Joel (5-1-2016 19:14:27) deeplink to this comment
Hi Jan, I just found a small bug. In the Get Previous subroutine in the clsNode class module, the set statement should read: Set Previous= .ChildNodes(I - 1) rather tham Set NextNode= .ChildNodes(I - 1) Many thanks again for developing this.
Comment by: Peter Thornton (6-1-2016 15:47:02) deeplink to this comment
Hi Joel, Indeed there was bug in Get Previous but it was fixed in build-026. If you're using the Access version (hmm we must get around to updating it) you might want to get the Excel download, grab the two class modules and at the top of the clsTreeview change "Excel" to
Comment by: Pete Danes (8-1-2016 00:32:18) deeplink to this comment
This looks insanely useful, but I haven't been able to make use of it. Your demo works beautifully, but I have been unable to import this stuff into another database. I can't import UserForm1, nor do I even see how that happens. All my databases have only three object categories: Microsoft Office Access Class Objects, Modules, and Class Modules. Your demo database also has a Forms category. Where does this come from? How do I get it into my database? My import efforts lead to an error: "Line 2: Property OleObjectBlob in ufTreeView could not be set." logged in the Temp folder. The database also says Out of Memory when I try this. The Forms category exists in Excel and Word, but not normally in Access, and I don't know how to get it there.
Comment by: Jan Karel Pieterse (8-1-2016 12:08:10) deeplink to this comment
Hi Pete, The Demo database has a "How do I?" button on its startup form, which tells you how to implement it in your own database. It includes some pointers on the userform bit too.
Comment by: Konstantin (18-1-2016 20:46:08) deeplink to this comment
Good afternoon. I work with the program SolidWorks. This is a grand CAD software. But in terms of tools for user attributes (user properties), it is extremely unfortunate. And above all, what I was looking for the last 15 years - a TreeView in Excel. Thank you very much. I still have to sort out the tool c. But the main thing for me are opportunities, which are still not there. I understand that you do not have to know what SolidWorks. Therefore, I say that because of user data formed an overview of engineered project, which add and use, and accountant and economist, and the storekeeper and buyer, and the technology and master in production. With your instrument, I can make the right data available to all. And while I do not have to persuade everyone to buy a license. This is a huge deal. And the question. Do you plan to expand your item for ListView element layering data. After all, as I understand it, at the moment TreeView - it's the same hierarchical ListView, but in one column. And, if possible expansion of the TreeView, the second request - possibility of multi-line entry in the cell. This feature is especially painful for custom properties SolidWorks. P.S. I know that there is a Microsoft Hierarchical FlexGrid, but connected to the VBA project it is difficult. You can use the sheet like Excel, but these quickly grow beyond the screen. Additionally, the program itself is maximized. While the basic form to fill in the properties of the screen takes and leaves the visual access to the working model of the project in SolidWorks.
Comment by: Dominik (25-1-2016 21:27:17) deeplink to this comment
Hello, how is it possible in Access to call the
Comment by: Jan Karel Pieterse (26-1-2016 07:28:40) deeplink to this comment
Hi Dominik, You can, if you pass to it the node you "clicked":
Comment by: Paul Charbonnet (27-1-2016 18:58:47) deeplink to this comment
Can your control be compiled into an OCX using VB6. I tried, but it is likely dependent on objects that are part of Excel VBA and not VB6
Comment by: Jan Karel Pieterse (27-1-2016 19:37:08) deeplink to this comment
Hi Paul, There is no point in using our control in VB6 as VB6 is strictly 32 bit and can hence use its own treeview control.
Comment by: Paul Charbonnet (27-1-2016 21:37:18) deeplink to this comment
The MSCOMCTL.OCX that contains the treeview has serious compatibility problems with Windows 8 and above
Comment by: Jan Karel Pieterse (28-1-2016 07:35:25) deeplink to this comment
Hi Paul, Got it. I'm afraid we have no plans to port the thing to VB6. But you are free to use our ideas to try and port it yourself. I think the base principles would be very much the same.
Comment by: Joseph Volence (29-1-2016 19:50:34) deeplink to this comment
I have this working very well in Access 2010, but there is a slight delay when building a large tree that I did not experience with the mscomctl tree. I have the tree subform in a popup form, and this causes the whole form not to display until the tree is built. Is there a way to build the tree async in the background, and then display when it is fully built - while, in the meantime, allowing the rest of the form to be usable? I toyed with the idea of pre-loading the popup form and setting it to "visible=false" before it is used, but I can't get the form to stay hidden. Once the tree is loaded, it unhides the form. I also tried the "acHidden" argument of docmd with the same result. Thanks!
Comment by: Jan Karel Pieterse (29-1-2016 20:55:34) deeplink to this comment
Hi Joseph, I will ask our Access expert to have a look at this. In the mean time, we are also developing a high performance treeview control. This one will not be for free however. If you are interested, let us know, so we can arrange a beta version for you to try.
Comment by: Yossi Dukes (3-2-2016 21:14:53) deeplink to this comment
Hi, I am very interested in your treeview control. I'd like to be part of the BETA program if possible. Regards, Yossi
Comment by: Jan Karel Pieterse (4-2-2016 11:09:44) deeplink to this comment
Hi Yossi, You can already use the control. as far as I'm concerned it is past beta stage and ready for production use.
Comment by: Valton (5-2-2016 19:28:11) deeplink to this comment
Very good development. They are at another level of programming.
Comment by: Anthony Warner (22-2-2016 03:51:04) deeplink to this comment
Hi Jan, I am very interested in your high performance treeview control. I'd like to be part of the BETA program if possible and If you are at a production level I am very interested in purchasing it once it has been checked out. Regards,
Comment by: Anthony Warner (22-2-2016 03:58:15) deeplink to this comment
Hi Jan, I overlooked in my previous comment that me end use of your production model "high performance treeview control" will be in access as I am experiencing extended load times in my forms with the winforms referenced coding of the demo. thank you very much for having addressed the treeview issue in access as the MSCOMCTL.OCX has been extremely difficult to revise in production front ends as access has matured. Many Thanks Anthony Warner
Comment by: lukasz (24-2-2016 03:15:19) deeplink to this comment
Hi Jan, Have you found a way to speed up adding many (over 5000) elements to the userform?
Comment by: Jan Karel Pieterse (24-2-2016 08:28:18) deeplink to this comment
Hi lukasz, Yes we have, but that version is not available for free. Contact us at the email address below if you'd like to do beta testing.
Comment by: Gianluca (26-2-2016 15:34:49) deeplink to this comment
Hi, i'm geting in trouble because 'Private mUF As ufTreeView' user-defined type not defined... can you support on this? I'm using access 2016 64 bit. Thank you Gianluca
Comment by: Jan Karel Pieterse (26-2-2016 16:01:17) deeplink to this comment
Hi Gianluca, Have you studied the docmentation that is in the Access demo file (the "How do I?..." button)? Especially the part about modStartup.
Comment by: Antonio Salva (10-3-2016 22:30:56) deeplink to this comment
Hi. On clsNode code, Build 025, there is a bug (error) in the Previous Property Get procedure. Yours is:
Comment by: Jan Karel Pieterse (11-3-2016 10:28:27) deeplink to this comment
Hi Antonio, Thanks, we know and was fixed in the current build.
Comment by: Mike C (NZ) (15-3-2016 22:50:19) deeplink to this comment
A great piece of work. Wish I had this 10 years ago working with treeview in MS Access Well done guys.
Comment by: Jan Karel Pieterse (21-3-2016 07:08:26) deeplink to this comment
Hi Brent, Thanks for the tip.
Comment by: Loïc Le Roux (18-4-2016 16:44:06) deeplink to this comment
Hi and thanks for sharing your work. I'm far from being an expert but I'm trying to use this treeview in a project with Access 2016 64-bit and i'm facing some issues. The main one is that the subform doesn't see its parent (in the following function), when I open the main form, and then closes by itself.
Comment by: Loïc Le Roux (20-4-2016 10:47:29) deeplink to this comment
Hello, Thanks for publishing my comment but I finally found my mistake and I think you can remove it. The issue was entirely my fault as I had pasted the line
Comment by: Jan Karel Pieterse (20-4-2016 11:28:46) deeplink to this comment
Hi Loïc, I'm glad you found the problem!
Comment by: Thomas Magdahl (29-4-2016 22:56:47) deeplink to this comment
Hi, Is there any way to change the ImageExpanded of a node. I am using the expanded icon to show the state of a node. It is not working and if it should I am not beeing able to figure it out. icono = "DiscardedIcon" mcTree.ActiveNode.ImageExpanded = icono mcTree.Refresh If I reload the tree structure with the new icon reflecting the new state it displays ok. I am only trying to avoid replace the node with a new one, that has the same info but different expanded icon. Regards Thomas
Comment by: Jan Karel Pieterse (30-4-2016 11:38:45) deeplink to this comment
Hi Thomas, When you create a node, you can set its ExpanderImage property, to which you pass the collapsed and expanded icons. The control then automatically adjusts the image on click. See the demo file, routine called "InitializeDemoIcons" in ufDemo.
Comment by: Thomas Magdahl (2-5-2016 13:37:56) deeplink to this comment
Hi Jan, I know; it works fine when I buid up the tree at program start, from data on an Excel worksheet. At that time I figure out the state of the excel row (very simple, based on the date) and generate its associated node with the correct colapsed and expanded icon. My problem is with the code I provided in the previous post, which happens when the user interacts with the node of the displayed tree. He changes the state of the active node (date changes at the excel row) and the expanded icon of that node has to change acordingly. Sorry if I missunderstood your answer and it only can be done at node creation as in the demo?, but then the property is available and I suspect it should work and I am doing a mistake. At the demo the propperty is only used for retrieving the value but it is not changed at run time with the nodes already created. I started to use your tree class a year ago and left the project by side for other needs. I started this days to continue on it, and I am again surprised by how good it is. My only wish would be to have 32x32 icons, but it is a minor issue for me. If it already had 32x32 I would probbably desire 64x64 :-)
Comment by: Jan Karel Pieterse (2-5-2016 13:55:47) deeplink to this comment
Hi Thomas, Theoretically it should suffice to refresh the tree after changing the expanded state of a node. But you do have to add the right icons to the nodes when you create them. I expect if you create those icons yourself you can use any size you like :-)
Comment by: Thomas Magdahl (2-5-2016 14:22:45) deeplink to this comment
Hi Jan, Sorry for being such a pain in the neck. If you add the line surrounded by asterixes in the demo code and at run time (with icons) double click on a node and type 456, I expect the expanded icon to change to "GreenTick". If a refresh is necesary you can run the code and add a child to another node, and the ImageExpanded will be still the same and not "GreenTick", despite a refresh that must have be requested. Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode) ' Validate user's manually edited node here If Len(NewString) >= 3 Then If NewString Like ("*123*") Then MsgBox "123 is not allowed !" & vbCr & "but try 456", , AppName Cancel = True ' undo user's change ElseIf NewString Like "*456*" Then NewString = Replace(NewString, "456", "789") '************************************************* cNode.ImageExpanded = "GreenTick" '************************************************* End If End If End Sub Regards Thomas
Comment by: Jan Karel Pieterse (2-5-2016 18:01:15) deeplink to this comment
Hi Thomas, Seems there is some roomm for improvement :-) This appears to work:
Comment by: Thomas Magdahl (2-5-2016 19:19:07) deeplink to this comment
Hi Jan, Worked out fine. Thanks for your kind help. Yo can not imagine how important your code (and the calendar you recomend) are for the portability of my simple application. Regards Thomas
Comment by: Jan Karel Pieterse (3-5-2016 08:56:34) deeplink to this comment
Hi Thomas, You're welcome!
Comment by: Jan Karel Pieterse (3-5-2016 16:58:15) deeplink to this comment
Hi Thomas, Seems I overlooked an important method and property. Changing the image of a node is as simple as: cNode.ImageExpanded = "GreenTick" cNode.ImageUpdate
Comment by: Tom (30-5-2016 09:04:35) deeplink to this comment
Hi gentleman, Thanks for your hard work! I'm currently integrating XLS and Selenium Webdriver into Excel VBA, using your TreeView as running instance (or workbench). Very nice to have a pleasant GUI. One question: do you happen to have an alternative for cutcopy by keys, but using some draganddrop function? Kind regards, Tom
Comment by: Jan Karel Pieterse (30-5-2016 18:00:56) deeplink to this comment
Hi Tom, The pro version of the treeview features better performance and drag and drop. It is in Beta right now.
Comment by: Damian Mamani Castro (11-6-2016 11:53:10) deeplink to this comment
it is good.
Comment by: David (12-6-2016 17:49:25) deeplink to this comment
Hi, There is a way to move nodes between siblings (up/down) I tried If Not mcTree Is Nothing Then Set cNode = mcTree.ActiveNode If Not cNode Is Nothing Then idx = GetColIndex(cNode) Set cParent = cNode.ParentNode mcTree.Move cNode, cParent, idx - 1 but it don't work
Comment by: Peter Thornton (14-6-2016 10:28:56) deeplink to this comment
Hi David, Your example should work, though obviously need to ensure vBefore:=idx-1 or vAfter:=idx+1 are between 1 and count of siblings. When done (after moving any nodes or anything else that's going to change what's displayed) need to call mcTree.Refresh FWIW our long awaited Pro version (alpha due this week) includes built-in nudge up/down, promote/demote sideways functions, amongst far more important features!
Comment by: Bill Dowton (17-6-2016 14:34:14) deeplink to this comment
Hi A brilliant piece of work and I'll definitely make a donation! :) I have the control working but am struggling with the icons. If I reference the ones in the demo everything works fine but I don't know how to create a frame with images although I've spent several hours trying :( I've also tried loading my images into the frImages but this doesn't help either. Can you help? Kind regards ps I'd be interested in the Pro version. Any idea when it'll be available?
Comment by: Peter Thornton (18-6-2016 14:16:06) deeplink to this comment
Hi Bill, For some reason adding pictures to image controls in a Frame in Access don't seem to stick. Try doing same in a temporary form in say Excel, then select and copy the image controls to the Frame in Access, can do all in one go. The treeview 'pro' and listview alternative are at alpha stage, contact us off-line if interested to test either.
Comment by: Bill (29-6-2016 16:06:28) deeplink to this comment
Hi Peter I now have the control working (almost) perfectly. I say "almost" because I can't get the icons to resize. I've tried resizing them myself, changing font sizes, node height etc. but they just stay the same. Any idea what I could be doing wrong?
Comment by: Jan Karel Pieterse (29-6-2016 16:57:06) deeplink to this comment
Hi Bill, You should create larger icons yourself and update the picture properties of each icon in the icon frame on the userform.
Comment by: Peter Thornton (29-6-2016 17:55:51) deeplink to this comment
Hi Bill, It depends what you mean by "get the icons to resize". If you mean size the control (label or separate image control depending on 'fullwidth'), change these constants to suit: mcIconSize and mcIconPad (in points, typically in Windows 1 pixel = 0.75pt) But if you mean resize the actual image you need to size the original source to suit and update as Jan Karel suggested. FWIW sizing of icons to the supplied image is handled automatically in the 'pro' version. Did you get my reply in response to your enquiry about the 'pro' ?
Comment by: Pete Danes (30-6-2016 16:03:30) deeplink to this comment
Is there a hard-coded limit in this control? I am working on an implementation that has around 20,000 nodes, up to 23 levels deep. Can I expect any difficulties with this?
Comment by: Jan Karel Pieterse (30-6-2016 16:08:03) deeplink to this comment
Hi Pete, No limits in the control, but with that many nodes the pro version is probably your best bet. I'll forward your details to Peter Thornton.
Comment by: Tony Matyas (8-7-2016 08:45:26) deeplink to this comment
Hi, maybe this small code modification can provide some help using TreeView Demo Version 026's documentation sheets: When I tried clicking or sorting procedures/properties drop downs on the documentation sheets (clsNode, clsTreeView) this always resulted in 1004 Error (the name object doesn't refer to a range; the reference to a constant or a formula Fails, ...). I don't know this to be an exceptional error, however. My Work around simply changes 2 code lines in module modDocNav from .RefersToRange to .RefersTo
Comment by: Peter Thornton (8-7-2016 16:06:22) deeplink to this comment
Hi Tony, Not sure why you're getting that error. AFAIK it's been working for most users for a long time with no problem. Could you send me the file so I can have a look. Also say which Excel version you're using.
Comment by: Alan Santos (22-7-2016 23:35:24) deeplink to this comment
Hi!Excellent job! In the TreeView I have a doubt: How can I adjust for when I start the Form to return the tree that was exported for the last time and that is in the "dump data" sheet? Thanks a lot and congratulations.
Comment by: Peter Thornton (1-8-2016 15:45:16) deeplink to this comment
Hi Alan, Thanks for your comments but not sure I understand your question - are you saying you want to reload the data that was dumped by the "dump data" example in Excel?
Comment by: Tony Matyas (2-8-2016 08:42:14) deeplink to this comment
Hi Alan, a hint to clarify your problem: it seems to me the Data Dump procedure has been deactivated in the 026 Demo Version just for temporary testing, so that no data dump is executed when clicking to the related command button. If you have a look at the ufDemo cmdGetData_Click, you should possibly find two inserted code lines, the second one stops the procedure with an 'Exit Sub'.
Comment by: John Murphy (5-8-2016 14:41:54) deeplink to this comment
Hi, I have commented out the 'Exit Sub' in 'cmdGetData_Click' procedure (as per Tony Matyas' suggestion) and 'dump data' now works beautifully but, how do I get it back? Thanks and congratulation on a brilliant piece of work!
Comment by: Jan Karel Pieterse (5-8-2016 18:42:12) deeplink to this comment
Hi John, There is no code in the demo to read the range back into the treeview I'm afraid.
Comment by: Jan Karel Pieterse (8-8-2016 14:34:02) deeplink to this comment
Hi John, You can read the data as dumped from the tree with code like this:
Comment by: John Murphy (8-8-2016 16:16:27) deeplink to this comment
That's absolutely BRILLIANT!!! I can now make changes within the TreeView, dump them and then reinstate them, which is exactly what I wanted. Thanks very much for your help and efforts, and for responding so quickly. Thanks a million!
Comment by: Jan Karel Pieterse (8-8-2016 16:43:43) deeplink to this comment
Hi John, You're welcome!
Comment by: Adam Taylor (8-8-2016 22:36:07) deeplink to this comment
Hi - GREAT product, thanks for sharing. I have an Access-related issue in relation to this. I've got the treeview shown as a subform, and sometimes if it falls over it can revert to showing with the flags. It's working great other than strange behaviour in the event of an unhandled error in my application. Is there a way to check for this happening and protect against it? Some simple command that can switch it back on? Or do I need to close and re-open the form as seems to be the case at present? Thanks in advance, Adam
Comment by: Jan Karel Pieterse (9-8-2016 09:23:17) deeplink to this comment
Hi Adam, My guess is that the treeview reverts to the default settings when your VBA code runs into a runtime error, but I can't really tell :-) I'm surprised it even keeps showing as I'd expect a code reset to remove the tree altogether?
Comment by: Peter Thornton (12-8-2016 15:02:05) deeplink to this comment
Hi Adam, Can you repro' when "it falls over". Add something to flag or debug when the subform's open and close events fire. In some scenarios the subform seems to want to close and immediately reopen, eg print preview and report view. If that happens the userform that contains the treeview will have gone which might explain what you describe. If this sounds familiar to any Access folk and in particular how to handle this scenario please contact me off-line!
Comment by: Tony Matyas (23-8-2016 16:23:25) deeplink to this comment
Hi, the All VBA Treeview shows an icon part on the left and an editable node caption part on the right. As I use XML files as data base and want to display also the corresponding XML node tags, I would like to enlarge the treeview by inserting a tag label, preferrably displayed between these two parts.
Comment by: Peter Thornton (24-8-2016 11:45:26) deeplink to this comment
Hi Tony, That's certainly possible: [icon] / [checkbox] / [tag-label] / node-caption. If applicable tag-labels could be included individually only for nodes only which have a tag caption. It would be too much to give full details here but to point you in the right direction look at how the respective node controls are added, and adapt for your tag labels. Although it's relatively straightforward there are some small details to include, if this is for a professional project it might be cost effective if we implement it for you.
Comment by: Scott Cordwell (2-10-2016 23:36:56) deeplink to this comment
Hi, Great work guys. I've been using the tree view in Access 2007 using the runtime for a while now. I just installed onto a Windows 10 PC that has Access 2013 retail on it as well. I'm getting a "System Error &H8007007E (-21470224770)" when opening the form with the treeview on it in the runtime environment. Then vba generates a "2926 - Because of your security settings and current security policy, this control has been disabled...." error. The front end works fine if opened using Access 2013 Retail? I've checked trusted locations etc, with no result. Any tips would be appreciated. I'll be getting the commercial version when released. Thanks. Scott
Comment by: Jan Karel Pieterse (3-10-2016 07:04:31) deeplink to this comment
Hi Scott, Are you saying you have two Access versions installed side by side? In my experience this often leads to problems with the references. I'd advise to try whether it works on a single-install of Access 2013.
Comment by: Scott Cordwell (3-10-2016 07:37:01) deeplink to this comment
Hi Jan, Yes, I use Sagekey Software's Access Runtime install builder to install the front end application and runtime on all PCs regardless of whether they have Access Retail on them or not. Works really well (for years) and has never adversely affected any existing Access installs. I suspect it's either a permissions issue or an FM20.dll version problem on this particular PC. I'll have to do some more digging. Thanks Scott
Comment by: Jan Karel Pieterse (3-10-2016 16:45:20) deeplink to this comment
Hi Scott, OK, please let us know if you keep having problems or if you find the root cause.
Comment by: Kieran Shirey (12-10-2016 02:16:04) deeplink to this comment
Hi, My license has expired. Is it possible to move from Access 2013 to Office 365 with a database that has a Treeview Control in it?
Comment by: Jan Karel Pieterse (12-10-2016 06:53:49) deeplink to this comment
Hi Kieran, you should be able to provided you install the 32 bit version of Office 365 (default). To be future-proof, perhaps now is the time to bite the bullet and implement our treeview ?
Comment by: Bill Cawley (17-10-2016 11:40:31) deeplink to this comment
THANK YOU! I have programmed - on and off - for over 30 years, yet I still cannot find how to install a Treeview addon to my Excel, even when following the rules conscientiously. Nor do Microsoft provide any examples that I could emulate. Your example download works fine. You have saved me a great deal of time.
Comment by: Gestec Video (2-11-2016 18:55:31) deeplink to this comment
I'm using your treeview control with Access 2016, but it doesn't work properly. The problem is that the captión of some nodes are hiden when I expand or collapse a node, but when I click on the caption of the hidden node, it appear. The icon to expand and collapse doesn't respond when I click over it. I have to chose de option 'expander icon'. The problem also happen with your demo.
Comment by: Jan Karel Pieterse (2-11-2016 19:41:10) deeplink to this comment
Hi Gestec, This is a known (temporary) problem with Office 365. Microsoft is working on a fix which should be published to the people which are in the fast track update schedule within the next couple of days.
Comment by: Bruno MARCHAL (6-11-2016 06:35:31) deeplink to this comment
Hi, First of All Thanks for all the Job Done ! Questions It seems it doesn't work with an accdb file I've tried to include the reference of Form 2 which is located in another folder but it can't compile the code due to problem of Uftreeview definition that is defined in user32.dll I assume. Any tips? I intend to Donate cause i am also interested in Drag & Drop Features. How does it work? Thanks
Comment by: Peter Thornton (7-11-2016 13:14:22) deeplink to this comment
Hi Bruno, thanks for your comments! I'm not sure why it's not working as an accdb for you. I can simply convert the demo mdb file to an accdb with SaveAs. Or, create a new accdb and drag the modules from the mdb. To add the Userform see the CheckUF function. Also get the v26 class modules in the Excel download if you haven't already. If stuck contact me off-line and I'll return an accdb (my address is in the headers). We've only implemented Drag & Drop in the 'pro' version (main feature is performance with much larger treeviews). This is currently available as beta in Excel & Word, and soon for Access.
Comment by: Kerry (9-11-2016 04:14:17) deeplink to this comment
First I wanted to say thanks a lot for creating the treeview control. It is a very useful functionality, and I wish MS had the control control available by default for 64 bit. I was wondering if you have code that can be used to populate the treeview control you created recursively. Thanks again.
Comment by: Peter Thornton (9-11-2016 11:43:13) deeplink to this comment
Hi Kerry, thank you for your comments! Without knowing how your source data is arranged and the hierarchical relationships are defined, it’s difficult to suggest a generic recursive approach to populate the treeview. If you can’t find an example to adapt maybe we can sort out something privately for you.
Comment by: Rob Sloan (17-11-2016 02:04:04) deeplink to this comment
This looks truly amazing. Hoping to likewise tackle the recursive issue to populate the tree view. Have a somewhat simple table: PK_Id, Name, Parent_Id (with Self Join to PK_Id) which can go X number of levels deep. I will take a look for some examples. But if you have any ideas, that would be great!
Comment by: Peter Thornton (17-11-2016 14:57:04) deeplink to this comment
Hi Rob, Even with your description afraid I can't visualise how your data is organised, or even if you actually need a recursive function. If you're new to recursive functions perhaps the key thing to consider is when variables should be be passed ByRef or ByVal. Typically with hierarchical structures the 'Level' counter should be ByVal as it increments, so it can reset after a given branch has completed. There's a recursive function in the demo for reading the treeview. If stuck you can send me an example of what you've got off-line and I'll take a look.
Comment by: Rob Sloan (22-11-2016 16:53:04) deeplink to this comment
I was able to use a recursive CTE in SQL (the backend for this database) and populate the treeview. The last couple things I am stuck on are 1) creating new properties for the clsNode that would only show the checkbox at the root node and 2) if possible, making certain root nodes read only (cannot unselect check box after marked as checked on a "for each cnode in mctree.nodes"). This is going to work wonders!
Comment by: Peter Thornton (30-11-2016 13:17:50) deeplink to this comment
Hi Rob #1 Adapting to show checkboxes only for certain nodes is quite difficult but not impossible. #2 Simplest way to prevent un-ticking a checkbox would be something like this, in the NodeCheck event on the form
Comment by: Damon (12-12-2016 22:23:21) deeplink to this comment
I've just started implementing this treeview and all I can say is "wow! You have saved my life!". I have three questions/comments: 1) I read in the comments above that for use in Access we should be able to copy the 026 modules into our project while using the startup, subform and userform from 025. I'm just starting my implementation and I'm wondering if anyone has successfully done this or whether I'm setting myself up for more headaches than simply using 025 as-is. 2) I want to use this treeview to display a folder/file structure and I'm wondering what I should use for the keys. I'm thinking about using the actual file's full path as the key so that they remain unique. Alternatively I could simply use the filename as the key and then when I want to access the file, use the FilePath method to build the paths as needed. Am I making a big mistake with either approach? I'm talking about a folder/file structure that is not very complex, maybe 3 or 4 levels deep and a total of a couple of dozen files, so performance isn't mission-critical. 3) I'm thinking also of implementing a "FullCaptionPath" method that takes an optional separator argument so that I can easily build a user-readable "Path of Captions" from a given node. Perhaps an optional separator can be used for the regular FullPath option so that we can extract the path in whatever format we want?
Comment by: Jan Karel Pieterse (13-12-2016 06:50:13) deeplink to this comment
Hi Damon, The best thing to do is open the example Access file and look at the "manual" which can be reached from the "How do I...?" button on the startup form (or open the frmHelp form).
Comment by: Jan Karel Pieterse (13-12-2016 06:53:15) deeplink to this comment
Hi Damon, As to your other questions: 2. I guess it doesn't make much of a difference. Though using just the filename risks duplicates. 3. This is precisely why our treeview is so flexible: you can add any functionality you like :-)
Comment by: (19-12-2016 16:39:27) deeplink to this comment
Sorry - the "Contact Us" link isn't working 'cause I don't have an email client registered at this location. And I thought there may be value in sharing this. I'm interested in the beta tests - but. I'm an Access developer and rarely work with Excel. Is the listgrid control something that would be usefull to a Access only person like myself? As for the treeview - friggin brilliant so far. BC
Comment by: Jan Karel Pieterse (19-12-2016 16:55:31) deeplink to this comment
Hi BC, I'll add you to the beta program for sure. Just send an email to i n f o AT j k p - a d s . c o m. I have no idea whether the listgrid would be useful to you. I would guess Access forms would do the trick nicely for displaying records.
Comment by: Peter Nguyen (5-1-2017 20:03:41) deeplink to this comment
Hi Rob, Would you post your code that populate the treeview from SQL backend? Thanks
Comment by: Christian (6-1-2017 23:09:44) deeplink to this comment
Hello Jan, I have implemented your treeview successfully in my access database. After adding a header and footer to my form it doesn't work any more. Run Error '-2147221494(8004000a)': Refresh: ' TreeControl' frame is not referenced After deleting the header and footer the treeview works again. What should I do? Regards Christian
Comment by: Peter Thornton (7-1-2017 12:50:58) deeplink to this comment
Hi Christian, I can't reproduce your error after adding a header or footer, not sure why it fails for you. What should happen is this: In the subform's load event an MSForms Frame is created and a reference retained mfrTreeControl. When the Treeview is created the next line should pass this reference to the treeview, in the demo/subform see Property pTreeview. I suspect what's happening for you is some action is causing your mfrTreeControl reference to lose scope, after the subForms' load event and before this line: Set mTreeview.TreeControl = mfrTreeControl Anything that causes your project to recompile while the form is running would result in variables losing scope.
Comment by: Christian (9-1-2017 13:38:47) deeplink to this comment
Hi Jan, I have solved the problem with the header and footer. Now I try to adjust the icons for my application. At first I select the image on the frame frImage. Then I chose properties. Under picture I try to change the picture, but it doesn't work. All other fields, like Name, can be edit. Is this the wrong way to adjust the images/icons?
Comment by: Peter Thornton (9-1-2017 13:56:09) deeplink to this comment
Hi Christian, For some reason it can be difficult or impossible to apply the picture to the MSForms image controls in Access. The workaround is create your images in a temporary userform in Excel or Word, and copy them into the Frame in Access. Be sure to name image controls as required and with unique names.
Comment by: Julian Rodriguez (13-1-2017 21:49:50) deeplink to this comment
Hi Peter: First of al allow to join to all the congratulations you've recieved so far for this brilliant piece of work. It is elegant, functional and I am sure that for many of us a relief after years of fighting with the problems of office and windowa versions compatibiliy of their treeview included ui the Microsoft web commons dll (mcotl). I have implemenyed your treeview in my appplications and so far goes so nicely. However, I noticed that when the nodes caption is wider than the subform treeview let say when expanding the tree, the lef-roght scrll bat shows up which is correct but, when I collapse back the tree and the caption of the nodes are within the limits of the subform, the scroll bar does not dissapear and stays on. Curiously this does not happen witn the up and down scroll bars. Is this behaviour normal? Is it a bug? Than you very much for your support and of course your code and for allowing us to use it
Comment by: Peter Thornton (14-1-2017 14:30:23) deeplink to this comment
Hi Julian, thanks for your comments It’s not a bug but I agree not ideal. For the vertical scrollbar it’s relatively simple to size the scrollheight according to number of visible nodes, or turn on/off if the total height of visible nodes is more or less than the height of the treeview. There’s much more to consider with the horizontal scrollbar. As written it displays and sizes to fit the widest node displayed in the lifetime of the treeview. That might be as a result of expanding nodes, or scrolling down and finding a wider node, or editing a node. In the scenario you described you might reasonably expect the scrollbar to adjust according to the node expanded in front of you, but what about wider nodes further down tree. Also, the widest node might not be in the highest expanded level, a level-1 node with a long caption might be wider than a level-3 node with a short caption. The scrollbar could be adjusted in real time as the tree is scrolled vertically to fit the current view, but would give an unsightly appearance while scrolling vertically. The best solution might be to size the scrollbar to the widest node in the current overall expanded configuration of the treeview, including out of site nodes. Indeed that’s possible, but more code than you might imagine and in a large treeview might compromise performance.
Comment by: Julián Rodríguez (15-1-2017 19:52:01) deeplink to this comment
Hi Peter and Christian: 1. Peter I understand what you explained and I think we can live with it. But what felt strange to me was that the horizontal scroll bars stay in a totally colapsed tree with all the nodes within the treview control in number and size. Anyway, as I told you not big deal and I will perfectly will live with it. It is a wonderful control that you have gifted to all of us. 2. Christian: I think, if I have not missunderstood you, that I have run with the same problema many times. The way I get around it is as follows: A. I get into de editor VBA tool window, look for the forms in the left side tree, and remove the ufTreeview object without exporting it. B. I close the aplication and open it again pressing the shift key so it goes to the Access window without executing macros and without openning forms. C. Then I run the startup macro which ask me if I want to create a new userform required for the correct functioning of the treeview. I say yes and it creates it. D. Then I close again the application and I am asked if I want to save the changes in the UF ufTreeview. I say yes and get out. E. I open normally the application and when open the demo form I close it, open the subform subTreeview which shows me the frame control with the my image controls and then it lets mi to change the image again by looking for it whenever I have the iamge I want to link to the image control. I do not know why it does this acces. It happened to me whenever I got a crush in my application and it is like something gets corrupted in the userform or so. I am not an expert in programming and I don't understand very well this behaviour. The important thing is that I can get around it! :)
Comment by: Christian M (31-1-2017 20:07:30) deeplink to this comment
Jan/Peter Well done, what a wonderful control. In the process of updating the code to add data columns to the right of the tree I noticed what I think is a typo. Shouldn't msngTopLabel be .Width instead?
Comment by: Peter Thornton (1-2-2017 01:29:07) deeplink to this comment
Hi Christian M, Thank you for your comments! Typo is a nice way of putting it but looking afresh not sure what the 'If msngTopLabel < mcFullWidth' was supposed to accomplish. If the treeview is not resizable during runtime, could change the Const mcFullWidth = 600 to a module level variable and assign its value as the treeview width (TreeControl) if the FullWidth option is set. It depends what you mean by "updates" but you're welcome to send them to me off-line. Others have also added columns.
Comment by: James (1-2-2017 11:51:01) deeplink to this comment
Hi, Is there any way to increase the Treeview line thickness? Thanks, James
Comment by: Jan Karel Pieterse (1-2-2017 13:37:21) deeplink to this comment
Hi James, You could play with the width and height of the label controls we've used for the lines perhaps. Those are controlled by this line in clsTree:
Comment by: Scott Braley (2-2-2017 06:11:49) deeplink to this comment
I just want to say that this tool is amazing. I do have one concern though before I try implementing this in my project. I've always understood that MS Access limits you to a certain number of controls that can be added/deleted over the lifetime of a form. Is that not the case? Thanks for your time.
Comment by: Jan Karel Pieterse (2-2-2017 10:50:33) deeplink to this comment
Hi Scott, So far we haven't had any complaints in that direction so I think we can safely say it works just fine. Should you run into a problem anyway, we do have a pro version which uses less controls to display the tree. You can register for the beta program to try it.
Comment by: Marcos Ximenes Carvalho (2-2-2017 22:23:31) deeplink to this comment
Mr. Pieterse I live in Brazil. What i need to do to change the icons? Thank you, very much
Comment by: Jan Karel Pieterse (3-2-2017 11:51:58) deeplink to this comment
Hi Marcos, You mean how you should add your own icons or how to use different ones of the icons we already provided? To use different icons, simply remove the ones we provided from the icon frame and insert new image controls into the frame and make sure their content is the way you like it. You can also of course replace the image sin the existing image controls. Make sure you name the picture controls properly as that is what the class uses to select the right icon.
Comment by: Marcos Ximenes Carvalho (5-2-2017 19:39:45) deeplink to this comment
Dear Mr Thank you very much for the material sent, but I could not put my icons in trieeview. Can you send an example? Thanks Marcos
Comment by: Peter Thornton (6-2-2017 15:40:50) deeplink to this comment
Hi Marcos, If you are using Access it can be difficult to add or edit the image controls. One way is to add and edit the image controls in Excel (on a userform), apply the 'picture' and simply copy them into the Frame in Access. You can copy/paste multiple image controls in one go. Also see this comment above Julián Rodríguez (1/15/2017 7:52:01 PM) I haven't tried Julián's suggestion but it looks interesting. In the 'pro' (currently beta) images can be stored as 'blobs' in a table.
Comment by: Arran Spencer (7-2-2017 19:47:30) deeplink to this comment
Hi MVPs, You mention that it is important to make sure that all instances of the classes are destroyed. I would like to use the classcounts code you have included in the demo to check that all instances are in fact destroyed in my implementation (I am using the queryclose event as per the demo). So I have some questions... How do I set debugmode to 1 to use classcounts? What happens if all instances are not destroyed? PS. The tree view tool is amazing, far better than I expected, and easy to implement (and although some of the code is hard to understand for a learner like me, I have started to improve my own VBA skills by studying your code so thanks for that too!) Thanks Arran
Comment by: Jan Karel Pieterse (8-2-2017 10:56:23) deeplink to this comment
Hi Arran, You can enable the class termination checks by adding a conditional compile argument to the project properties (tools, VBA project properties, add DebugMode = 1 to the last textbox of the dialog)
Comment by: Alexandre (7-3-2017 11:46:48) deeplink to this comment
Hi, I am using your ClsTreeView and ClsNode classes to have a treeview on my form. However, when I close the form and it gets unloaded, I get an "automation error" or "Object required error". I saw that some sheet objects, which are normally not empty, get emptied. If I remove the line calling this form, my code works. Have you ever seen this issue? Could you please help? I can send you screenshots via email. Regards, Alexandre
Comment by: Jan Karel Pieterse (8-3-2017 09:50:46) deeplink to this comment
Hi Alexandre, Sure, send your screenshots (perhaps your file as well, so we can trouble-shoot?).
Comment by: Kaman (17-3-2017 16:12:04) deeplink to this comment
Hey guys, I am having a problem trying to get the mcTree_KeyDown, mcTree_Click events to fire, am I doing something glaringly obvious wrong? It's working in your frmDemo in the same database. Cheers
Comment by: Peter Thornton (19-3-2017 13:27:32) deeplink to this comment
Hi Kaman, Did you declare a withevents reference to the treeview, eg
Comment by: Daniel Dumitru (27-3-2017 15:30:54) deeplink to this comment
What you guys created is pure genius. I thought I knew VBA, but I'm barely keeping up with understanding how to use the code you created. I can't even begin to fathom how much work you put into this! Congrats! Do you think you can help me with something? I'm trying to create a file manager, and I got almost everything working, except the trigger
Comment by: Jan Karel Pieterse (27-3-2017 15:45:36) deeplink to this comment
Hi Daniel, I assume you have this in the declaration section of the userform's code module:
Comment by: Daniel Dumitru (27-3-2017 16:35:26) deeplink to this comment
Hi Jan, Yes I do. I have the
Comment by: Daniel Dumitru (27-3-2017 16:40:02) deeplink to this comment
It does have to be defined in the Form, now it's working. Thank you!
Comment by: Jan Karel Pieterse (27-3-2017 16:50:54) deeplink to this comment
Hi Daniel, Indeed in the form, as that is where form code belongs :-)
Comment by: Filia (15-6-2017 15:34:32) deeplink to this comment
Hi, I cannot import Form_subTreeView into my project (Access 2016). Message: The module name 'Form_subTreeView' is invalid". Any idea why?
Comment by: Jan Karel Pieterse (15-6-2017 15:57:05) deeplink to this comment
Hi Filia, I'm afraid I don't know!
Comment by: Martin (16-6-2017 02:58:20) deeplink to this comment
You mention: Access 2003 Access 2010 (32 bit) Access 2010 (64 bit) What about 2007, 2013 & 2016?
Comment by: Jan Karel Pieterse (16-6-2017 15:03:02) deeplink to this comment
Hi Martin, It should work on those versions as well, the ones mentioned are the ones we tested on.
Comment by: Peter Thornton (22-6-2017 09:38:07) deeplink to this comment
@ Filia, I've just tried the following with Access 2016 64 bit: - Open a newly downloaded treeview demo v025, all worked fine. - Drag the subForm from the treeview demo to a new project, no problem. @ Martin, >What about 2007, 2013 & 2016? I can confirm working in Access 2007, 2013-32, and 2016-64. I believe others have tested in 2013-64 and 2016-32. The current version 025 was made with 2003. We held back from updating to 026 as we can no longer compile in a way that will work for 2003. However as time has gone by we may update the 025 mdb to 026 as an accdb.
Comment by: Filia (29-6-2017 16:08:00) deeplink to this comment
Well, I got it to work through trial and error in Access 2016. The only problem remaining is that when text in the control extends under the vertical scrollbar and I drag the horizontal scrollbar to the right to view it, the vertical scrollbar doesn't refresh so I get 2 vertical scrollbars (original position on load and new position resulting from dragging). When I populate the control, my last two commands are Refresh and then ExpandToLevel(x). Any help to solve this will be appreciated.
Comment by: Peter Thornton (1-7-2017 15:54:01) deeplink to this comment
Hi Filia, I don't know how two vertical scrollbars can be displayed at the same time because normally only one can exist. However moving the horizontal scrollbar will not do anything to help show nodes 'below' the vertical scrollbar. If you want you can send me what you have off-line and I'll take a look.
Comment by: Julian (6-8-2017 13:10:54) deeplink to this comment
Hi, there. I have implemented your wonderful treeview in my aplication and so far I was running it on Windows 7 or higher + Officce 2007. However I understood that your treview was also ready for access 2003. Well as some users of my aplication still run it on XP and access 2003 I downgraded my aplication to a version for them and I have run into an strange problem. Whenever at the begining when it has to pass the trusted property it fails to do so and tells me it has not found that object (?¿) and it does not create the uftreeview userform. I really do not understand very much about this. I tied tou use your demo compiled by the way in access 2003 and it just crash ass soon as you enter in the mdb file. Do you think there is something I am doing wrong? It is not compatible? Thanks
Comment by: Peter Thornton (7-8-2017 10:34:07) deeplink to this comment
Hi Julian, I have similar problems as you describe with Access 2003. Not sure but possibly because my 2003 is now in Windows 10 with multiple later Office versions. These all 'share' the VBA run-times, these have been updated with various security changes which maybe cause problems in the now unsupported 2003. If your Access 2003 is in a similar setup could you ask one of your XP/2003 users to download a fresh copy of the Access 025 demo and try it.
Comment by: Julian (18-8-2017 19:16:15) deeplink to this comment
Hi Peter, thanks for the suggestion. I will do that and see what happens. Thanks so much
Comment by: TungSon Nguyen (20-8-2017 16:41:39) deeplink to this comment
Hi JKP, I am trying to use your treeview control for my project. there are two point I would like your help to make me clear: 1. I could not get the tooltip option, especially when my node text is longer then the width of the treeview-container frame. 2. Is this possible to get the FullPath of a node with the node caption instead of the node key? I am looking forward to receiving your reply. Thank you in advance and Best regards.
Comment by: Jan Karel Pieterse (21-8-2017 08:21:05) deeplink to this comment
Hi TungSon, In class clsNode, find the routine called AddChild. Search for: ".Caption = vCaption" Below that line, add:
Comment by: TungSon Nguyen (22-8-2017 13:03:11) deeplink to this comment
Hi JKP, I am following your instruction but it does not work and I got an error message: "Method or data member not found". By the way, what i found is ".Caption = sCaption" instead of ".Caption = vCaption" What should i do now? Thanks and regards.
Comment by: Jan Karel Pieterse (22-8-2017 16:10:01) deeplink to this comment
Hi TungSon, If you email your file I'll have a look.
Comment by: TungSon Nguyen (23-8-2017 07:36:22) deeplink to this comment
Hi JKP, I got the clue: the one i am using is Ver 015. i down loaded the latest one from your page, V026, then followed your instruction and it working now miracly. i will send you my file when i finish coding. Thank you for your sharing and instruction. Best regards.
Comment by: Jan Karel Pieterse (23-8-2017 09:56:51) deeplink to this comment
Hi TungSon, Great, glad you got it solved.
Comment by: Dean Arnold (26-8-2017 15:20:09) deeplink to this comment
In the clsNode code for mctlControl_Click() it states that "NodeClick will raise the click event to the form". How do you take advantage of this? What I am trying to do is to jump to a record when it is selected on the tree (I have subTreeView and a subRecordView on a form). I thought it would fire the On Click event of the parent form...I could then derive the selected record number from the selected node's sKey and jump to it.
Comment by: Peter Thornton (26-8-2017 18:37:09) deeplink to this comment
Hi Dean, In your form or even a class modules declare a WithEVents reference to the clsTreeview, eg Private WithEvents mcTree As clsTreeview Select mcTree in the middle dropdown and in the right select 'Click' This will add the Click even stub to your form code. When you clickk on a node with the mouse or navigate with keyboard arrows the Click event should fire. cNode will refer to the clicked node. For your objective I guess your first line will include - cNode.Key
Comment by: Dean Arnold (27-8-2017 04:14:36) deeplink to this comment
Thanks...makes sense and works great. Two more questions, if you don't mind. I assume if I want the dblClick event to do something similar (e.g., open the selected record for editing when the node is double clicked) I would need to first modify the mctlControl_dblClick() routine to behave similar to the mctlControl_Click() routine. Secondly, is there a function that collapses the tree to a certain level? When I initially load the tree I would not wish to show four levels of detail.
Comment by: Peter Thornton (27-8-2017 11:51:28) deeplink to this comment
The dblClick event is already trapped in the node class to start Edit-mode (if enabled). Follow from where Click is trapped in the Node class to 'Event Click' and replicate with the dblClick, but best not with EnableLabelEdit The default .Expanded property for nodes is True, so change any to False as required while populating but before calling Refresh. Alternatively, yes there is a method to expand the tree to a specified level, look at .ExpandToLevel in the documentation (in the Excel demo)
Comment by: Wolfgang Backes (30-8-2017 10:20:44) deeplink to this comment
Congratulation for this very neat and admirable piece of software. After adding a root node without caption I lateron got error 13 (type mismatch) in Sub BuildRoot. The error is caused by the code line .Caption = cRoot.Caption due to the fact, that cRoot.Caption is not available here. Of course, there are plenty of trivial ways to circumvent the error, for example 1) Without changing your source code: the client program could be required to supply a value for paramter 'vCaption'in method 'AddRoot' of class clsRoot. But in my opinion an optional parameter should never be compulsary. 2) I think a caption is always supposed to be of type String. Therefore, adding an appropriate default value of "" for the optional parameter vCaption solves the problem. 3) You could change the culprit line by e.g. .Caption = IIf(IsMissing(vCaption), "", vCaption) 3)
Comment by: Peter Thornton (30-8-2017 14:17:46) deeplink to this comment
Hi Wolfgang, I think you're the first person in 4 years to stumble on that! It didn't occur to us anyone would include an empty caption;) The Caption as displayed of course is always a string, but as a property might want it retained as a string or numeric, or something else, so I think best to keep it as a Variant. Your suggestion 3) is probably the best way to handle it for all scenarios, or almost the same
Comment by: Julian (1-10-2017 16:29:29) deeplink to this comment
Hi Jan: I use your treeview in acces based application using the last version available (BUIL 025). It works really great except for one issue. I would like to be able to scroll the treeview with the mouse wheel (I am on Access) but it does not work in this version. Reading past comments on your web I saw that this problem had already been issued by Thomas Koester back in 2015. You suggested him to adapt a piece of code to have it working and apparently (he was an expert programmer) he succeded in doing that. I followed that link and tried to adapt that code to my application but beeing honest I am far from having enough knowlege to do that and I failed several times. Is there any way you could help me with that. Is there other alternatives (2 years have past and maybe there is something new) Thanks
Comment by: Jan Karel Pieterse (2-10-2017 07:58:51) deeplink to this comment
Hi Julian, Wheel scroll is implemented in our (paid) pro versio of the treeview. If you are interested, send us an email (see address near bottom of page).
Comment by: TungSon Nguyen (10-10-2017 09:13:21) deeplink to this comment
Hi JKP, I am using your treeview with my app. And now, I am asking for your support on my problem: On the treeview already created, can I add a Parent node for a Root node? If yes, would you please show me the way? Thank you and best regards.
Comment by: Jan Karel Pieterse (10-10-2017 13:25:25) deeplink to this comment
Hi TungSon, That would probably take removing the rootnode, adding a new rootnode and then adding your removed rootnode to the new root.
Comment by: Gustavo Ruiz (20-10-2017 05:46:25) deeplink to this comment
Great job! Had a few problems a year back with several windows updates that broke MSCOMCTL.OCX and forced me to do several "tricks" to get it work with older OCX version under Win7 64bit, treeview under MS Access let me show the proper office app main menu based on user permissions (activating nodes accordingly). This week I had to migrate to Office 2016 just to found out that the installed version was 64bit, although there was no need for it but some IT guys see 64 bit on the OS and think that Office should be 64 bit aswell even if this mean losing functionality in several Excel files based on 32bit addins/VBA code :) Anyway, older treeview has gone and now its replaced by your excellent MSForms Treeview. I will donate to you as soon as I finish this message. If anyone is doing this on Access be prepared to add several PtrSafe keywords on your code and convert Long to LongPrt on some of the parameters related to :) THANKS!!!
Comment by: Jan Karel Pieterse (20-10-2017 14:48:16) deeplink to this comment
Hi Gustavo, You're welcome! And BTW, you can find the API stuff here too: https://jkp-ads.com/articles/apideclarations.asp
Comment by: AYAMNASH (13-11-2017 14:16:39) deeplink to this comment
Hi JKP I have been trying to get my data table but i could not because i'm beginner in vba code i have one table (table1) for g/l accounts field one (fatheraccno)type number field two (fatheracc)type text field three (subaccno)type number field two (subacc)type text how i can get my table to your subform in your project ? could you write the code? thank in advance 9
Comment by: Jan Karel Pieterse (14-11-2017 09:58:49) deeplink to this comment
Hi AYAMNASH, What does your data look like?
Comment by: ayamnash (19-11-2017 19:52:51) deeplink to this comment
realy i don't understand what does you mean any way ... i will ask my question in another way i have accounting data base it is working perfectly but it has not chart of account (treeview ) The problem was existence two tables the first table for( accounts category ) the second table for (account names) Many experts told me it is not good to build chart of accounts with two tables Because in case of deletion, modification or addition it will be difficult to modify the two tables So I want to create a new database for new project based on one table . I expect the table to have four main fields field one (fatheraccno)type number field two (fatheracc)type text field three (subaccno)type number field four (subacc)type text subaccno subacc fatheraccno fatheracc 1 Assets 11 fixd assets 1 Assets 1101 cars 11 fixd assets 1102 buildings 11 fixd assets 11021 building1 1102 buildings 11022 building2 1102 buildings 1103 lands 11 fixd assets 12 current assets 1201 cash 12 current assets 1202 bank 12 current assets 1203 debitors 12 current assets how i can drop this table to your subform?
Comment by: Jan Karel Pieterse (19-11-2017 20:31:09) deeplink to this comment
Hi ayamnash, The demo treeview contains code that demonstrates how to add nodes and sub nodes. It should not be very hard to figure out how to add the information in your table to the tree.
Comment by: Mathias Dumke (20-11-2017 10:32:20) deeplink to this comment
Hi, very good job. My Question is, how i can scroll in the treeview. Greeting. Mathias Dumke
Comment by: Peter Thornton (21-11-2017 18:40:08) deeplink to this comment
It's difficult to make wheel scroll reliable in the 'free' version. If you really need it scroll is included in the 'pro' version, see just below the top of this page.
Comment by: Ivan Petrik (26-11-2017 11:51:09) deeplink to this comment
Dear JKP, I would like to ask you how to add new image to the form frame. I tried to load new image to the frame but property Picture remains "(None)". Could I ask you for your help? Many thanks. Best regards Ivan Petrik
Comment by: Jan Karel Pieterse (26-11-2017 16:31:03) deeplink to this comment
Hi Iwan, Simple: - You insert a new image control - Name that control the same as how you want to refer to the picture in code - Copy the image you want in the control - Select the control and click in it's Image property - hit control+v
Comment by: Ivan Petrik (27-11-2017 21:42:33) deeplink to this comment
Thank you for response. Now it's finaly working. The problem was in the way how to edit the frame. If I open frmDemo in design mode, click on the SubTreeView and try to add some new image onto the frame, the properties are read only. But If I open the SubTreeView directly, the frame is available to edit. Great job Jan.
Comment by: edd (4-12-2017 17:36:45) deeplink to this comment
Hi, my data will end up creating a ragged structure with some levels as deep as 27. Will this treeview work with the recursive loading used in the Microsoft article as below; https://support.microsoft.com/en-us/help/209891/how-to-fill-a-treeview-control-recursively-in-access-2000 Whilst I was able to use the above, I am not sure I know enough to implement this without someone pointing me in the right direction... Ps this is great stuff!!! :)
Comment by: Peter Thornton (5-12-2017 10:17:47) deeplink to this comment
Hi edd, There's no defined limit for the number of Levels though for usability 27 seems a lot. That recursive example would need slightly adapting but it should work, at a glance you'd need to change: objTree As TreeView > WithEvents objTree As clsTreeview ' at module level nodCurrent As Node > nodCurrent As clsNode objTree.Node.Add > objTree.NodeAdd tvwChild > tvChild You'd first need to create the Treeview and write any properties you want included, and not need the Set objTree = Me!xTree.Object line. Refer to the documentation in the Excel download and update the Access v025 to 026 If you've got a large tree, particularly with those 27 levels, you might find our 'pro' version more suitable.
Comment by: Elen Glimois (5-12-2017 22:10:16) deeplink to this comment
Hello What a great work I found on your web site !!! I am wondering if your treeview support multiple columns. If it does, how does it work? I would like to implent it on MS Access 2013. Thanks a lot
Comment by: Jan Karel Pieterse (6-12-2017 14:13:23) deeplink to this comment
Hi Elen, Our treeview does not support columns. But there is a listgrid control that we sell which has capabilities that might help?
Comment by: Volker Lorenz (30-1-2018 15:50:34) deeplink to this comment
Hi Jan, I modified your frm_demo and after a short time it worked great! Than I copied clsNode, clsTreeview, modStartup, modDemo and the form into my Access application and at the line: Set mcTree = Me.subTreeView.Form.pTreeview the error occured: Error Load Nr:40036 Discr: Die Methode 'Form' für das Objekt '_SubForm' ist fehlgeschlagen DLLError:0 Context: 104436 Can you please give me an advise, what I have done wrong? Thanks Volker
Comment by: Jan Karel Pieterse (30-1-2018 17:14:39) deeplink to this comment
Hi Volker, In the Access Demo's startup form there is a "How do I?" button which displays important information on how to implement the treeview in Access.
Comment by: shinzi asano form japan (18-2-2018 02:25:43) deeplink to this comment
Nice to meet you About shown Treeviev I thank for precious information. About contents, there might be a better method particularly, but succeeded in hierarchy indication when I did it as follows. I inform it until a report. Will you come better than machine translation? Dim cNode(3) As clsNode '(a): maxLevel Set cNode(1) = cRoot.AddChild(cRoot.Key & "A", "NodeA")  Set cNode(2) = cNode(1).AddChild(cRoot.Key & "_1", "nodeA_1") Set cNode(3) = cNode(2).AddChild(cRoot.Key & "_1.1", "nodeA_1.1") cNode(3).AddChild cRoot.Key & "_1.1.1", "nodeA_1.1.1" cNode(3).AddChild cRoot.Key & "_1.1.2", "nodeA_1.1.2" Set cNode(2) = cNode(1).AddChild(cRoot.Key & "_2", "nodeA_2") cNode(2).AddChild cRoot.Key & "_2.1", "nodeA_2.1" Set cNode(1) = cRoot.AddChild(cRoot.Key & "B", "NodeB") Set cExtraNode = cNode(1).AddChild(cRoot.Key & "_3", "nodeB_1") cExtraNode.AddChild cRoot.Key & "_3.1", "NodeB_1.1"
Comment by: Peter Thornton (18-2-2018 10:43:30) deeplink to this comment
Hello Shinzi, One of the objectives of the simple demo is to illustrate it is not necessary to add nodes in sequential order, several approaches are possible. One of the 'pro' Treeview version demos illustrates an approach that works with an array cNode(0-9), one for each level, like your example. In passing note your declaration cNode(3) is actually cNode(0 to 3).
Comment by: Kerry (13-3-2018 13:03:46) deeplink to this comment
Thanks for providing the control, it fills a real need that Microsoft should have. I want to have the tree control start collapsed, but when I try to have
Comment by: Jan Karel Pieterse (13-3-2018 14:11:00) deeplink to this comment
Hi Kerry, After adding a node, set its expanded property to the value you need, E.g.:
Comment by: Zoltan (23-3-2018 14:27:46) deeplink to this comment
Hello, Is there any posibility to check if a Node already exists before to add it to the tree? Somtimes I have to update some values of a node and not to add it again.
Comment by: Jan Karel Pieterse (23-3-2018 15:02:41) deeplink to this comment
Hi Zoltan, You can traverse through the nodes already there using:
Comment by: Woo (10-4-2018 13:06:52) deeplink to this comment
Hello,, I download "treeview sample Access database" file. And I insert following code into on Load event of "frmDemo" form like this. Private Sub Form_Load() '=========== Original Code =========== AppName = GCSAPPNAME ... '=========== Inserted Code =========== InitializeDemo1 If Not mcTree Is Nothing Then Me.subTreeView.SetFocus End If End Sub Problem is.. I can't move between nodes when mainform load event finished. "Me.subTreeView.SetFocus" code doesn't seem to be working. I wanna move between nodes when mainform load event finished. How can I work around this..? Sorry for my poor english.
Comment by: Peter Thornton (10-4-2018 17:10:15) deeplink to this comment
Hello Woo, It's not clear if you say you've removed all the code in the Load event, I hope not! If I follow you want to show the treeview in the form's load event, and SetFocus to the treeview so you can immediately start navigating between nodes with the arrow keys, or Tab out or F2 Edit, without first clicking the treeview with the mouse(?) When the Load event fires the subForm's window doesn't yet exist, so SetFocus doesn't work. In Excel the Activate fires after the form has fully loaded, but not it seems in Access. Try adding a timer, something like this - Load event ' Me.subTreeView.SetFocus Me.TimerInterval = 500 ' milliseconds Private Sub Form_Timer() Me.subTreeView.SetFocus Me.subTreeView.Form.moSubControl_Enter Me.TimerInterval = 0 ' cancel the timer End Sub In the subFrom change Sub moSubControl_Enter from Private to Public Maybe Access experts can suggest a better way, we are Excel guys!
Comment by: Woo (11-4-2018 02:23:52) deeplink to this comment
Hello Peter. I executed your solution. It works well. Thank you for your help and explanation. As you said, It seems that there is no form_load_finished event in access. Additionally, I find another imperfect solution. --- another workaround --- 1. Main form(form load event) Insert following code at the end of the original code InitializeDemo1 2. Main form: Add textbox(with tab index = 0, tab stop = true) 3. in added textbox(got focus event) Insert following code. SendKeys "{TAB}" Have a good day, Peter.
Comment by: Yuan Yao (15-5-2018 20:59:46) deeplink to this comment
Hi Peter, this is absolutely a masterpiece to me! Thanks for all the work! A quick question, how do i dump only the data I checked/ticked?
Comment by: Peter Thornton (16-5-2018 10:07:38) deeplink to this comment
Thank you Yuan Yao, but note of course Jan Karel is also co-author In the demo form, adapt Sub GetData1 or GetData2 like with something this
Comment by: Simon (26-5-2018 12:30:30) deeplink to this comment
Hi Been using your treeview for a while now in some PPT projects. Have to say is brilliant and I have even managed to customise it a bit, added drag drop and a small/large option for the icons/text. I have a new weird issue I can't pin down. With the macro running if any of the methods that alter the frame's controls collection (add or remove) are triggered then the VBA Toolbox appears on the screen I found a mention of this relating to something else and their solution was to make sure the VBA Toolbox was on the same screen as the VBA Editor and close it before saving. Tried that and it works sometimes but not others. The VBA Toolbox doesn't always come back on the same monitor either (I have 3 monitors). If I then test the created Add-In on a single screen the Toolbox appears on the screen. Tried using a FindWindow sub to pick up the Toolbox so I could force it closed but not able to find it. Has anyone had this issue? It may be something to do with my machine but fresh install of Office (2013) hasn't cleared it. Any help or clues appreciated! Thanks Simon
Comment by: Jan Karel Pieterse (27-5-2018 11:31:30) deeplink to this comment
Hi Simon, I would try removing the VBE entry in the registry (export its key first!!). It would be something like: Computer\HKEY_CURRENT_USER\Software\Microsoft\VBA\7.1\Common AFAIK the VBA editor will add it back with its default settings. The 7.1 might be different for 2013.
Comment by: Simon (29-5-2018 11:14:08) deeplink to this comment
Hi Jan Karel Thanks for your quick response and suggestion. Tried that. 7.1 was the highest key avail so I backed it up and deleted it. 2013's VBA reset to defaults and new key was created. So all good there. Unfortunately it hasn't shaken the problem. I decided to start from scratch and tried a test VBA add-in with a form with buttons that altered the form's controls collection and added a simple treeview (usaing same version of classes as main project). Still no toolbox. Going back to my project, I realised I also have a treeview as part of the "open" code as an option - the user sees the source templates "folder structure" as a treeview. That code used AddChild etc., same treeview classes but no toolbox there. So, the issue is something to do with the main form where users click to add nodes to their "chosen slides" list (treeview) selecting from the "available slides" (treeview). Still can't imagine quite what is going on to make a toolbox appear though! I appear to have created a problem in VBA. I will continue to investigate! Thanks Simon
Comment by: Simon (29-5-2018 11:44:29) deeplink to this comment
Hi again OK, pinned down the issue I think. Stepping the code I found it wasn't adding the node that causes it but part of the refresh "wrapper" I put around a user choosing a slide. Specifically setting focus onto the Chosen node (frame "fraChosen.SetFocus") I destroyed the frame and added a new one and the problem seems to have gone away. I swear last week when I stepped it, the toolbox appeared right on the Controls.Add line so maybe the registry edit has made a difference too. VBA not doing what it should do regardless! Thanks for your help Simon
Comment by: Jan Karel Pieterse (29-5-2018 12:53:52) deeplink to this comment
Hi Simon, I guess this is just "one of those things" we have regularly when developing in Excel VBA :-)
Comment by: Peter Thornton (31-5-2018 08:49:26) deeplink to this comment
Hi Simon, Try deleting (or rename) your toolbox file, for your Powerpoint look for Powerppt.box. It should rebuild itself but you'll need to re-add any 'additional controls'.
Comment by: Arlyn (10-6-2018 14:21:15) deeplink to this comment
In playing around with the control for an Access database I noticed that the scrolling feature does not work. Is that something that just needs to be turned on, or does that control simply not work for this?
Comment by: Peter Thornton (11-6-2018 09:27:12) deeplink to this comment
Hi Arlyn, Wheel scroll is not implemented in the free version, it's not included in the original MSComctl.ocx version either. If it's important for you might be interested to look at our 'pro' treeview, more details at the top of this page.
Comment by: Sasha (9-7-2018 07:37:05) deeplink to this comment
Hi, I use MS Access 2007, on 32-bit Windows 7 machine. When trying to use access version of Your treeview, I'm getting a error message : 'Run-time error : 2926 Because of your security settings and current security policy, this control is disabled. To modify your policy and enable the database, use the Message Bar.' What to do to make it work ? Thanks in advance.
Comment by: Jan Karel Pieterse (9-7-2018 09:55:29) deeplink to this comment
Hi Sasha, WHen you open a database, often you get a yellow bar beneath the ribbon which contains a button to enable content. Click that button.
Comment by: Peter Thornton (9-7-2018 10:19:07) deeplink to this comment
Hi Sasha, Just to add, the demo form includes an MSForms.Frame control with a number of image controls as one way to store image objects for icons in the treeview. The demo also includes three MSForms spin controls, these are only to change node height, node width and font size and only to illustrate the treeview. If your security settings disable controls without notification, in the demo form comment all lines that refer to mfrImages (the frame control) and axSpinButton,1,2&3 (the spinners). You will not be able to run demo2 with icons demo, but there are other ways to store and retrieve icon images. The treeview is not a control and it doesn't need these controls to work. These MSForms controls are considered as "built-in" in other Office applications such as Excel and Word. Simplest way to enable them in Access is to change your security settings to show a warning prompt with controls.
Comment by: Sasha (9-7-2018 10:42:36) deeplink to this comment
Hi, Thanks for quick response. All MS ACCESS 2007 settings in Trust Center are adjusted to accommodate work with macro's and vba code without restrictions. Code crashes with error 2926 in Form_Load procedure of the frmDemo form:
Comment by: Sasha (9-7-2018 13:28:12) deeplink to this comment
Hi Peter, I followed your instructions and commented out all references of the mfrImages instances, and managed to create new form with subTreeView as subform. I then copied all the code from frmDemo to new form, and did some deleting mainly because I defined TreeView properties directly in the code and not by having the check boxes and combo boxes on the form to select values for the properties from. Everything is looking good, beside fact that loading of subTreeView subform causes message from Acess stating that my security settings disabled control on this form. That is if I open my form with subTreeView as subform directly. If I do it by calling the code from modStartup where I did change form name to my form name, then I do not get the security warrning message. Maybe I'm still missing something?
Comment by: Peter Thornton (11-7-2018 16:06:52) deeplink to this comment
Hi Sasha, If your security settings block all controls I don't think you needed to create an entirely new form, just remove all code references to the MSForms controls (the Frame of image controls on the subForm and spin-buttons on the main form), and delete the controls. Indeed you'd have got warning trying to open the subForm if you hadn't deleted the Frame, but interesting you could open it with code. Just to clarify, it's not security's Macro settings but ActiveX settings which if disabled blocks these controls. In passing, get the Excel download with v026, copy code in the class modules, and debug/compile.
Comment by: Stefano (16-7-2018 09:24:14) deeplink to this comment
Hi Peter, thankyou for sharing this great alternative. I'm trying to convert my ActiveX TreeView into this project, but I'm stuck with the references of my accdb. All the events of the form "subTreeView" give this error: "The Expression On Load you entered as the event property setting produced the following error: User-defined type not defined". I am missing something? I am on Win7 x64 and running Ms Office 2013 32bit. This is the list of references of my accdb: MSComctlLib | C:\windows\SysWOW64\MSCOMCTL.OCX Office | C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\MSO.DLL DAO | C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\ACEDAO.DLL Scripting | C:\Windows\SysWOW64\scrrun.dll MSForms | C:\windows\SysWOW64\FM20.DLL SHDocVw | C:\Windows\SysWOW64\ieframe.dll stdole | C:\Windows\SysWOW64\stdole2.tlb Access | C:\Program Files\Microsoft Office 15\Root\Office15\MSACC.OLB VBA | C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL Thankyou
Comment by: Peter Thornton (16-7-2018 15:35:12) deeplink to this comment
Hi Stefano, The only additional reference our Treeview requires is to MSForms, and it looks like you've got that. Have you tried Debug / Compile? Often that will highlight compile errors which would trigger that message, but there might be other things. If/when you've successfully adapted best to remove the ActiveX control from your form and the MSCOMCTL reference. I suspect the SHDocVw would fail fail in Access 64, not sure if there's an alternative but that's a different matter.
Comment by: Yair (2-8-2018 14:25:34) deeplink to this comment
Hi guys, This is amazing - converted it to Access 2016 and it works beautiful out of the box! Is there a way to change it to display the lines RTL, so it can be used for Hebrew content as well? Thanks again for the great work!
Comment by: Peter Thornton (3-8-2018 12:38:32) deeplink to this comment
Hi Yair, Userforms have a RightToLeft property. I can't apply the setting but maybe you can - in the subForm's Load event
Comment by: Yair (3-8-2018 15:56:44) deeplink to this comment
Hi Peter and thank you for your reply. I tried applying the RTL property to the mUF (in both places in the load event) and also changed the forms's orientation property to RTL but it didn't help. The problem is not flipping the text (this is handled well by Windows and Office), but to display the tree lines going from the right side toward the left side. I did put in the code the confirmation lines and it reported back OK: Debug.Print mUF.RightToLeft Debug.Print Me.Orientation
Comment by: Peter Thornton (3-8-2018 17:03:22) deeplink to this comment
The documentation about exactly what the userform's RightToLeft property actually changes is very vague, except apparently showing vertical scrollbars on the left - does it? If as you say Windows handles the RTL text in Label captions, perhaps all that's left to do is to flip all the controls to the right side. If controls' Left values are still measured from the left, the mirrored left might be something like (frControl.ScrollWidth - orig-Left). Or if ScrollWidth is zero use InsideWidth. However I've no idea how a horizontal scrollbar would impact things. Also you say (just) the lines, are you saying the Label captions get automatically flipped but not the lines, if so maybe it's only the Lefts of the lines that need recalulating. As I can't test the RTL property I'm guessing in the dark! Try experimenting in say Excel. On a userform place some Labels on a Frame control, see what it takes to flip them, particularly with a horizontal scrollbar.
Comment by: Jan Mellema (4-8-2018 09:50:37) deeplink to this comment
Goede morgen uit Leipzig! ik zoek naar een mogelijkheid bij treeview met mouse-over additionele en verdiepende informaties te geven over de node die zo wordt geraakt. Is dat mogelijk/hoe kann dat mogelijk worden gemaakt?
Comment by: Jan Karel Pieterse (4-8-2018 20:35:19) deeplink to this comment
Hoi Jan, Je zou zelf een eigenschap kunnen toevoegen die de controltiptext vult van de controls van de node.
Comment by: Ian Digby (15-8-2018 11:21:59) deeplink to this comment
This treeview control is absolutely great. Much faster to render a tree than the 32 bit MS Treeview control. Similar in syntax, so easy to learn if you've used the latter, and very thorough and feature-rich. I will be making a Paypal donation, and thanks for an awesome effort and for generously making it available. "The earth is but one country, and mankind its citizens" - Baha'i Writings.
Comment by: Jan Karel Pieterse (16-8-2018 10:42:24) deeplink to this comment
Hi Ian, Thanks!
Comment by: Luigi (31-8-2018 21:46:03) deeplink to this comment
Hi guys, this is absolutely amazing control! I am using it with Access 2016 64bit and it works beautifully and it is very fast. I know this is a trivial issue, but I am struggling with trying to get the treeview border to look flat rather than sunken. I have tried changing the border property to "transparent" and specialeffect property to "flat" everywhere but with no success...any help? I'd like to make it look like the XER toolkit in the example list of your website. Thank you
Comment by: Jan Karel Pieterse (3-9-2018 10:23:30) deeplink to this comment
Hi Luigi, You change that by changing the appearance of the frame you placed on the userform yourself (the one called frTreeControl in the Excel demo).
Comment by: Hartmut Gruenhagen (12-9-2018 09:22:51) deeplink to this comment
A suggestion to enhance the FullPath function for some compatibilty with the fullpath property in the ms treeview control: Public Enum ndPathType ndKeys = 1 ndCaptions = 2 End Enum Public Function FullPath(Optional ByVal ndType As ndPathType = ndKeys) As String ' PT, get all the grand/parent keys ' assumes use of key Dim s As String Dim cNode As clsNode On Error GoTo errDone Set cNode = Me If ndType = ndKeys Then s = Me.Key While Err.Number = 0 Set cNode = cNode.parentNode s = cNode.Key & "\" & s Wend Else s = Me.Caption While Not cNode.parentNode.Caption = "RootHolder" Set cNode = cNode.parentNode s = cNode.Caption & "\" & s Wend End If errDone: FullPath = s End Function
Comment by: Jan Karel Pieterse (12-9-2018 13:48:07) deeplink to this comment
Hi Hartmut, Thanks!
Comment by: Louise Dinkelmann (27-9-2018 15:50:48) deeplink to this comment
Good day. I have used the control and it works fantastic. However when I install a tested, working copy of my Access database on another laptop I get Access error 2683 There is no object for this control when I try to open the form with the treeview. What could be the reason for this error? Any help will be appreciated. Regards.
Comment by: Jan Karel Pieterse (27-9-2018 16:34:05) deeplink to this comment
Hi Louise, The error might be due to ActiveX controls disabled in security settings, prompted by the Frame and image controls on the demo form used to replicate an 'ImageList'. If changing the security setting fixes but normally can't do that, there are other ways to store images as illustrated in the 'pro' demos.
Comment by: Blaine (1-10-2018 14:40:42) deeplink to this comment
Good day, I have one query about the enter activity. When the tree is active and after you press enter on the keyboard, all other action is unavailable(nodeclick,rightclick...) How to stop it from not working? Many thanks
Comment by: Jan Karel Pieterse (1-10-2018 15:11:07) deeplink to this comment
Hi Blaine, I am not sure I understand?
Comment by: Blaine (1-10-2018 16:45:02) deeplink to this comment
Hi Jan, So when I open the tree and click one node it is all fine. And then I press enter, and the tree seems to collapsed and all the click and right click stop working. Is there any changes i can make to avoid enter button?
Comment by: Jan Karel Pieterse (1-10-2018 17:02:43) deeplink to this comment
Hi Blaine, You can of course make sure enter is not handled by the treeview. You can do so by editing the routine TreeControl_KeyDown in clsTreeView and disabling this bit:
Comment by: DJISOR (6-10-2018 12:07:20) deeplink to this comment
hi, great job ! just a little issue with access 2013-windows 7 (64 bits) : *********** the lines in autoexec macro must be : [CurrentProject].[Application].[Version]>="12" and not [CurrentProject].[Application].[Version]>=12 (error 13 Type Mismatch) ********** same in form_frmHelp form_load sub : If Application.Version >= "12" Then and not If Application.Version >= 12# Then
Comment by: Peter Thornton (8-10-2018 10:37:40) deeplink to this comment
Hi DJISOR, Normally the 'string number' should coerce without a problem for the comparison with the numerical value. Does the following work for you in the Immediate window: ?"13.0" > 12 If that works what does this return: ?Application.Version If it includes any non numerical characters that would explain why you get the mismatch error, unusual though. Whatever the reason we can't change it as we can no longer compile an MDB that will work for 2003, which is also why the v.025 in the current Access download was not updated to the slightly later v.026. Time has moved on so perhaps it's time we update it to an ACCDB for use in 2007 or later, in which case the If >12 app' version checks would not be required and can be removed.
Comment by: DJISOR (8-10-2018 23:59:49) deeplink to this comment
for me : ? "13.0" > 12 -> type mismatch curiously, the Luigi comment (8/31/2018 9:46:03 PM) says "I am using it with Access 2016 64bit and it works beautifully ". thanks guys !
Comment by: Jan Karel Pieterse (9-10-2018 07:09:17) deeplink to this comment
Hi DJISOR, This is a problem with the implicit conversion from string to number. You obviously are not using the period as the decimal separator and hence VBA fails to convert "13.0" to a valid number. The way around this is to use the Val function around the version string, which takes as many characters as it can to construct a number from the string (if the decimal separator differs, it just takes the whole number):
Comment by: Filipe Jose Roque Caetano (12-10-2018 11:47:30) deeplink to this comment
Hello from Portugal. Thank you for this fantastic treeview control. I've been able to change the way that you populate the treeview from a table i MS Access, although I can only go up to level 6 (after that level of disaggregation the treeview don't display that nodes anymore) Here it is for anyone that find it useful.
Comment by: Filipe Jose Roque Caetano (12-10-2018 11:48:04) deeplink to this comment
--Continued from previuos comment-- The structure of the table is something like this: ID | Parent | Designation 1 | 0 | Root1 2 | 0 | Root2 3 | 0 | Root3 4 | 1 | Root1_Child1 5 | 2 | Root2_Child1 6 | 2 | Root2_Child2 7 | 2 | Root2_Child3 8 | 6 | Root2_Child2_Child1 9 | 8 | Root2_Child2_Child1_Child1 10 | 3 | Root3_Child1
Comment by: FILIPE JOSE ROQUE CAETANO (12-10-2018 12:02:46) deeplink to this comment
Hello again. I forgot to mention that the recordset is based on a query "qryMenu" that, in turn, is just a way to sort the table that is showed on the last post. Regards,
Comment by: Peter Thornton (12-10-2018 17:37:23) deeplink to this comment
Hi Filipe, There's no defined limit to the number of levels, I can't see from your code why you can't create more than 6 levels, something in the data perhaps(?). If your nodes will always be added in sequential order, or at least such that any parent is created before any of its child nodes, you might find it easier to use the treeview.NodeAdd rather than node.AddChild. Add root nodes in the normal way with AddRoot. Be sure to give each a unique key, its ID perhaps (if necessary converted to a string if the ID field is numeric). Then for all other nodes
Comment by: Filipe Caetano (15-10-2018 09:47:52) deeplink to this comment
Thank you so much Peter for looking at my code. I'll try to implement your suggestion. As for passing level 6, I think it should in fact be a problem with my data / table, but I can not figure out how to overcome this problem. As I enter more levels in the treeview, it will display them, but I can not register those levels in the table. It's a strange thing, considering that up to level 6 I have no problems. The table and the data are a simple structure, like the one presented in one of my previous posts. greetings
Comment by: Peter Thornton (16-10-2018 12:06:09) deeplink to this comment
Hi Filipe, It's probably something obvious in your data/table, but sometimes they can be the hardest things to find! Try debugging your code, eg Set cChild = cNode.AddChild( arg's) With cChild Debug.? .ParentNode.Key, .Level; .Caption End With
Comment by: CARLOS ANTONIO (16-10-2018 20:22:27) deeplink to this comment
Really this is a job "SPECTACULAr", my congratulations! It is impressive the level of knowledge of those involved through the codes developed and the final result of the solution presented. After that I learned that I have not learned anything yet from VBA ... In my limitations of the tool and my knowledge, it would be possible for those involved to provide a small code of what a tree of three nodes would look like in TreeView. Taking as an example a simple spreadsheet with 3 columns. Where column 1 being the YEAR, column 2 being the MONTH, and column 3 being the VALUE OF SALE. In this case you would create a parent node being a year, then a child node for the month, and then add the value for each month related to the YEAR of the parent node. I do not know if I was clear in my comment, and if they can make the code available here to clarify the doubts of the other colleagues, or if they can send me in my e-mail. Right now, thank you very much for the material provided and applied knowledge. It will be very useful for my applications
Comment by: Filipe Caetano (18-10-2018 12:39:43) deeplink to this comment
Thank you once again Peter. I'll try your sugstion a see if that do the trick! Regards
Comment by: Jan Karel Pieterse (18-10-2018 17:00:05) deeplink to this comment
Hi Carlos, Perhaps this comment helps: https://www.jkp-ads.com/articles/treeview.asp?AllComments=True#25809
Comment by: Filipe Caetano (23-10-2018 21:40:02) deeplink to this comment
Hello again :) I have been wondering if it is possible to display a count of nodes next to each parent node. Thank you very much!
Comment by: Jan Karel Pieterse (24-10-2018 09:44:11) deeplink to this comment
Hi Filipe, Do you mean the number of child-nodes of a node? What to do with children of child-nodes, do they count with only their parent or also with the parent of their parent node?
Comment by: Filipe Caetano (24-10-2018 09:58:15) deeplink to this comment
Hello Jan. Thank you for showing interest in my question. What I am trying to do is recursively count the number of grandchild nodes whose child nodes will add up to that count and, in turn, parent nodes will add up the count of child nodes. However, I do not want to duplicate counts, for example: Parent node (7) > Child node (3) >>Grandchild node >>Grandchild node >>Grandchild node > Child Node (4) >>Grandchild node (2) >>>Grandgrandchild node >>>Grandgrandchild node >>Grandchild node >>Grandchild node >>Grandchild node Thank you in advance for any assistance on this matter
Comment by: Jan Karel Pieterse (24-10-2018 10:28:39) deeplink to this comment
Hi Filipe, Code like this adds the # of childnodes to the parent nodes:
Comment by: Filipe Caetano (24-10-2018 10:31:26) deeplink to this comment
Thank you very much Jan. I didn't thought it was so simple :) Best regards
Comment by: Filipe Caetano (24-10-2018 17:21:26) deeplink to this comment
Hi Jan, Thank you very much. I was able to add the nodes count to the treeview, but I'm not happy with aesthetics. Instead of showing the count at the beginning of the node's caption, is there any way to put that count at the right side limit of the treeview's frame? Example:
Comment by: Jan Karel Pieterse (24-10-2018 17:45:19) deeplink to this comment
Hi Filipe, The challenge will be to figure out where to put the number. First of all, you should set a non-proportional font for the entire tree, otherwise there is no way you can accurately align the characters. Then you would have to determine the maximum length of all nodes captions in the same level of the tree (with the same number of parents) and use that value to decide how many space characters need to go between the current caption and the node count.
Comment by: Filipe Caetano (24-10-2018 17:54:10) deeplink to this comment
Thank you very much Jan. You have put me in the right track once again. I will try to develop your idea, trying to overcome yet another problem that will be the indentation of nodes captions. Regards,
Comment by: Eduardo Bernal (27-10-2018 19:48:04) deeplink to this comment
Hi everybody My name is Eduardo. I´m from Spain. First of all, my congratulations for this great and powerful code. After reading the comments I´m trying to insert the treeview in my database using the code provided by Filipe Caetano (thanks Filipe!), in order to associate each node in the treeview with a record from a table. For now, it is working, but I´m not able (I´m just a beginner in access vba code) to edit and change records in the table by changing the node caption in the treeview. This functionality would be great to edit and update any data in the main table just changing the caption node in each case. If someone has found a way to do so, please mail me an example to study and understand the code. Sorry for my pour English and thanks in advance,
Comment by: Jan Karel Pieterse (29-10-2018 11:11:45) deeplink to this comment
Hi Eduardo, I hope someone will respond to your question as I don't happen to have examples lying around :-)
Comment by: Matthew Nicoll (30-10-2018 23:49:14) deeplink to this comment
Thanks Jan, for helping us all with your TreeView code. With clsTreeView build 026, in Excel 2010 VBA on Windows 7 I successfully replaced the Microsoft treeview control in my Excel VBA project. I had one problem, described below, and I made a few simple additions, which I'll put in following comments. I got error "Object variable or With block variable not set" on statement: With ActiveNode.Control in Sub : ActiveNodeColor in class mod: clsTreeView ... because ActiveNode.Control was = nothing. Here are my relevant VBA statements:
Comment by: Matthew Nicoll (31-10-2018 00:04:45) deeplink to this comment
Here are a few simple enhancements I made to Build 026 of clsTreeview and clsNode. (Trivial for those familiar with class modules, but possibly useful for others!) A. To clsNode I added the following property, so that I don't have to check for "ChildNodes Is Nothing" before every reference to ChildNodes.Count:
Comment by: Jan Karel Pieterse (31-10-2018 10:27:23) deeplink to this comment
Hi Matthew, You call NodesClear and then try to modify Nodes(1), which of course is not in the tree as it is empty. So you need to first add nodes to your tree.
Comment by: Jan Karel Pieterse (31-10-2018 10:28:17) deeplink to this comment
Hi Gary, After changing such a property, make sure you refresh the treeview:
Comment by: Jan Karel Pieterse (31-10-2018 10:31:49) deeplink to this comment
Hi Matthew, Thanks for the code!
Comment by: Matthew Nicoll (31-10-2018 18:41:37) deeplink to this comment
Hi Jan, Regarding my yesterday's comment regarding "Object variable or With block variable not set" ... My apologies: I identified the wrong statement as the one causing the error, then I made a mistake copying VBA into the comment! This is what the the VBA snippet should be:
Comment by: Peter Thornton (1-11-2018 09:34:56) deeplink to this comment
Hi Matthew, I haven't had a chance to go through your code but calling Set .ActiveNode will error if .Refresh has not been called to physically create the node controls. You could include error handlers in ResetActiveNodeColor and SetActiveNodeColor, or in each check the control exists before attempting to change its format properties, and/or only call ActiveNode after the node controls exist with Refresh.
Comment by: Filipe Caetano (5-11-2018 13:26:24) deeplink to this comment
Hello Eduardo. Just change “DesignationField” and “TableName” with your own object names. Hope that helps!
Comment by: Aleksandr (9-11-2018 12:23:46) deeplink to this comment
Hello, first of all thanks for the great treeview element . Meantime i've got 1 error and 1 constraint with the treeview. 1. When i initialize userfrom and treeview element on it and want to check some elements that was previously selected element behave improperly by checking not only selected nodes and siblings and sometimes all elements within the list . for checking im using following code :
Comment by: Peter Thornton (9-11-2018 17:09:18) deeplink to this comment
Hi Aleksandr, 1. Your method should correctly show the intended nodes as checked. However if you include the 'triState' option, all descendant nodes of a checked node will be checked, and its parent nodes back to the root will show checked (if all their childnodes are checked) or 'tri-state' checked) if some but not are checked. 2. Normally 400 nodes shouldn't take too long to refresh but the time gets exponentially slower with more nodes. For near instant performance even with several thousand nodes you might want to try our 'pro' Treeview (see above).
Comment by: Eduardo Bernal (11-11-2018 11:58:32) deeplink to this comment
Hi everybody, Finally I could get data from a query in the treeview, by adapting the code provided by Filipe Caetano (thanks again Filipe). Its is working properly so far, showing all the parents nodes(and childs nodes) in each level of the treeview as it was expected, but..., when I add a criteria in the query in order to filter the results to be showed in the treview, suddenly it continues working but only shows the main node and his childs (two levels of teh treeview) an do not show his childs nodes from the third level in advance. ¿any idea to solve this?, thanks Regards
Comment by: Eduardo Bernal (11-11-2018 18:33:32) deeplink to this comment
Hi Filipe!, your code for afterLabelEdit works perfectly. Thank you very much for the code. Regards
Comment by: Jan Karel Pieterse (12-11-2018 10:21:47) deeplink to this comment
Hi Eduardo, Can you perhaps show us some relevant code?
Comment by: Filipe Caetano (12-11-2018 16:35:37) deeplink to this comment
Hello Eduardo. I'm happy to have helped. As for your question, see if you are not breaking the chain structure of nodes and their parents when you filter records in the query. if somehow your filter does not include the entire chain hierarchy of a given node, it will fail. For example: Root> Child> grandchild> grandgrandchild If you filter the query and it does not include the grandchild, you will lose the reference to the child and the Root node. If this is not the problem, please be more specific and, if you can, show an example. best regards
Comment by: Eduardo Bernal (12-11-2018 17:30:23) deeplink to this comment
Hi Jan, I have tried with Filipe code to display nodes from a table, just renaming some parts to suit it to my database, and by creating a recordset from an existing table (tNormativa)using a SQL WHERE clause as a criteria to display only the records that match with a combobox (cbxCategoria) value in the form: Private Function GetDataFromTable() On Error GoTo Error_Trap Dim rst As DAO.Recordset Dim sql As String Dim cNode As clsNode 'cbxCategoria is linked with "Categoria" field from "tNORMATIVA" table: sql = "SELECT * FROM tNORMATIVA WHERE Categoria='" & Me.cbxCategoria & "'" Set rst = CurrentDb().OpenRecordset(sql) Set mcTree = Me.subTreeView.Form.pTreeview With mcTree .NodesClear 'Add Root nodes If rst.RecordCount > 0 Then While Not rst.EOF If rst.Fields("Parent") = 0 Then Set cNode = .AddRoot(rst.Fields("ID"), rst.Fields("Designation")) cNode.Bold = True End If rst.MoveNext Wend 'Add Child nodes For Each cNode In .Nodes rst.MoveFirst While Not rst.EOF If rst.Fields("Parent") > 0 Then If cNode.Key = rst.Fields("Parent") Then cNode.AddChild rst.Fields("ID"), rst.Fields("Designation") End If End If rst.MoveNext Wend Next cNode End If 'Display the tree .Refresh End With rst.Close Set rst = Nothing Error_Exit: Exit Function Error_Trap: MsgBox ("Error Code:" & Err.Number & " Description do Erro:" & Err.Description) Resume Error_Exit End Function
Comment by: Eduardo Bernal (12-11-2018 17:45:42) deeplink to this comment
(continue my last comment:) ...doing so, review works but only displays main nodes and the first level of child nodes, despite existing more child nodes that field Categoria matches with cbxCategoria values, but they are from the third level in advance in the treeview. Nevertheless, If I use a parameter query (qryNormativa) existing in access to create the recordset (DAO), inserting a parameter in "Categoria" field as a criteria to filter just the records matching with "cbxCategoria" value in the form like this: Set rst = CurrentDb().OpenRecordset("qryNormativa", dbOpenDynaset) Then it doesn't work, and display error code 3061 (few parameters) Or else, with the same code, if I use a SQL sentence to define my recordset, but without WHERE Clause, like this: sql = "SELECT * FROM tNORMATIVA" Set rst = CurrentDb.OpenRecordset(sql) Then it works properly and displays every node in every level, but it does not filter by cbxCategoria field. Sorry so much for the mistakes, my vba is as poor as my english.
Comment by: Filipe Caetano (12-11-2018 17:48:11) deeplink to this comment
Hello Peter Thornton and Jan Karel Pieterse. Is it possible to have some of the caption of the node showing a different color as if it were a rich text? I ask this because I have the nodes displaying accounting balances in the mouse movement event next to the node's own caption and would like the negative values ​​to be displayed in red. Thank you very much. Greetings
Comment by: Peter Thornton (13-11-2018 10:47:14) deeplink to this comment
Hi Filipe, The Label control caption does not support mixed font colours or anything like 'rich text'. Although nothing is impossible it would be extremely difficult!
Comment by: Filipe Caetano (13-11-2018 17:55:09) deeplink to this comment
Another contribution I'd like to make is sharing how to search within the treeview. On the form that holds the subTreeView subform add a textbox and on it’s change event call the following function to search a string on the treeview.
Comment by: Jan Karel Pieterse (14-11-2018 10:06:11) deeplink to this comment
Hi Filipe, Thank you!
Comment by: Eduardo Bernal (17-11-2018 12:54:01) deeplink to this comment
Hi Filipe!, ¿any idea for your code to use treeview with data from table (your post 10/12/2018 11:47:30 AM) with a recordset with a parameter query (or a recordset filer with a criteria clause (WHERE) in SQL sentence? Thank you!
Comment by: Eduardo Bernal (18-11-2018 17:55:49) deeplink to this comment
Hi! I keep on trying with Filipe`s code to display data from tables (access), and the problem starts with treeviews with just one root node. For instance, a table with just one root and three levels: ID | Parent | Designation 1 | 0 | Root1 2 | 1 | Root1_Child1 3 | 2 | Root1_Child1_Child1 4 | 2 | Root1_Child1_Child2 5 | 1 | Root1_Child2 6 | 5 | Root1_Child2_Child1 Treeview only displays: Root1 (1st level) Root1_Child1 (2nd level) Root1_Child2 (2nd level) But, the same table with two nodes root o more: ID | Parent | Designation 1 | 0 | Root1 2 | 0 | Root2 3 | 1 | Root1_Child1 4 | 3 | Root1_Child1_Child1 5 | 3 | Root1_Child1_Child2 6 | 2 | Root2_Child1 1 | 6 | Root2_Child1_Child1 Treeview will display all levels: Root1 (1st level) Root1_Child1 (2nd level) Root1_Child1_Child1 (3nd level) Root1_Child1_Child2 (3nd level) Root2 (1st level) Root2_Child1 (2nd level) Root2_Child1_Child1 (3rd level) ¿any idea to solve this? thanks!!
Comment by: Jan Karel Pieterse (19-11-2018 10:34:21) deeplink to this comment
Hi Eduardo, This should work:
Comment by: Eduardo Bernal (19-11-2018 17:49:02) deeplink to this comment
Great Jan! it works perfectly!, I´m very grateful, Thanks a lot. Regards
Comment by: Harun KILIÇ (9-12-2018 18:52:41) deeplink to this comment
Hi. I'm trying to develope an assembly duplicator for Autodesk Inventor, I used your codes for custonm treeview control. You have mentioned that mouse wheel scroll hook is available with in paid version. How much is it? Thanks.
Comment by: Jan Karel Pieterse (10-12-2018 09:37:08) deeplink to this comment
Hi Harun, You mention Autodesk, are you planning to use the treeview in Excel VBA or in another program? The pro controls are only available in Excel and Access.
Comment by: Harun KILIÇ (10-12-2018 18:28:12) deeplink to this comment
Hi Jan, I already used your treeview codes in Inventor VBA. This is the screen capture of userform; May be you can create a dll only for wheel scroll hook methods; for a fee; if possible. Thanks.
Comment by: Jan Karel Pieterse (11-12-2018 10:25:52) deeplink to this comment
Hi Harun, Your image URL leads to a site which displays as "Forbidden", so I have removed it from your post. In which application did you implement the treeview precisely?
Comment by: Danny (22-12-2018 03:38:06) deeplink to this comment
Hi! It's amazing job you did and shared to us. I'm grateful. I have a question that we can use the button 【Allow Edition】 to edit the name of label,but how to save the the data when we finish edit. Is there have this function in this Demo Treeview or need to programming. Briefly, I want to know that if the user (not programmer) "after label edit". How can they save the data? Because If the user presses the button 【Demo1Treeview】, then all the things starts again!! Thanks and Merry Xmas.
Comment by: Peter Thornton (22-12-2018 12:33:22) deeplink to this comment
Hi Danny, You can trap individual edits in the 'AfterLabelEdit' event which returns the node object and the new edited caption. You could automatically update the source data with the new caption in real time in this event. You can update all data by looping nodes with a recursive type function. You could provide a button for your user to update some or all data, see the example in the demo behind the 'Dump Data' button. Alternatively you could trap the form's QueryClose event and save all data when the form closes.
Comment by: ANDRÉ LUIS RODRIGUES DOS SANTOS (28-12-2018 15:56:56) deeplink to this comment
Good afternoon, I've implemented some situations to view hierarchically without treeview and it worked fine, I just have one question? Note also that the mause wheel was not able to scroll the expanded data view in the treeview, was this implemented or is it not currently possible?
Comment by: Peter Thornton (28-12-2018 16:58:45) deeplink to this comment
Hi André, It is very hard to make wheel scroll reliable in the 'free' treeview and best not to try implementing it unless for your own use only. Wheel scroll is implemented in the 'pro' treeview.
Comment by: ANDRÉ LUIS RODRIGUES DOS SANTOS (29-12-2018 14:59:21) deeplink to this comment
I noticed that when I click 2 times on one it does not open the children, it has how to do this without using with object. ShowExpanders = false?
Comment by: Peter Thornton (31-12-2018 12:13:04) deeplink to this comment
Hi André, I'm not sure I follow, do you mean you want to be able to double click on a node to collapse/expand and not show expanders? If so, in the mctlControl_DblClick event comment the existing code (to enter edit mode) and copy the code from the mctlExpander_Click event. If that's not what you mean please explain with more detail.
Comment by: Guillermo (21-1-2019 20:40:32) deeplink to this comment
Hello Peter and Jan. Thanks for your product. It's great for me! I detected a problem only in Access (no Excel), different versions and machines. It occurs in all applications, also in the "Treeview-26.mdb" demo. While you are working with treeview, if you change the active application (for example, using the taskbar go to Windows Explorer), when you return to the treeview application (click again on the taskbar), it will not get active before the first click, you must click three times on the taskbar for it. That happens when you have operated the treeview (for example, expand/contract a node) before switching to another application. Therefore, if you go out from the treeview application without any action, the problem does not occur. Again, thank you very very much! Best Regards, Guillermo.
Comment by: Peter Thornton (22-1-2019 11:45:50) deeplink to this comment
Hi Guillermo, I understand what you are describing, particularly while developing and switching to the VBE. In the 'free' version it's complicated to change that behaviour due to the particular arrangement of windows with Access, it's OK in the 'pro' version.
Comment by: Guillermo (23-1-2019 18:03:41) deeplink to this comment
Hello. Thanks for your reply. Due to the issue that I have posted and other features (mousewheel scroll, add/edit image in Access, etc) I'm interested in the ‘Pro’ version. How can I do to test/acquire it? Best Regard, Guillermo
Comment by: Barati Mohammad Reza (10-2-2019 22:31:36) deeplink to this comment
HI I've used your ACCESS TREEVIEW but I can not create a search that when I search for a word and find two or more similar words, it will first focus the first one and then by click a button focus next and next Please guide me
Comment by: Peter Thornton (11-2-2019 10:33:48) deeplink to this comment
Guillermo contacted us via the 'Pro Treeview Enquiry' link above, it is now fully implemented in his application.
Comment by: Peter Thornton (11-2-2019 10:43:57) deeplink to this comment
Hi Barati Mohammad Reza, See 'Extended Project Explorer' in the examples link. Note this example uses an earlier treeview version. Find match and find next is included in the 'pro Treeview'
Comment by: Omid (16-2-2019 09:20:00) deeplink to this comment
Hi, Thanks for your codes! Is it possible to create a rtl Tree? does it support it? Best regards, Omid
Comment by: Jan Karel Pieterse (16-2-2019 11:45:15) deeplink to this comment
Hi Omid, What is an rtl tree?
Comment by: Omid (16-2-2019 13:26:27) deeplink to this comment
Thanks for reply! I mean right to left view for non-English languages.
Comment by: Peter Thornton (18-2-2019 18:47:19) deeplink to this comment
Hi Omid, I am a co-developer of the treeview. It does not support 'right to left' view and we have no plans to adapt it. It would be difficult without a 'rtl' system available. Peter Thornton
Comment by: MDrost (5-3-2019 16:02:12) deeplink to this comment
Thank you, this works very smooth!... Good documentation too. Now browsing for a datetimepicker ;)
Comment by: Jan Karel Pieterse (5-3-2019 16:13:48) deeplink to this comment
Hi MDrost, There is a link to a very good one on this page!
Comment by: Bernd Reber (9-5-2019 02:00:00) deeplink to this comment
I got excited to see this, but sadly I'm running into a lot of errors. It starts when I try to open the demo DB with Access 2003: "Userform ufTreeView does not exist in this VBA Projet. Create it?" When I do this, run-time error 2683 occurs (there is no object in this control) and the debug line that comes up is
Comment by: Peter Thornton (9-5-2019 10:20:00) deeplink to this comment
Hi Bernd, The demo form includes some embedded MSForms controls, so your security settings will need to enable ActiveX controls. If that's not the problem contact me off-line and I will send you an earlier version which hopefully will work for you.
Comment by: Robert Helgeson (9-5-2019 23:05:00) deeplink to this comment
First, thank you for the MS Access treeview control. I was able to easily incorporate the functionality into my code. Is it possible to make the mouse scroll wheel work inside the treeview control?
Comment by: Baudi Deckers (10-5-2019 11:44:00) deeplink to this comment
Hi, many thanks for your excellent tutorials, as this on the Treeview control. My question: I have to load over de 1.600 records in the tree. I use the DoCmd.Echo to hide (and accelerate) the loading. But when the loaded tree appears after a few seconds, the tree is rather centered, instead of fully on the left; and on the last record. I would like the tree to appear on the first record and as left as possible. Any advise ? I appreciate!
Comment by: Peter Thornton (10-5-2019 11:46:00) deeplink to this comment
Hi Robert, Although possible I don't recommend including wheel scroll, and definitely not in 64 bit. If interested it is included in the 'pro' version (see above).
Comment by: Peter Thornton (10-5-2019 12:01:00) deeplink to this comment
Hi Baudi, Normally the tree and nodes should appear where and as expected, I'm not sure why for you it becomes centered or quite what you mean by 'on the last record' (unless you set the last node as the activenode). If you want you can send me a screenshot off-line. As far as the treeview is concerned I wouldn't expect use of DoCmd to make a difference reduce load time. In the free version time is mainly involved with the creation of controls as individual components, with 1600 nodes that could be several times that quantity and would take a while. FWIW the pro is designed differently, 1600 nodes would take an eye blink to create.
Comment by: Andrew (30-5-2019 11:16:00) deeplink to this comment
Hi This is a great and very welcome solution. 2 questions: 1. How do I allocate different background colours to nodes based on specific data values for that node eg. status 2. How do I open a related pop-up form based on the node index. Best Regards
Comment by: Salim Talal (30-5-2019 22:31:00) deeplink to this comment
Am trying to download but error popping up and terminating download.
Comment by: Jan Karel Pieterse (31-5-2019 11:50:00) deeplink to this comment
Hi Salim, Which error do you get during the download? You can find example code in the demo file. The NodeRemove example is in the ufDemo form's code, in the mcTree_KeyDown event.
Comment by: Jan Karel Pieterse (31-5-2019 16:09:00) deeplink to this comment
Hi Andrew, The clsNode class has a BackColor property you can use to set the node color. If you click a node, the tree's click event is triggered, giving you the clicked node, see ufDemo in the demo file:
Comment by: Andrew (7-6-2019 11:40:00) deeplink to this comment
Really impressed by your treeview solution! I highlight the selected node by setting .Bold to true. Is there a more direct way to set the previously selected node to .Bold = False ? At the moment I am recreating the entire tree but then the focus is lost on the selected node. Best Regards Andrew
Comment by: Jan Karel Pieterse (7-6-2019 13:34:00) deeplink to this comment
Hi Andrew, Does the current background color highlight work not for you?
Comment by: Bob H. (26-6-2019 21:11:00) deeplink to this comment
I'm using the free version of TreeView for MS Access. I added a Search routine so the user can type in the name of the last child node and select it. It works fine except the child node is outside the frame. Is there a way to automatically move the selected node to the top of the frame?
Comment by: Peter Thornton (27-6-2019 10:12:00) deeplink to this comment
Hi Bob, Assuming you have set the found-node as the activenode try this:
Comment by: Bob H. (27-6-2019 13:32:00) deeplink to this comment
Thank you for your response. It worked well. 1 more question: How can I set the focus to the treeview when the form is initially displayed?
Comment by: Jan Karel Pieterse (27-6-2019 15:53:00) deeplink to this comment
Hi Bob, You can set focus to the frame which contains the treeview.
Comment by: Peter Thornton (27-6-2019 16:09:00) deeplink to this comment
Bob, just to add, in Access try this
Comment by: Bob H (29-7-2019 17:12:00) deeplink to this comment
Is there a way to default the treeview to expand to a certain level. Currently it is fully expanded and I would like it to expand to level 2.
Comment by: Peter Thornton (29-7-2019 17:52:00) deeplink to this comment
Hi Bob, By default the Expanded property of nodes is True. While populating if you retain a reference to each new node (if not sure this node level is >2 uncomment the If line)
Comment by: Carlos (30-7-2019 17:07:00) deeplink to this comment
what do we need to add to this code to turn it into a Virtual treeview? I have hundreds of Nodes and it can be really slow to upload. Many thanks
Comment by: Peter Thornton (30-7-2019 20:06:00) deeplink to this comment
Hi Carlos, Not calling .Refresh would give you a sort of virtual treeview, but without knowing your overall objective hard to suggest more than simply that. If you're dealing with larger treeviews you might consider the pro Treeview, it can easily handle tens of thousands of node. It also supports virtual treeviews, mainly to facilitate a multi-level undo for treeviews. If interested see the enquiry link above.
Comment by: Mads Berge-Lind (13-8-2019 08:36:00) deeplink to this comment
I am super happy with the 'pro' Treeview and its developers! The control is very quick, and it has all the functionality I need. I am running up to 24 simultaneous trees with thousands of nodes each, and drag and drop between them. The support is just fantastic. Peter has modified the control over a weekend to add functionality. Having this kind of support is great. Thank you!
Comment by: Peter Thornton (13-8-2019 13:15:00) deeplink to this comment
Hi Mads, Thank you for this nice feedback:)
Comment by: Jamie (4-9-2019 21:49:00) deeplink to this comment
In Access, I have the Treeview subform as per the example and the "Public WithEvents mcTree As clsTreeView" in the Parent form. I do everything I wanted to do perfectly, from the parent form. However, I have another subform on the same parent (call it a sibling form to the Treview subform??) and I want to adjust the TreeView nodes from this subform. How do I reference the existing Treeview from the sibling subform.
Comment by: Peter Thornton (5-9-2019 12:17:00) deeplink to this comment
Hi Jamie With the mcTree reference in the main form declared as Public, in the subForm refer to it simply as 'Me.Parent.mcTree', for example -
Comment by: Filipe Caetano (6-9-2019 11:10:00) deeplink to this comment
I need to create a child node inside another node. My code is:
Comment by: Peter Thornton (6-9-2019 11:44:00) deeplink to this comment
Hi Filipe I don't quite follow all of what you are doing but to add a childnode to the activenode and put it in editmode ready for editing adapt the following
Comment by: Filipe Caetano (20-9-2019 12:27:00) deeplink to this comment
Hello. Is there a better alternative (simpler or faster) to navigate to the node above/under the active node, than this? Navigate to the next/under node:
Comment by: Jan Karel Pieterse (20-9-2019 15:20:00) deeplink to this comment
Hi Filipe, I recommend using the NextVisibleNodeInTree function of the tree class. The only problem is that this function currently is private so you would have to change that to public or Friend to use it. Your current method may select an entirely different node than you expect, depending on in what order the nodes have been added to your tree.
Comment by: Peter Thornton (20-9-2019 15:22:00) deeplink to this comment
Hi Filipe, With small treeviews it's simple and fast to loop the entire collection as your example. However do not look for the next Index value, nodes might not not be added sequentially but even if they were will not necessarily be adjacent depending on the .Expanded state. Instead look for .VisIndex, for example:
Comment by: Filipe Caetano (20-9-2019 15:26:00) deeplink to this comment
Thank you very much for your precious help. Regards, Filipe Caetano
Comment by: Filipe Caetano (21-9-2019 13:27:00) deeplink to this comment
Hello. I have read all comment searching for help, but with no success. How do I use the new methods in v026 (Treeview.ImageAdd and Node.ImageUpate) to add or update images, on MS Access, to the image control or directly to the treeview's nodes? I can't seem to understand how do I can add my own images to image control, or add images to treeview nodes directly from a file path. Thanks again for your help. Regards, Filipe Caetano
Comment by: Peter Thornton (22-9-2019 14:13:00) deeplink to this comment
Hi Filipe, Did you try what I suggested in my reply to Christian on 2017/1/9 - "...create your images in a temporary userform in Excel or Word, and copy them into the Frame in Access"
Comment by: Filipe Caetano (23-9-2019 10:12:00) deeplink to this comment
Hello Peter. Thank you for your help. But instead of resorting to this manual process, is it possible to update the images by using a path to them on the disk, registering it in a table, and then calling each image for each node (all done in MS Access)? Thanks. Regards, Filipe Caetano
Comment by: Peter Thornton (23-9-2019 18:55:00) deeplink to this comment
Hi Filipe, In the 'pro' treeview you can store images in a table as binary and the pass direct to the treeview which in turn will convert the binary to image, as you suggest. However it would be complicated and a lot of work to include in the free versionn. Simpler to save the image back to file (from the table) and load directly to the treeview with
Comment by: Filipe Caetano (23-9-2019 23:02:00) deeplink to this comment
Thank you Peter. Sorry, I do not understand what you mean with "save the image back to file from the table. I know you are a real altruist person and I don't want to be a "pain in the ass" :)
Comment by: Peter Thornton (24-9-2019 11:49:00) deeplink to this comment
Hi Filipe, With images stored as binary in a table you can write them back to file and load into the treeview, something like this:
Comment by: Filipe Caetano (1-10-2019 09:59:00) deeplink to this comment
Hello. Is it possible to raise an event (for example mcTree_MouseUp or mcTree_AfterLabelEdit) and use it in a normal module instead of using it in a form module? Because I have multiple tree controls, I've tried to move their specific events to a single module and delete them from form modules, but then they don't get triggered. Thanks in advance. Regards, Filipe Caetano
Comment by: Peter Thornton (1-10-2019 12:09:00) deeplink to this comment
Hi Filipe, You can raise additional events like MouseUp though AfterLabelEdit is already included, see NodeEventRouter in clsTreeview and it's all there for you. Events can only be trapped in class or form modules (a type of class module). Code in an event stub can of course call a routine in a normal module but normally that's not a good approach, at least do not maintain treeview or node references in a normal module. If you have a small number of treeviews trap their events in the same form or class instance with mcTree1, mcTree2 etc. If you have several treeviews create a separate 'WithEvents' treeview class.
Comment by: Filipe Caetano (2-10-2019 10:27:00) deeplink to this comment
Hi Peter. I don't think I understood everything you wrote but I'll try to follow your directions. Thank you very much once again. Best regards, Filipe Caetano
Comment by: Filipe Caetano (3-10-2019 17:17:00) deeplink to this comment
Hi Peter. Let me ask for a simple example. if I put this on a normal module:
Comment by: Peter Thornton (4-10-2019 09:58:00) deeplink to this comment
Hi Filipe, As I mentioned last time it is not possible to trap events in a normal module. You can only use WithEvents in a class module, form modules and object modules like Excel Worksheet modules are types of class modules. Maintaining a reference to a treeview instance in a normal module serves no purpose, except perhaps occasionally for special objectives. Also as I mentioned last time, if you have multiple treeviews you can handle their events with an array or collection of class modules, without trapping any events in the form. With the treeview it is important to ensure all 2-way parent child (clsTreeview <> clsNode) references are fully cleared when done, this needs extra care if trapping events outside the form to ensure TerminateTree gets called for each instance when closing.
Comment by: Filipe Caetano (4-10-2019 11:26:00) deeplink to this comment
Thank very much Peter. Best regards, Filipe Caetano
Comment by: Joe Kane (9-10-2019 14:31:00) deeplink to this comment
This is very nice work, I salute your implementation. I am considering the use of the treeview for my application. One of the functions that appears to be missing is a way to search the tree for a node, or test if a node exists. I will be building the tree from data that has the paths flattened (So I will probably implement an inverse function to the fullpath method of clsNode. So as I'm processing paths, I'd only want to create new nodes/child nodes if they don't already exist. It looks like the code will throw a runtime error if you try to add a node with a key that already exists. So I suppose I can trap around that. I can also maintain my own list of nodes as I create them, probably in a simple dictionary, but it is some added overhead. I see that Nodes returns a collection object. This always frustrates me about collections (no native "Exists" method).
Comment by: Jan Karel Pieterse (9-10-2019 15:54:00) deeplink to this comment
Hi Joe, Thanks for the kudos :-) You're right, there is no NodeExists property or function. The good thing is that given that you get access to the source code it is quite easy to add the functionality yourself, simply add a routine to the clsTreeview class and you're good to go. I agree though, it is a collection so there is no Exists property and you still have to traverse the entire collection to find out if it is in there. We wanted to keep this VBA-only, so without any refs outside of the default Office ones.
Comment by: Filipe Caetano (9-10-2019 22:23:00) deeplink to this comment
Hello. I know how to get all node's parent nodes by doing this:
Comment by: Jan Karel Pieterse (10-10-2019 11:59:00) deeplink to this comment
Hi Filipe, You loop through childnodes recursively as you said:
Comment by: Filipe Caetano (10-10-2019 13:32:00) deeplink to this comment
Thank you very much Jan. It works! Great solution. Regards, Filipe Caetano
Comment by: Selami Demir (13-10-2019 18:23:00) deeplink to this comment
Dear authors of VBA treeview. I've used your control in one of my projects, which is an MS Excel VBA tool for wastewater treatment plant simulation. Your control helped me to easily design a userform on which simulation results are classified in treeview. I will publish my work in a scientific journal and I will add the following text to the acknowledgement. Is it Ok? Thanks. The acknowledgement text goes here: The author would like to thank JKP Application Development Services and Peter Thornton (https://jkp-ads.com/Articles/treeview.asp) for creating a wonderful, all-VBA treeview control, which was extremely beneficial for implementing the userform to view the simulation results.
Comment by: Jan Karel Pieterse (14-10-2019 10:04:00) deeplink to this comment
Hi Selami, Yes that is perfect. Would you be willing to post a reference to the published article here, once it is available on-line?
Comment by: Marcus (28-10-2019 21:22:00) deeplink to this comment
Hello, verry nice tool. Thank you for your effort. Is it possible use it as a kind of file explorer. I used the MS treeview to navigate thru folders and files. How can I use your tool to do so?
Comment by: Jan Karel Pieterse (29-10-2019 11:52:00) deeplink to this comment
Hi Marcus, It isn't very hard to replace the common controls treeview with ours, I suggest you study the demo Excel file and then just try to apply that to your own project. If you run into a problem, let us know and we'll try to help.
Comment by: Martin Liss (26-11-2019 19:48:00) deeplink to this comment
The font size of the ControlTipText seems smaller than in a traditional Treeview. Is it possible to make it larger?
Comment by: Jan Karel Pieterse (26-11-2019 20:25:00) deeplink to this comment
Hi Martin, It should be the same as the tooltip for any other control on your userform. I think there should be a setting in Windows which controls that, but I have no idea which one.
Comment by: Martin Liss (26-11-2019 20:42:00) deeplink to this comment
Yes it may be the same font size as the buttons, etcetera in my userform, but the ControlTipText font size is definitely larger in a traditional Treeview then in your replacement. Everything else works great otherwise. Is there some way for me to send you a picture or two?
Comment by: Ron Mittelman (27-11-2019 00:41:00) deeplink to this comment
Great looking control! I'm looking to replace the stock Switchboard Manager with a more versatile form, and thought a treeview would be perfect to represent the menu heirarchy. I don't see any methods for moving a node up or down, but please correct me if there are these methods. Is it possible to move nodes, and if so is it possible to restrict moving the nodes only within the existing parent node? Thanks...
Comment by: Ron Mittelman (27-11-2019 00:43:00) deeplink to this comment
Will the TreeView control work properly in Access 2016? I notice that version is not listed in the article. Thanks...
Comment by: Jan Karel Pieterse (27-11-2019 09:37:00) deeplink to this comment
Hi Martin, I can imagine the controltip to be different of the traditional treeview, this is likely due to the fact that it is part of an external library. Given that all controls of our tree are "internal", they share the properties of all built-in controls. There is no way to change the controltooltip formatting for just one (set of) control(s). But hey, consistency is important is it not? You could program a mousemove event to show a label control on top of the tree which you can format any way you like. The challenge would be to make sure the label disappears in all cases when the mouse leaves the control, which is not easy to do!
Comment by: Jan Karel Pieterse (27-11-2019 09:42:00) deeplink to this comment
Hi Ron, Addressing both questions... Yes the treeview will work in that version of Access. I've updated the page accordingly. Moving nodes (drag and drop) is something that is reserved to the pro version of the control, which is available for purchase. Let me know if you're interested. Given that the free version includes all source code you can of course also implement drag and drop yourself :-)
Comment by: Eduardo Teles (11-12-2019 17:04:00) deeplink to this comment
Great tool Peter! I'm unexperienced but on Excel i managed to Populate The "DataDump" sheet, but... I can't populate the Treeview from the "DataDump" Sheet back to treeView :( I'm confidant that there's a way, can you please show me? Thank you! :)
Comment by: J.K. Pieterse (11-12-2019 20:28:00) deeplink to this comment
Hi Eduardo, Perhaps this comment helps? https://jkp-ads.com/articles/treeview.asp?AllComments=True#21575
Comment by: Jamie (14-12-2019 19:27:00) deeplink to this comment
1) How do I change the background colour of the tree? I know that the UserForm picks up the colour when it's added to the subTreeview form, but how do I reference it, once it's created. 2) I often get "XXXX Can't move the focus to the control subTreeview". My design is a main form with the SubTreeform on the left and another subform (ObjectWindow) on the right. When I select something in the tree I change the contents of the subform on the right. If I am in the middle of editing the data in the ObjectWindow and I click on the tree, that's usually when I get this error. I have tried to stop the focus from moving, but I have had no luck thus far.
Comment by: Peter Thornton (15-12-2019 11:16:00) deeplink to this comment
Hi Jamie, 1) Apply default formats (incl font) as required for the background, they will also be inherited by default by node labels: mcTree.TreeControl.BackColor = RGB(240, 250, 255) Apply before calling Refresh. 2) With the Access version there can be issues with focus, mainly while developing (if that's what you mean by 'Objectwindow') but also after alt-tab'ing windows, though usually not a problem in use. It's a lot of work to change and not viable to add to the free version, the 'pro' version is much better in this respect.
Comment by: Jamie (19-12-2019 18:14:00) deeplink to this comment
I have a need to open the tree to a specific node (rather than to a specific level). I have the node's key. I have tried: Set .ActiveNode = .Nodes("key") but it only appears to work if the parents of the node are all expanded. Is there a way to have the tree expanded "down" to a specific node?
Comment by: Peter Thornton (19-12-2019 19:56:00) deeplink to this comment
Hi Jamie, After setting the active node call .ScrollToView Also see ScrollToView in the documentation and search this page for same.
Comment by: Jamie (2-1-2020 15:50:00) deeplink to this comment
Is there a way to disable the mcTree control? I have another subform, called "ObjectWindow" on the same parent form as the subTreeview subform (A sibling form, if you will). I don't want people be able to click on nodes in the tree while I am editing data in subform "ObjectWindow", so I would like to disable it.
Comment by: Peter Thornton (2-1-2020 16:11:00) deeplink to this comment
Hi Jamie,
Comment by: Hervé Vuillaume (13-1-2020 11:16:00) deeplink to this comment
Hi, That you for your treeview, I'm currently working for implementing it in a big Word macro. I use treeview because I need lists with icons. But I have 2 remarks: - could you add an option to totally remove the root node ? - could you add also an option for having a double click if the treeview is not in edit mode ? For the double_click, I had modified the function like this :
Comment by: Peter Thornton (13-1-2020 20:43:00) deeplink to this comment
Hi Hervé #1 To delete or remove a root node use mcTree.NodeRemove cNode, where cNode refers to the root node (or any other node) you want to delete. There's an example in the demo, select the node and press Delete on the keyboard, see the mcTree_KeyDown event. #2 You've correctly uncommented mcTree.NodeEventRouter the node label's DblClick event. In NodeEventRouter in clsTreeview also uncomment the following Else tvDblClick RaiseEvent MouseAction In the declarations uncomment: And in your form add the MouseAction stub from the right dropdown which should now appear when you select mcTree in the left dropdown. You can use the 'MouseAction' example to pass all events, if you prefer make and raise your own dblClick event.
Comment by: Peter Thornton (13-1-2020 20:46:00) deeplink to this comment
Re #2, I forgot: In the declarations uncomment: Event MouseAction
Comment by: Andrew de beer (6-2-2020 07:31:00) deeplink to this comment
Good Day. Is it possible to change tr Font, Font Siza and Forecolor of the node labels? Best Regards Andrew
Comment by: Peter Thornton (6-2-2020 10:32:00) deeplink to this comment
Hi Jamie, In the Excel version apply your choice of Font properties to the tree-control frame, node labels will inherit the same properties. In Access, referring to the demo adapt the ApplyDefaultFont routine in the subform code module. Call ApplyDefaultFont before adding any nodes, for example in 'Get pTreeview' just above. Nodes also have a ForeColor property so you can change them individually.
Comment by: Richard Cawthorn (10-2-2020 12:05:00) deeplink to this comment
I'm having an issue with using icons. I have a 12x12 icon that does not show on the tree. I've tried adjusting mcIconSize and mcIconPad, but to no effect (no visible change seen).
Comment by: Jan Karel Pieterse (17-2-2020 09:38:00) deeplink to this comment
Hi Richard, Is the icon visible in an image control if you manually add it to the userform?
Comment by: Bernard (27-2-2020 11:15:00) deeplink to this comment
Hi It would be wonderfull if the scroll wheel is supported when having a large tree. For the rest the tree is working great ! Regards Bernard
Comment by: Jan Karel Pieterse (27-2-2020 13:03:00) deeplink to this comment
Hi Bernard, Scroll wheel support is included in the pro version of the treeview.
Comment by: Claus Krog Mikkelsen (17-3-2020 10:03:00) deeplink to this comment
Hi, and thanks for a fantastic treview module. I have a question: I would like to change the node icon to another image from the image-frame to visualize a status-change of the node. I have tried to: - set a new .ImageMain - set a new .ImageExpanded - and then called a .Refresh But nothing happens. Is there a way to do that and still have the same node selected? Best Regards Claus
Comment by: Jan Karel Pieterse (17-3-2020 14:17:00) deeplink to this comment
Hi Claus, In the NodeAdd method, make sure you include both the ImageMain and the ImageExpanded arguments. The class modules will then handle display of the correct node icons.
Comment by: Claus Krog Mikkelsen (17-3-2020 22:17:00) deeplink to this comment
Hi Jan, thanks for a very quick answer - but I can't make it work. This is what I do: Set cNode = ParentSampleNode.AddChild(Sample_number, Sample_name, "iS1", "iS1") and later trying to change the icons: cNode.ImageMain = "iS2" cNode.ImageExpanded = "iS2" SampleTree.Refresh But I found a solution in the comment-list from 11-4-2014 16:35:10, doing this: cNode.ImageMain = "FLGUSA02" cNode.Expanded = Not cNode.Expanded cNode.Expanded = Not cNode.Expanded Is this still the way to make it work? Best Regards Claus
Comment by: Jan Karel Pieterse (18-3-2020 10:21:00) deeplink to this comment
Hi Claus, Yes that is a good way to do it. If you assign a different Icon name to a node, it does not trigger reloading the icon, changing the expanded state does.
Comment by: Andreas Katits (21-4-2020 11:28:00) deeplink to this comment
Hi, I use the free version of the Treeview and I have a suggestion in your Code. Module clsNode Private Sub moEditBox_KeyDown You make a distinction between Mac and Win because of NewLine. You can use just the constant vbNewLine. This constant works on every OS properly. It uses I think vbCrLf on Windows and vbCr on Mac. Best Regards Andreas
Comment by: Jan Karel Pieterse (21-4-2020 13:00:00) deeplink to this comment
Hi Andreas, Thank you!
Comment by: EJ (23-4-2020 10:20:00) deeplink to this comment
There might be some missing parts within the Access version? I got messages like "User-defined Type not defined". I running office 365, is that a problem? Do you have any solutions?
Comment by: Peter Thornton (23-4-2020 13:42:00) deeplink to this comment
Hello EJ,, I have just tested the current Access download in Office 365 and it all works fine for me. Are your security settings enabled for macros and ActiveX controls (for the MSForms Frame on the Access form with the images). If all enabled where are are you getting those messages?
Comment by: Ayush (15-5-2020 20:49:00) deeplink to this comment
I was trying to save the date, after updating the tree, but while reading it to populate tree, it is throwing error at Set nNode = nNode.AddChild(, CStr(vData(lRow, lLevel)))
Comment by: Peter Thornton (16-5-2020 12:02:00) deeplink to this comment
Hi Ayush, Does nNode refer to a node (clsNode) that already exists, if so- What is the value of vData(lRow, lLevel) when the error exists. If lRow and lLevel are within the bounds of the array, can the element value be converted to a string with, for example Null and error values can't be.
Comment by: Brian (17-6-2020 02:29:00) deeplink to this comment
I have used MS Access Treeviews for years. My company just updated to 64 bit Office and everything stopped working. I found your tool with an internet search. I looked through your code and it looks very promising. In looking at your code, it looks like a separate query is used for each level in the tree. I currently have a single table setup to control my trees. The structure is... NodeID: numeric value with no duplicates ParentID: numeric value - 0's are root nodes. Child nodes contain the NodeID for the root it belongs under (may be several levels deep) NodeOrder: numeric value to sort Nodes of a given level in the correct order NodeText: text to display for the node in the three NodeObjectType: Form, Query, Report, etc. (used if clicking on a node will open another object) NodeObjectName: name of Access table, form, query, etc. (used if clicking on a node will open Access object) NodeImage: icon to display in tree
Comment by: Peter Thornton (17-6-2020 10:30:00) deeplink to this comment
Hi Brian, In the 'pro' version nodes can be added in any order, even before their before prospective parents were added. But if I follow, in your code it looks like child nodes are always added to an existing parent (except root nodes which are added with 'tvwLast'). If so, try replacing the following in your code: TreeView0 > mcTree, or your name for the WithEvents clsTreeview reference. Nodes.Add > NodeAdd tvwLast > tvLast tvwNext > tvNext
Comment by: Jan Karel Pieterse (17-6-2020 10:32:00) deeplink to this comment
Hi Brian, You definitely can do that, you just need to adapt your current code to use the properties and methods of our tree rather than the common controls one. Instead of using AddChild, check out our implementation of the NodeAdd method, as that allows you to specify the key of the parent node. I advise you to download the Excel sample as that has more extensive documentation.
Comment by: Brian (20-6-2020 01:15:00) deeplink to this comment
Peter and Jan Karel, thank you for your assistance. I got my treeview to populate and function as desired. I have one additional question. With the ActiveX treeview, I had buttons on my form to expand/collapse all nodes. Is there a way to do this with your tool without rebuilding the entire tree (I can make it work by rebuilding the tree and adding cNode.Expanded = True/False (depending on button pressed)? I would rather not have to rebuild the entire tree if there is another way to accomplish this task. Thank you
Comment by: Jan Karel Pieterse (22-6-2020 10:28:00) deeplink to this comment
Hi Brian, There is an "ExpandToLevel" method for expanding/collapsing the tree to a certain nodelevel.
Comment by: Raphael Love (8-8-2020 14:10:00) deeplink to this comment
Is there a way to let sub procedure from standard module to control mcTree? Thank you for your assistance.
Comment by: Peter Thornton (9-8-2020 13:21:00) deeplink to this comment
Yes, several ways. You could maintain a reference to the form in the normal module, and call a public method in the form; either process the mcTree reference in the form or return a copy of mcTree to the normal module. Or you could maintain a copy of the mcTree reference in the normal module. Whichever approach you go for it'll be important to ensure any references in the normal module (to the form or the treeview) are destroyed when the form closes, in particular ensure that TerminateTree gets called.
Comment by: Vas Rabani (27-9-2020 10:38:00) deeplink to this comment
I'm not sure but there seems to be a bug in the code for 'Dump Data' button if you amend the code for the Sub GetData1 to display the node.key in the excel output rng(lCt, lLevel) = cParent.Key & "|" & cParent.Caption The root node key is not showing as RootNode1 but the key is displayed as a run date example : 27/09/2020 09:23:50|Root Node1 Root1_1|1.A Root1_2|2.B Same behaviour exists when reading data - although the key has the right key on the front end, when dumping the data the date behaviour is showing Thought it was odd. Is this an expected behaviour? Great site ! and great implementation of the TreeView!
Comment by: Peter Thornton (28-9-2020 11:21:00) deeplink to this comment
Hi Vas, Indeed that is not expected behaviour but I cannot re-create what you describe. For me when I include your line that adds the Key the following is dumped in the cell for the root node: Root1|Root Node1 I can't imagine how but for you it looks like the Key is replaced with 'Now'. If you want to send me your version of the demo file I will have a look.
Comment by: Bob Luedtke (16-10-2020 21:22:00) deeplink to this comment
Rather than a userform, can this TreeView solution be done using a frame dropped directly on to a worksheet? I've tried a few things but can't make it populate. Thanks.
Comment by: Jan Karel Pieterse (19-10-2020 10:47:00) deeplink to this comment
Hi Bob, Yes that can be done indeed. I've sent you a direct email.
Comment by: Davood (20-10-2020 13:43:00) deeplink to this comment
Hi I visited your site and you have practical content in the field of Excel. I have a problem and that is about Treeview. My office version is 2010 but I do not have Treeview in the tools. please guide me
Comment by: Jan Karel Pieterse (20-10-2020 17:33:00) deeplink to this comment
Hi Davood, What exactly is your question? This page already explains how you might use our treeview in your project.
Comment by: PYC (4-12-2020 10:09:00) deeplink to this comment
A WONDERFUL GIFT. Here is exactly the spirit of IT I like since I starded 40 years ago (I was 12 at that time for my first program on an ORIC Atmos) … even if nowadays I'm trying to move to some web oriented development I'm still developing a lot of small applications in MS-ACCESS or EXCEL because I'm super fast to do so and the rendering is very efficient … Up to now I was not so much using Trees (more tables) but recently I had to manipulate the organization tree of my company and the tree is for sure definitively the best … I lost some time trying by myself … and discovered your example when I was done … but still I can learn some few things so thanks a lot. .. hum … may be one question … Is there a way for example to highlight in RED BOLD only one or few elements of the trees that have some specific attributes so that we can identify them … not sure I saw that in you example Anyway A BIG THANKS AND CONGRATULATION FOR THE GIFTING SPIRIT PYC Your example is beautiful and complete and will help a lot of people for sure. THANKS a lot
Comment by: Jan Karel Pieterse (4-12-2020 11:34:00) deeplink to this comment
Hi PYC, You're welcome! You can set several formatting properties (like the ForeColor) of a node after creating it or after a click on the node. After changing a node's property you have to refresh the tree (a method of the clsTreeview class). In the example that would be something like:
Comment by: Tony (31-1-2021 21:00:00) deeplink to this comment
Hi Jan Karel, I use the frame method to collect images (from a second userform). - Is it possible to check in advance whether the image name string I want to pass as argument to the .AddChild() function actually exists? Thank you in advance.
Comment by: Peter Thornton (1-2-2021 17:08:00) deeplink to this comment
Hi Tony, You could loop all the Image controls and exit if/when you find it to return found, or test if it exists like this:
Comment by: Theodore Whistle (8-2-2021 13:12:00) deeplink to this comment
Hi, I love to use this treeview control and it is a great gift. Now I'm using the image feature the first time. I successfully added some images to the tree. Now I have a usecase where it is neccessary to remove images. I tried to implement this by myself but I found now way. Any suggestions how I can try to implement this? I added a sub to set image count variables to zero but I found no way to force repainting of the affected node. Any suggestions how I may repaint any node to remove the images? If I can implement this successfully I would share this here. Best regards Theodore
Comment by: Jan Karel Pieterse (8-2-2021 13:47:00) deeplink to this comment
Hi Theodore, You need to first set the ImageMain to an empty string and then call the ImageUpdate method and finally refresh the tree:
Comment by: Theodore Whistle (9-2-2021 09:53:00) deeplink to this comment
Hi Karel, thank you for this fast replay. Great! The procedure you mentioned is what I have done. For adding pictures after treeview was generated this worked well. But if I do this to remove the image the picture will be still displayed. I think the code in ImageMain Property has no functionality to remove Image if empty string is entered.
Comment by: Jan Karel Pieterse (9-2-2021 10:04:00) deeplink to this comment
Hi Theodore, Odd, I tried this with our demo and the code did remove the node icon which is added with the "Demo treeview with icons" button.
Comment by: Theodore Whistle (9-2-2021 12:13:00) deeplink to this comment
Hi Karel, thanks again for your very fast replay. I tried this out with your demo files and with the excel demo file it works like you descriped. I have an access project and with your access demo file the icons can also not be removed. I have forgotten to mention in my first post that I'm using Access. Sorry. Best regards Theodore
Comment by: Peter Thornton (20-2-2021 15:11:00) deeplink to this comment
Hi Karel, Sorry for late reply. Assuming in Access you're using the same 026 treeview version as Excel there is no difference in how Images are handled. Use ImageAdd to add a new image to the Images collection (if necessary), then cNode.ImageMain = "new-icon-name", and optionally similar for .ImageExpanded. Then to update cNode.ImageUpdate
Comment by: C.Horn (27-2-2021 17:43:00) deeplink to this comment
What is the trick to allow the 'Read Data' Function to work? When button is clicked it crashes. I would like to be able to use the DataDump sheet to create my own hierarchy.
Comment by: Peter Thornton (28-2-2021 11:53:00) deeplink to this comment
Hello C. Horn, I have just tested the 'Read Data from Tables' button on the Access version demo form and it works fine for me. Maybe something in your file has changed, if you want to zip it and send it to me off-line I will have a look. The 'DumpData' sheet is in the Excel demo, as part of an example to copy the treeview data to a worksheet. You can use the same example in Access but you just need a few extra lines to automate Excel from Access.
Comment by: C.Horn (28-2-2021 19:44:00) deeplink to this comment
I am using the Excel Version and running it from Excel.
Comment by: Peter Thornton (2-3-2021 10:49:00) deeplink to this comment
Hello again C. Horn, There isn't a 'Read Data' button or similarly named function in the Excel demo so I am not sure what you are trying to do...?
Comment by: Jakob Gram (2-3-2021 14:49:00) deeplink to this comment
Running Access 2003 on Win10. I get this message when opening: Method 'Form' of object '_subForm' failed I have added FM20.DLL as I didnt use before (64-bit?) kindly regards Jakob
Comment by: Peter Thornton (3-3-2021 13:30:00) deeplink to this comment
Hi Jakob, Normally the first time you load the demo form the userform is created automatically in the 'CheckUK' routine. It sounds like it wasn't for you, not sure why not but it wouldn't be related to use of x64. One possibility is if your VBE is set to 'break on all errors' it would have failed in the error handler that anticipates an error checking if the form exists. But if you have since managed to add it you should be good to go.
Comment by: Jakob Gram (3-3-2021 20:42:00) deeplink to this comment
Thanks for super rapid answer but: When the database is opened: Automation object does not contain IsTrusted -Macro: Autoexec: Currentproject.Application:Version>=12 Cannot runMacro Argument: Checktrust STOP When database is open I try frmDemo: LayOut unknown Userdefined type not defined and at last when the form is loading Userdefined type... and when resizing: userdefine type....(Sorry but my Access speaks danish :/) Does it give you any clues? Jakob
Comment by: Peter Thornton (4-3-2021 10:27:00) deeplink to this comment
Hi Jacob, The clue is simply Danish! The problem might be related to Application.Version returning a string with a comma separated decimal which does not coerce to a numeric value in VBA. As you've already added the userform and 'Trusted' the app, you can remove the AutoExec and CheckTrust macros entirely. Or to ensure the demo form loads at startup, in the AutoExec as you are running 2003/v11 remove the first line and in the remaining line under Condition enter simply True. Also in the frmHelp code, in the Load event to ensure the app version is returned as a numeric value include Val - Val(Applcation.Version)
Comment by: John (18-3-2021 03:47:00) deeplink to this comment
Is it possible to sort the tree by key and not caption without having to copy things around before and after sorting? Also, do you refresh before or after the sort?
Comment by: Peter Thornton (18-3-2021 11:50:00) deeplink to this comment
Hi John, In clsNodes / Function Sort / in the For i = loop Change sCaptions(i) = ChildNodes.Item(i).Caption > to > .Key Always call Refresh as the last and final call to physically rebuild the tree after making any changes, such as sorting.
Comment by: Ozair Khalid (14-4-2021 05:58:00) deeplink to this comment
Hi, We are experimenting with MS Access version of your wonderful tool. Question: Does it support Drag and Drop? Dragging an item from one node and dropping it onto another. Other Details: Windows 2012 Server using Remote Desktop 64bit MS Access 2016 64bit. Best, Ozair.
Comment by: Jan Karel Pieterse (14-4-2021 10:50:00) deeplink to this comment
Hi Ozair, The free implementation doesn't support drag and drop, but our pro version does. We'll send you the details.
Comment by: Adrian Connolly (21-4-2021 07:25:00) deeplink to this comment
First is there a video that demonstrate how to add the program to a file effectively for MS Access office 365. how do i connect the form to a table and change the information on the tree-view list based on a SQL statement of the said table. using a form button example Practice settings then client. and how do i get the tree view sub form control the other sub form an what is shown? thanks
Comment by: Jan Karel Pieterse (21-4-2021 12:31:00) deeplink to this comment
Hi Adrian, I'm afraid there is no video explaining things, but the access download does include a form containing instructions on how to add the treeview to your Access project and some demo code. Does that help?
Comment by: sancarn (22-4-2021 00:02:00) deeplink to this comment
I'm currently in the process of attempting to use this library. First - really great work! I'm currently having a little bit of trouble, and it's likely because what I'm doing is a little more advanced. I'm making an Accessibility viewer (MSAA) I was a little saddened by seeing that `clsNode` doesn't implement some interface, and also has a lot of methods implemented which are about the style and rendering of the node... I've been trying to extract out the graph aspects but it's difficult to know how everything relates, so I've resorted to re-implementing the clsNode interface entirely (and hopefully getting it right) Generally it'd be useful if clsTreeNode internally referred to INode who has strictly relational information (parent, children, name/key (maybe?)) That said I'm sure I'll be able to do what I need. Will update on progress as I make it :)
Comment by: Jan Karel Pieterse (22-4-2021 13:28:00) deeplink to this comment
Hi sancarn, You cought me there, I have too little experience with Interfaces :-)
Comment by: Götz (5-5-2021 10:30:00) deeplink to this comment
Hi Jan, I am using your Treeview in our Access application and it works perfectly without major problems. Many thanks for making this versatile piece of software available for free! Now my question: If the subform containing the tree gets invisible it receives the close event resulting in the termination of the contained Form and Treeview objects. This may happen during normal work e.g. if I switch the form to datasheet view or if I set a filter with empty results. When the subform get visible again (when I go back to form view or remove the filter) the Form and Treeview objects are not restored and I get runtime errors due to empty references. Any hints how to prevent this? Maybe this issue is fixed in the pro version? Thanks in advance. Götz
Comment by: Jan Karel Pieterse (5-5-2021 10:45:00) deeplink to this comment
Hi Götz, If you change the form to design view, the Form_Close event triggers. In the demo, that terminates the treeview (and it should!). So I think the best approach in your situation would be te have the tree reinitialize itself when it is supposed to be visible again.
Comment by: Steve Van Dellen (16-5-2021 20:49:00) deeplink to this comment
The Demo in TreeView 026_3.xlsm downloaded from https://jkp-ads.com/DownloadScript.asp?filename=treeviewexcel.zip works fine on Windows 10 but if won't compile in Excel 16.49/VBA 7.1 on macOS 11.1. I wonder if it's really the correct version? VBA says "...must be updated for use on 64-bit systems..." for:
Comment by: Peter Thornton (17-5-2021 18:33:00) deeplink to this comment
Hi Steve, We haven't updated the original download version for a long time, long before Mac 2016 came along with a 64 bit version! It looks like the problem here is the '#If VBA7 And Not Mac', your x64 Mac then tries to compile the 32 bit APIs and fails. You could include all the APIs between '#If Mac = False Then ... #End If' so they are ignored. First though try simply removing the 'And Not Mac', it might work but I don't have Mac 2016x64 to test. For your second question - move the '#If Mac...#End If' lines so they are just above the Select Case line. (Keyboard Edit in Windows is F2 but Ctrl-F in Mac)
Comment by: Steve Van Dellen (17-5-2021 20:47:00) deeplink to this comment
Thanks for the prompt attention. Deleting "And Not Mac" and moving the "Select Case" statements fixed those problems.
Comment by: Peter Thornton (18-5-2021 10:17:00) deeplink to this comment
Thanks Steve for the follow-up and tip to 'invert' that Mac test. For anyone else following, this has been about minor fixes for use with Excel 2016-64 for Mac
Comment by: Marc Rosario (12-6-2021 00:41:00) deeplink to this comment
Can I load data from Excel into the treeview?
Comment by: Peter Thornton (12-6-2021 11:17:00) deeplink to this comment
Hi Marc, Yes, if you are running the treeview in Excel typically (though not necessarily) the data is stored in Excel, ie worksheet cells.
Comment by: Justin (29-6-2021 20:34:00) deeplink to this comment
Hi, I'm interested in either the free or pro version of your Treeview tool. I have a few questions. 1. Can I choose the font and font size used in the Treeview control? 2. Can drag and drop be used to not only move a child record to another parent, but also to copy a child record to another parent? 3. Can I populate the top level nodes from one table or query and the child nodes from another? Please let me know if you need more explanation regarding this. Thanks! Justin
Comment by: Peter Thornton (30-6-2021 23:48:00) deeplink to this comment
Hi Justin, #1 You can change Font properties in both free and pro treeview versions. Though the pro includes additional methods for changing and updating properties in real time. #2 D&D is included in the pro Treeview. Although in theory it could be added to the free version there'd always be potential issues with it which we wouldn't want to support! #3 Both the free and pro versions can be populated from a variety of sources, such as you describer. The pro is more flexible as nodes can be populated in any order, even before a given node's prospective parent node has been created and exists. Contact us if you are interested to try the pro version.
Comment by: Sven (13-7-2021 09:54:00) deeplink to this comment
With Tristate enabled (e.g. Demo 1), when a child is deactivated, parents are deactivated, too. This behavior is not correct from my point of view. The element itself and all its children should be deactivated, but not its parents.
Comment by: Jan Karel Pieterse (13-7-2021 11:14:00) deeplink to this comment
Hi Sven, Unlike this treeview implementation: https://docs.telerik.com/devtools/aspnet-ajax/controls/treeview/checkboxes/tri-state/tri-state-checkboxes-overview ours does not have a property to control what happens with children if you check or uncheck their parent. Given that we give you access to the code, you can of course implement that yourself :-)
Comment by: Mas Watanabe (22-7-2021 19:56:00) deeplink to this comment
Hi, thank you for creating this great TreeView alternative which has been relieving a lot of headache. This solution has been working flawlessly on my Access database until about two weeks ago. Not sure what happened two weeks ago (may be Windows update?), but it started causing the following error when the form containing this is about to open. The expression Layout you entered as the event property setting produced the following error: User-defined type not defined. *The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. *There may have been an error evaluation the function, event, or macro. It seemed that 64 bit Access does not cause this error, but 32 bit Access creates this error. Once this happened, even a 64 bit Access caused this error as if 32 bit Access corrupted the file. Do you have any idea what is happening?
Comment by: Jan Karel Pieterse (23-7-2021 11:57:00) deeplink to this comment
Hi Mas, I'm not sure, but perhaps you need to delete the userform from the project and use the code as available in the demo project to add it back. Also, maybe you need to do this separately for the 32 bit and 64 bit versions of Access, I'm not sure?
Comment by: Peter Thornton (23-7-2021 12:18:00) deeplink to this comment
Hi Mas, Just to add, maybe you accidentally changed something in your 32bit file, then if this file is transferred to Access 64 the same error would occur. The first thing to try, if you haven't already, is Debug / Compile and fix any compile errors. However this doesn't always find all issues. If you have a working backup try that.
Comment by: Raimund Bogner (3-8-2021 21:25:00) deeplink to this comment
Hi, I'm using your great free treeview alternative with Access 2016 64bit. I know, how to find and activate a node using For Each cNode In mcTree.Nodes ..., but how can I highlight a node without clicking on it? Is mouse wheel support part of the pro version? ty
Comment by: Peter Thornton (4-8-2021 10:10:00) deeplink to this comment
Hi Raimund, If you mean set a node as the active node without clicking on it -
Comment by: Anthony Curtis (30-8-2021 22:03:00) deeplink to this comment
Hello, Thank you so much for writing and actively supporting the users. I got the code to work outside of the demo but what I need to do is count the number of end-nodes selected. For example (using the demo data), if I select 1.A, 4.0001, 4.0002, and 4.0003 the total selected should be 4. It's currently showing 5 with my code because 4.D +15 is being counted. I'd like to only have the last child counted. Here's the code:
Comment by: Peter Thornton (31-8-2021 11:06:00) deeplink to this comment
Hi Anthony, If I follow you want to count all checked 'leaf' nodes in the entire tree(?). Try this -
Comment by: Anthony Curtis (2-9-2021 15:42:00) deeplink to this comment
Hi Peter, This is exactly what I was looking to do, thank you! Anthony
Comment by: Ann (30-9-2021 17:12:00) deeplink to this comment
Hi Peter, I'm using your magic free treeview alternative with Access 2016 64bit but, how I list only the nodes checked? Thanks! Ann
Comment by: Jan Karel Pieterse (30-9-2021 17:26:00) deeplink to this comment
Hi Ann, Nodes have a property called Checked which has these possible values: -1 true, 0 false, +1 mixed mixed is when the node has childNodes and not all are checked.
Comment by: Sam (2-10-2021 19:22:00) deeplink to this comment
Hi, I have tested the free version and created a tree of 197000 nodes. Upload was done in more than 26 minutes ! I guess pro version works faster.. What will be the gain approximately ? Thanks
Comment by: Peter Thornton (3-10-2021 19:46:00) deeplink to this comment
Hi Sam, I'd expect 200,000 nodes to load and fully display in about 5 seconds in the pro Treeview. That excludes time to get the data which, depending on the source, can vary considerably, but that's not directly related to the treeview. Actually, if it 'only' took 26 minutes for you with our free version you've probably got a modern/powerful system, so maybe less 5 seconds with the pro for you! You're welcome to try the pro version, just contact us.
Comment by: Chris Sweetnam (23-11-2021 23:23:00) deeplink to this comment
Thanks for your wonderful product Jan. I have donated The only problem I have had is when I copy and paste my MS Access front end, often the reference to fm20.dll gets lost and has to be re established. Nothing to do with your product of course but writing it in case someone also goes crazy wondering why the references to MSForms are not being found.
Comment by: Richard (24-11-2021 20:45:00) deeplink to this comment
Hello, In the treeview, ONE of the items is listed with a smaller font that everything else! here is an image: https://i.ibb.co/NV5n26Q/Bad-Font-Size.jpg Any ideas why this happens and how to fix? Is there some place in the code I could examine for this problem? THANKS!
Comment by: Peter Thornton (25-11-2021 11:11:00) deeplink to this comment
Hi Richard, I suspect your screen display is set to a non standard size, 125% perhaps. First could you test with 100%. If that works revert to the original screen size and try the next smaller & larger font sizes. If your original screen size and font size are important to you might try the 'pro' version (contact us). A lot more work has gone into the pro treeview to display font sizes consistently with different display settings. However even with the 'pro' there are some display/font size combinations the odd irregular font size can't be avoided.
Comment by: Mohsyn (16-12-2021 09:07:00) deeplink to this comment
Hi, Is it possible to populate a treeview with data in worksheet ?
Comment by: Jan Karel Pieterse (16-12-2021 11:44:00) deeplink to this comment
Hi Mohsyn, Yes it is, we have a sample workbook that demonstrates this. Ping me through email if you're interested. My address is at the bottom of this page. Please add a link to this comment in your email by adding the link to your comment (use the link next to your comment "deeplink to this comment")
Comment by: Rodrigo Muñoz (7-1-2022 15:18:00) deeplink to this comment
Your treeview is fabulous. Thanks so much
Comment by: Jan Karel Pieterse (7-1-2022 17:50:00) deeplink to this comment
Hi Rodrigo, Thanks!
Comment by: Raymond Pierce (16-2-2022 23:51:00) deeplink to this comment
Just wondering if there's a way to have the treeview be created with all the nodes collapsed instead of opened?
Comment by: Jan Karel Pieterse (17-2-2022 10:10:00) deeplink to this comment
Hi Raymond, Of course! Make sure you set the Expanded property of the root-node(s) to False
Comment by: Silvy (15-3-2022 17:36:00) deeplink to this comment
Hi, your treeview save me... but i have a problem when i change the picture to display: sometimes i can't change bmp. there is a method to load picture by code? Thanks!!!
Comment by: Peter Thornton (16-3-2022 11:30:00) deeplink to this comment
Hi Silvy, You can add new images to the treeview's Images collection during runtime with mcTree.ImageAdd(pic, sName). The 'pic' object could, for example, be returned with VBA's LoadPicture. Change the node's ImageMain or ImageExpanded property to the unique name you included with ImageAdd. Then, force the node to update and display the new image with cNode.ImageUpdate. See ImageAdd and ImageUpdate in the demo's documentation in the clsTreeView and clsNode sheets respectively.
Comment by: Trevor (29-3-2022 18:39:00) deeplink to this comment
Is it possible to late bind the treeview control code? I haven't seen an example of the form object being late bound, to avoid setting the reference directly.
Comment by: Jan Karel Pieterse (30-3-2022 09:38:00) deeplink to this comment
Hi Trevor, What exactly do you have in mind? Given that the classes are in your project I see no benefit of late-binding, as there isn't anything to reference?
Comment by: Trevor (30-3-2022 10:21:00) deeplink to this comment
Sorry I wasn't very clear - You have to manually set a reference to the Ms Forms Object Library in the VBA IDE. If you open a database on another machine that doesn't have this reference set it obviously doesn't work. Not all end users would be comfortable setting this reference, or if distributing an accde would be able to if they only had a runtime environment?
Comment by: Peter Thornton (30-3-2022 11:55:00) deeplink to this comment
Hi Trevor, The MSForms reference must be set at design, it wouldn’t work with late binding for various reasons. Normally there should be no problem to distribute with the reference, including in an ACCDE. Your Access users should have the runtime which is included with Office. Slightly related, although the demo illustrates adding a userfom with startup code on first use, in practice it should be fine to distribute with the userform already added.
Comment by: Steve Cook (10-5-2022 03:27:00) deeplink to this comment
Great product! But I was wondering if there was a way to get the MouseDown event from the tree to allow adding a popup menu on a right mouse button click on a node?
Comment by: Jan Karel Pieterse (10-5-2022 09:54:00) deeplink to this comment
Hi Steve, There are some examples in these comments on how to add your own events to the control. Here is one: https://jkp-ads.com/Articles/treeview.asp?AllComments=True#19378 to find more, search that page for raisevent.
Comment by: David Bell (1-8-2022 23:12:00) deeplink to this comment
Current versions of Excel on the Mac (and possibly other Office products) do not seen to be refreshing the display properly on update and leave partial lines and icons hanging around Scrolling causes a refresh but is of course a bit painful for the user. Repainting the form after update fixes the issue. I added the following at the end of BuildRoot - just after the 'done:' label. It is not optimal as it determines the parent on each update, where most probably it will be fixed. It works for me, though I am aware that this might not be the only place it is required. YMMV :-) done: Dim myParent As Object Set myParent = TreeControl Do Until TypeOf myParent Is MSForms.UserForm And Not LCase(TypeName(myParent)) = "frame" 'reset to previous parent in the tree Set myParent = myParent.Parent Loop myParent.Repaint
Comment by: Peter Thornton (2-8-2022 09:47:00) deeplink to this comment
Hi Dave, I don't recall reports of issues with the Excel version not fully redrawing, though indeed the Access version sometimes requires a nudge when scrolling or expanding. However the free version gets exponentially slower with larger treeviews, if that's the issue you might consider the 'pro' version if you have a budget. If you really do need to refresh it should only TreeControl frame that requires the .Repaint, rather than the entire form as your example. Small point, the Typename of an MSForms frame control is always "Frame", so no need for cater for unknown case. If you think I've missed something and want to send an example you can contact me off-line.
Comment by: Hervé (6-10-2022 11:17:00) deeplink to this comment
Hi Peter, I have a little problem on Surface laptop with 200% zoom: if a text was very long, the end of text is truncated (one or several characters). I can provide you screen shot if you want. And I also have to add the code ".PictureSizeMode = fmPictureSizeModeZoom" in BuildNodeControls in order to have visible icons: If mbFullWidth And mbGotIcons Then If cNode.hasIcon(vKey) Then sngIconPad = mcIconPad If cNode.Icon Is Nothing Then Set cNode.Icon = TreeControl.Controls.add("Forms.Image.1", "Icon" & sName, False) With cNode.Icon .BorderStyle = fmBorderStyleNone .Left = mcTLpad + msngRootLine + msngIndent * lLevel + msngChkBoxPad .Top = mlVisCount * NodeHeight + msngTopIcon '.AutoSize .Width = mcIconSize .Height = mcIconSize .BackStyle = fmBackStyleTransparent .Picture = mcolIcons(vKey) .BackStyle = fmBackStyleTransparent .PictureSizeMode = fmPictureSizeModeZoom .Visible = True End With Thanks in advanced for your help
Comment by: Peter Thornton (6-10-2022 13:48:00) deeplink to this comment
Hi Hervé If using FullWidth, in the declarations try increasing the mcFullWidth value, for example
Comment by: Yaro (16-12-2022 13:40:00) deeplink to this comment
Hi, there is a problem with treeview in access form when treeview is a subform and HighDPI mode is enabled on msaccess.exe. It seems that subform unexpectedly un-pin from form. Is there any issue resolved with that?
Comment by: Peter Thornton (16-12-2022 20:32:00) deeplink to this comment
Hi Yaro It’s true our original free Treeview doesn’t handle non-standard DPI particularly well cosmetically, however I don’t think we’ve ever received any reports of what you describe. If this occurs with the original/unmodified demo please describe the steps to reproduce. If it only occurs in your project, if you can email me a stripped down version with instructions I’ll have a look.
Comment by: Marcus (13-1-2023 15:57:00) deeplink to this comment
Excellent update to the Treeview control, Jan. How would you suggest handling different screen resolutions to provide a more consistent display of icons. For example, I have a high resolution laptop (3840 x 2400), but also need to use the Treeview on screens with a far lower resolution. On high resolution screens, the icons are tiny. Would you suggest storing several icon sizes and swapping between them based on the screen's resolution (and zoom level) or something else perhaps? with thanks
Comment by: Jan Karel Pieterse (13-1-2023 17:17:00) deeplink to this comment
Hi Marcus, I suggest to try adding one higher resolution icon to an image control with the image control set to zoom. Then create multiple copies of various sizes of that control, simply in design mode on a temporary userform. That way you can play with the picture related settings of the image controls to see what comes out best on various screen sizes. Your challenge is going to be to reliably get the resolution of the screen on which your firm is shown.
Comment by: Filipe Caetano (15-1-2023 11:46:00) deeplink to this comment
Hi. Please tell me what is new on build 26.5. I can't find any information on excel file about this build. Thank you very much.
Comment by: Jan Karel Pieterse (15-1-2023 12:09:00) deeplink to this comment
Hi Filipe, To find out what is new, look for "Narrator" and/or "reader". It is a temporary textbox which we added to make sure screen reader software understands the treeview and reads out the caption of the active node after you select it. This is an important accessibility feature for people with eye sight issues.
Comment by: Filipe Caetano (16-1-2023 10:38:00) deeplink to this comment
Thank you very much! Great addition to the treeview control. Keep the outstandin work. Best Regards.
Comment by: Mike Neely (19-2-2023 22:12:00) deeplink to this comment
Hi, Trying to use your control in Access 365 but I'm getting User Defined Type Not Defined. I added MS Forms 2.0 to my references but still not working. Would appreciate your help if possible, willing to donate more.
Comment by: Jan Karel Pieterse (20-2-2023 13:57:00) deeplink to this comment
Hi Mike, Try this: - Delete the userform from your project - Copy modStartup from the demo access database into your project - Run the routine called "CheckUF" - Save your VB project - Compact the database
Comment by: jo van uffelen (29-3-2023 09:21:00) deeplink to this comment
where can i find the installation files of MS Forms 2.0 ? Treeview is not working
Comment by: Jan Karel Pieterse (29-3-2023 09:40:00) deeplink to this comment
Hi Jo, MSForms is part of the default Office setup. If you don't see the reference in your project, simply insert a userform and it will be checked. Removing the userform will not uncheck the box.
Comment by: jo van uffelen (29-3-2023 10:12:00) deeplink to this comment
is there a special procedure for adding extra icons to the frImages-frame? By using the toolbox I can add an extra image to the frImages-frame bit i cannot picture property.
Comment by: Jan Karel Pieterse (29-3-2023 11:50:00) deeplink to this comment
Hi Jo, In which application are you using the treeview? If in MSAccess you may have to draw the images on a regular userform window in Excel and then copy-paste them into the Access subform.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.
Please enter your name (required):
Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):
Your request or comment (max 2000 characters):
To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].
I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.