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:
Compile errors due to a difference in how the control libraries
are registered in 32 bits Windows' System32 and 64 bit Windows' SysWOW64
folders. If you distribute a file that was saved in 64 bit Windows,
containing one of the "Microsoft Windows Common Controls 6.0" (The Treeview
control is one of them) and with the reference set to "mscomctl.ocx",
people using 32 bit Windows will almost certainly have problems. At
best it could entail removing both the control and the reference and
replacing both, but at worst the user's Excel can crash when trying
to load the file and run the app.
The standard Treeview control, like all non built-in ActiveX controls,
cannot be used in 64 bit versions of Office.
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.
MAC Office and 64 bit
Office Compatible!
Our Treeview works in all versions of Excel, Access and Word; from Office
2007 to 2019 and 365, for Windows and Mac, 32bit and 64bit. It also works
in some earlier versions though you would need to adapt the demos made for
2007 and later.
Windows screenshot:
Treeview demo on Windows Excel
Mac screenshot:
Screenshot of treeview on Mac Excel 2011
Acknowledgements
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!
Copyright and Licensing
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:
Keep our comments in place.
Do not remove our names, url's or email addresses from the code.
Send us your praise and your comments.
Send us any functional additions you do to the treeview.
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.
Disclaimer
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.
Donations
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:
Download
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.
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:
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.
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.
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.
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
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.
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)
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.
Sub GetData3(cParent As clsNode, lCt As Long, _
ByVal lLevel As Long, rng As Range)
' PT, a recursively retrieve the node's decendent data
' but only if visiblle
Dim cChild As clsNode
lLevel = lLevel + 1
lCt = lCt + 1
rng(lCt, lLevel) = cParent.Caption
If Not cParent.ChildNodes Is Nothing Then
If cParent.Expanded Then
For Each cChild In cParent.ChildNodes
GetData3 cChild, lCt, lLevel, rng
Next
End If
End If
End Sub
call it like this -
Dim cRoot As clsNode
Dim lCnt As Long, lLevel As Long
For Each cRoot In mcTree.RootNodes
GetData3 cRoot, lCnt, lLevel, ws.Range("A1")
Next
You can simply adapt that in the "cmdGetData_Click()" demo
Re 4# If I follow I think that would be well beyond the scope of our Treeview.
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.
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.
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.
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...
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"
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.!
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.
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).
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:
Sub foo()
Dim cRoot As clsNode
Dim cNode As clsNode
Dim cTree As clsTreeView
Set cTree = New clsTreeView
With cTree
Set cRoot = .AddRoot(sKey:="Root", sCaption:="Root Node")
Set cNode = cRoot.AddChild("1", "1")
Set cNode = cNode.AddChild("2", "2")
Set cNode = FindNode(cRoot, "2")
End With
End Sub
Public Function FindNode(cNode As clsNode, vKey As Variant) As clsNode
Dim cFoundNode As clsNode
Dim cNextNode As clsNode
Set cFoundNode = cNode.GetChild(vKey)
If cFoundNode Is Nothing Then
If Not cNode.ChildNodes Is Nothing Then
For Each cNextNode In cNode.ChildNodes
Set FindNode = FindNode(cNextNode, vKey)
If Not FindNode Is Nothing Then Exit Function
Next
End If
Else
Set FindNode = cFoundNode
End If
End Function
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
mcTree.NodeAdd cNode.Key, tvChild, "my key", "my caption" ' optional icons
' or
mcTree.NodeAdd cNode, tvChild, "my key", "my caption"
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.
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.
#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.
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.
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 -
With mcTree ' a reference to the treeview
' code
.Refresh
Set .ActiveNode = cNode 'refers to a clsNode
'or in the demo
Set .ActiveNode = .Nodes("2.1.1")
.ScrollToView
If you notice in the demo the node with key "2.1.1" is the only node that's not shown at startup, hidden behind a collapsed button. However setting it to the activenode will ensure all it's parents are expanded.
Also, setting the activenode like this will highlight it with choice of colours depending on if the Treeview itself is or is not in focus.
Final detail, call .ScrollToView (see the documentation sheet for optional arg's) to ensure the activenode is in view (though not necessary with the node "2.1.1").
There are lots of other ways to refer to an existing node besides its key. For your purposes, while creating the nodes, assign and maintain a reference to the node you will eventually want to make active, then when done Set it to the Activenode to it as above.
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.
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)?
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:
With TreeControl
sngVisHt = .Height <<< Error Line
If .ScrollBars = fmScrollBarsBoth Or .ScrollBars = fmScrollBarsHorizontal Then
sngVisHt = sngVisHt - 15 ' roughly(?) width of horizontal scrollbar
End If
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?
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.)
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?
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?
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.
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.
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.
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.
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!!
Hi Greg,
Just to add to what Jan Karel suggested, the equivalent of
Me.TreeView1.Nodes(4).Selected = True
in our VBA treeview is
Set mcTree.ActiveNode = mcTree.Nodes(4)
Unless you are keeping very careful track of the index numbers of all your nodes, rather than .Nodes(4) set a reference to your particular node in one of several ways (see the documentation in the Excel download). You might also want to ensure the new Active node is both visible (it's parents are expanded) and is scrolled within view, eg
Set mcTree.ActiveNode = myNode
mcTree.ScrollToView myNode
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
Private mUF As ufTreeView
'User Defined type not defined' I notice your VBA has a Form 'ufTreeView' but can't see how to get that into my DB. Glad of your help
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
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.
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.
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.
@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:
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.
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!
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.
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.
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.
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!
Public Sub ActivateNodeByKey(sKey As String)
Dim cNode As clsNode
For Each cNode In mcolNodes
If cNode.Key = sKey Then
Set ActiveNode = cNode
Exit Sub
End If
Next
End Sub
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:
Set mfrTreeControl = mUF.Controls.Add("Forms.Frame.1", "frTreeControl", True)
in the Load_Form event of subtreeview.
Would you have a work around for this?
Thanks very much in advance and keep up the great work!
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.
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 ?
To change the signature of the Click event, find this line in the clsTreeview class:
Event Click(cNode As clsNode) 'Node clcick event
and modify it like so:
Event Click(cNode As clsNode, Button as Integer) 'Node clcick event
Then also change all occurences of:
RaiseEvent Click(cNode, Button)
and also change this:
Friend Sub NodeClick(ByRef oCtl As MSForms.Control, ByRef cNode As clsNode)
To:
Friend Sub NodeClick(ByRef oCtl As MSForms.Control, ByRef cNode As clsNode, ByRef Button As Integer)
Now make sure that you add that button variable to all instances of NodeCLick in both classes.
However, the mctlCheckBox_Click event that is supposed to trigger these events does not return the mousebutton. For that, you would have to use the MouseUp (or down) event instead.
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.
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
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.
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
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.
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
With moTree.Nodes
If Len(sKey) Then
100 .Add cChild, sKey
I use the treeview control to display the contents of a CustomXMLPart in a Microsoft Word project.
I may have a node with an xPath "...Apples[1]" and another node with the xPath "...APPLES[1]" The first node passes the code above without problems, however when attempting to add the second node the duplicate key error is thrown. It seems the control does not see a difference between "Apples" and "APPLES."
I'm not sure what kind of compare you key is using, but is there some way to modify your code so the key compare would distinguish this difference?
I've stetted this line:
100 .Add cChild, sKey
as follows:
100 .Add cChild ', sKey
and I no longer get the error and everything "seems" to work ok, but I am not familiar enough with the wizardry you've put together here to know or understand the consequences.
Thank you very much. I can send you my project with instruction for creating the sitution causing the error if you wish.
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
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.
@ 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.
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.
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:
For Each cNode in mcTree
If cNode.Checked Then
'Do something
End If
Next
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
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
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 ?
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
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?
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.
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.
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)
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.
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.
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.
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?
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?
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.
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
cNode3.Expanded = False
Also keep in mind internally the levels are numbered from zero for root nodes.
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.
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 -
Sub ShowBranchOnly(cNode As clsNode)
mcTree.ExpandToLevel 0 ' < change as required, eg 1
Set mcTree.ActiveNode = cNode
mcTree.ScrollToView cNode
ExpandBranch cNode
mcTree.Refresh
End Sub
Sub ExpandBranch(cNode As clsNode)
Dim cChild As clsNode
cNode.Expanded = True
If Not cNode.ChildNodes Is Nothing Then
For Each cChild In cNode.ChildNodes
ExpandBranch cChild
Next
End If
End Sub
Pass a reference to your "building" node to ShowBranchOnly say in your toggle button. You may want to remove or adapt the Set ActiveNode and ScrollToView lines. See ScrollToView in the documentation for more about its optional arguments,
You mentioned before you have a "large" treeview, I'm interested to know how large. We have ideas to make the current treeview to handle a very considerably larger number of nodes but it means complete redesign and a lot of work!
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.
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.
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
Set cNode = mcTree.Nodes("myBuildingKey")
Call ShowBranchOnly(cNode)
But there are lots of other ways, eg if you maintain a reference to the parent node, eg the root node and if only one it's be simply .Nodes(1), you can loop its childnodes
' loop first level nodes
For each cNode in cRoot.ChildNodes
If cNode.Caption = "Building" Then
' you've found your node so bail out then pass cNode
Re size: 8000 nodes is pushing the treeview well beyond its design limits! If you've got a powerful system it might just be OK, but be sure to test it on equivalent systems your users might have.
That said even with 8000 there are things you can do to help, in particular do not show all nodes when building the treeview, IOW set appropriate nodes as .Expanded = False. However if/as user expands nodes and the number of visible nodes grows, scrolling and expanding will become clunky, especially in less powerful systems (note controls are only created the first time they are required and that exponentially takes time). Also do not show lines with such a large treeview (to reduce the number of controls).
I'm interested though in your "large" treeview. As I mentioned we have a new design for the treeview that should handle 8000 nodes very easily, even in old systems. As so much work has gone into this and it would only be of interest for a small number of professionals we are thinking of making it available as a special "pro" version, hopefully to recoup some of the development costs!
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.
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?
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.
@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)
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!
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!
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)
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.
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.
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.
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:
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.
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?
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
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).
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.
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.
I have got a suggestion.
In clsTreeView the SpecialEffect is set to sunken.
If bInit Then
.SpecialEffect = 2 ' fmSpecialEffectSunken
Else
...
End If
It would be nice if that was an option that could be set by the programmer or that the special effect setting of subtreeview is used.
Just my 2 cents.
p.s.
I really like your Treeview.
It's such a relieve that this just works.
I've been trying to get Treeview (MSCOMCTL.OCX) work in Windows 7 with MS Access 2013 for over a day.
I've used various version MSCOMCTL.OCX and didn't succeed.
Selecting an menu item didn't trigger the onclick event.
Very frustrating.
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
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
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
Public Property Get propTree() As clsTreeview
Set propTree = mcTree
End Property
and in the second subform something like this
Private Sub Command0_Click()
Dim cTree As clsTreeview
MsgBox Me.Parent.propTree.Nodes(1).Caption
' or probably better ensure it exists
Set cTree = Me.Parent.propTree
If Not cTree Is Nothing Then
With cTree
MsgBox "Nodes.Count " & cTree.Nodes.Count
' your code here
' cTree.Refresh
End With
End If
End Sub
Suggest do not make an semi permanent module level copy of the reference in the 2nd subform, get it as & when needed only.
2. I hadn't looked at putting a treeview in a Report but in a quick try it appears to be exactly the same as installing in a normal form. Working with a backup of the demo I did this
- Create a blank Report
- From the Design tab, Controls, subform/subreport
- in the wizard, 'use and existing form or report'
- I selected subTreeview and gave it a name (though you'll probably want to start with your own subform based on the demo's)
Add the essentials in the form code to create the treeview and be sure to 'tear down' the treeview in the form's close event if not before
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?
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.
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.
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.
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
' TreeView Code on Top of your UserForm 'ufDemo'
Private WithEvents mcTree As clsTreeView
Private WithEvents mdTree As clsTreeView
Private mbExit As Boolean
'No Changes in Private Sub InitializeDemoIcons()
'I repeat only the important lines
Set mcTree = New clsTreeView
With mcTree
Set .TreeControl = Me.frTreeControl ' Demo Frame/TV
Set .Images = Me.frmImageBox ' Img Frame Method
' build Nodes
' ...
End With
'--------
'What's wrong with my cloned procedure, where I made only the following small changes ?
'Private Sub InitializeDemoIcons()
Set mdTree = New clsTreeView
With mdTree
Set .TreeControl = Me.frTreeControl2 ' Another Frame/TV
Set .Images = Me.frmImageBox ' Img Frame Method
' build Nodes
' ...
End With
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.
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?
All clicks on the tree are handled by this routine:
Friend Sub NodeClick
there is a staatement to check whether an expander was clicked:
If oCtl.Name Like "Exp*" Then
It is there where you would write code to add nodes to the tree.
Alternatively, you can add your own click event to the expander. You can see how to do that by checking out the mcTree_NodeCheck event in the demo form. It has two lines of code in the clsTreeView class. One in the declaration area:
Event NodeCheck(cNode As clsNode)
and one in the NodeClick sub:
RaiseEvent NodeCheck(cNode)
The first one enables you to add the event to the form where the clsTreeview class was added. The second one actually fires the event.
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.
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.
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?
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.
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".
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.
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
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?
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
....
Set nodFound = mcTree.Nodes(j)
set mcTree.ActiveNode = nodFound
nodFound.checked = True
mcTree.Refresh
...
...
I'm not sure I'm doing something wrong. If you could you please help me addressing to a solution I'd really appreciate.
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
the node is highlighted and right-clicking, fires up the event.
Basically the final effect is quite the same (except the background color). Node is set as ActiveNode and it's highlighted so final user can see at glance where his/her node is.
Though it seems weird that selecting a node and mouse right-click does not fire up its event. I need to investigate with more accuracy.
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
.Refresh
mcTree.EnterExit True ' or False
Change the True to False if the treeview is configured to be in focus. This will probably be a permanent fix for Access as it is difficult to determine with code if the treeview is in focus until an enter/exit event has been received, but if anyone knows how drop me a line!
Now for your particular question - ScrollToView does not change the activenode, working with the demo form try this
With mcTree
'code
.Refresh
.EnterExit True 'False
' .EnterExit probably not necessary this time but
' no harm to include it
Set cExtraNode = .Nodes(.Nodes.Count - 4)
.ScrollToView cExtraNode
Set .ActiveNode = cExtraNode
End With
This should scroll to show the 5th node from bottom and make it active.
To trap right click in your form, Raise the MouseUp event similar to the click event but include the Button argument.
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)
With mcTree
' code
'create the node controls and display the tree
.Refresh
' let the treeview know if it's in focus and
' highlight the activenode's backcolor or border as appropriate
.EnterExit bExit:=Not Me.ActiveControl Is Me.frTreeControl
' optionally put the treeview in focus, the frame's
' enter event will fire and highlight the activenode's backcolor
Me.frTreeControl.SetFocus
End With
For Access only
With mcTree
' code
'create the node controls and display the tree
.Refresh
' let the treeview know if it's in focus and
' highlight the activenode's backcolor or border as appropriate
.EnterExit bExit:=Not Me.ActiveControl Is Me.subTreeView
'' optionally put the treeview in focus, the subTreeView's
'' enter event will fire and highlight the activenode's backcolor
Me.subTreeView.SetFocus
End With
Once running the treeview's container frame (or subForm in Access) will receive Enter & Exit events and highlight the activenode appropriately.
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?
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.
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:
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.
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 -
Dim c As New Collection
s1 = "/ns0:CCInstaMap[1]/Name[1]"
s2 = "/ns0:CCInstaMap[1]/name[1]"
It would be difficult to rewrite to accommodate for case insensitive keys, and in doing so might add new problems for others as the whole point about keys is they should be unique, irrespective of case.
At least three workarounds come to mind, two of make use of other built in methods and properties (eg Tag) and a third where you maintain your own look up table. If you want to send something to test with I'll have a look (my address is in the headers).
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).
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:
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)
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
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:
Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ChkCh
End Sub
Sub ChkCh()
If lblC.Caption = "1" Then
MsgBox "Changed!"
lblC = 0
End If
End Sub
Private Sub TextBox1_Change()
lblC.Caption = 1
End Sub
Now run the form, I selected tree with icons. Click on some nodes: all works well go to the frame, make a change in the textbox, then click back on a different node on the tree. Note how the active node description below the form has not changed. Click again in the tree, the original node is activated and only now does the tree start behaving normal again.
Having put some breakpoints in the code shows that node click and node mouseup events are not firing.
Any quick fix for this?
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:
Public Property Set ActiveNode(oActiveNode As clsNode)
'Existing code
'Rais event goes here
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
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
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
Dim levelCurr As Integer
Then I set the variable and run the SQL but the variable does not pass to the SQL statement.
levelCurr = 1
strSQL = "select LocAsset, LocAssetDesc from dtbl_Hierarchy where LocAssetLevel = levelCurr"
Set rst1 = dbs.OpenRecordset(strSQL)
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.
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).
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
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?
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?
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 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!
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
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:
Sub Demo()
Dim oCell As Range
Dim lLevel As Long
Dim nParentNode As clsNode
Dim nNode As clsNode
For Each oCell In ActiveSheet.UsedRange.Columns(1)
For lLevel = 0 To ActiveSheet.UsedRange.Columns.Count
If oCell.Offset(, lLevel).Value <> "" Then
If lLevel > 0 Then
'Add node to last parentnode, then make current node new parentnode
Set nNode = nParentNode.AddChild(sKey:=oCell.Offset(, lLevel).Value, vCaption:=oCell.Offset(, lLevel).Value)
If oCell.Offset(1, lLevel + 1).Value <> "" Then
'Next row has child, change parentnode
Set nParentNode = nNode
End If
Exit For
Else
'Add new rootnode, make it current parentnode
Set nParentNode = clsTree.AddRoot(sKey:="Root", vCaption:="Root Node")
Exit For
End If
End If
Next
Next
End Sub
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.
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.
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?
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.
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!
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.
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...
Sub GetData1(cParent As clsNode, lCt As Long, ByVal lLevel As Long, rng As Range)
' PT, a recursively retrieve the node's decendent data
Dim cChild As clsNode
lLevel = lLevel + 1
lCt = lCt + 1
rng(lCt, lLevel) = cParent.Caption
rng(lCt, lLevel).ID = cParent.Key
If Not cParent.ChildNodes Is Nothing Then
For Each cChild In cParent.ChildNodes
GetData1 cChild, lCt, lLevel, rng
Next
End If
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".
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?
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.
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?
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...
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.
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.
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.
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
If Not moLastActiveNode Is Nothing Then
' about 23 lines of code
ElseIf Button = 2 Then ' new
MsgBox "my right click" ' new
End If
There are various of ways to go from here, eg call a routine in the clsTreeView (passing a ref to the node) to raise an event back to the form (similar to click) to create the context menu.
Generally easiest to create a commandbars popup, but can also use APIs to create a menu if say you want to include your own custom icons.
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."?
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.
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
I think I found it, concerning my problem with tree lines not showing up.
When drawing the lines, you use this code :
.BorderColor = vbScrollBars
Alas, the theme I'm using on my Win 7 uses the same color for scrollbars background and window background, thus drawing white tree lines on white background, which is not very visible...
Changing this line to
.BorderColor = 0
displays correctly black tree lines connecting the nodes.
Maybe vbWindowText would be a better choice for the border color ?
@ 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.
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) ?
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.
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.
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.
@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>
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.)
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:
set nd = forms("frmMain).mctree.activenode
IF CommandBars.ActionControl.Parameter = "Collapse" Then
CollapseBranch nd
Else
ExpandBranch nd
endif
Sub CollapseBranch(cNode As clsNode)
Dim cChild As clsNode
cNode.Expanded = False
If Not cNode.ChildNodes Is Nothing Then
For Each cChild In cNode.ChildNodes
CollapseBranch cChild
Next
End If
End Sub
Sub ExpandBranch(cNode As clsNode)
Dim cChild As clsNode
cNode.Expanded = True
If Not cNode.ChildNodes Is Nothing Then
For Each cChild In cNode.ChildNodes
ExpandBranch cChild
Next
End If
End Sub
But all this is doing is changing the icon in front of the node from - to + (or + to -), it does not actually collapse or expand the node.
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?
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.
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
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.
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.
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!
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!
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.
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
Set cNode = mcTree.Nodes("myUniqueKey")
Set mcTree.ActiveNode = cNode
mcTree.ScrollToView cNode, , bCollapseOthers:=True
Unless 100% your unique key exists be sure to use an error handler.
If you are not using keys see the "Extended Project Explorer" demo in the examples tab (top left this page), user can enter one or more letters which will find and select nodes whose captions match the search string.
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.
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).
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?
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?
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.
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?
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
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.
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)
the resultes were like:
%%CheckBoxNode8 Marlett a
so they were correct.
When I realized that checkboxes in "Demo treeview" and "Demo treeviews with icons" work COMPLETELY different way (but they shouldn't - both go through the same function UpdateCheckbox), I gave up.
Maybe it's because i've got excel 2003 and some functionalities were lost (or work another way) during conversion. It seems I have to go back to common controls SP6.
Anyway, thanks a lot
brgrds
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.
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!
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
Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode)
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!
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
Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode)
Dim s As String
' Validate user's manually edited node here
'code
With cNode
s = "Edit: " & .Caption & " <changed to> " & NewString & vbNewLine & _
"Key: " & .Key & vbNewLine & _
"Index: " & .Index & " Level: " & .Level
' .VisIndex might change with EnableLabelEdit bAutoSort
End With
Me.labInfo.Caption = s
End Sub
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.
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).
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.
Set cNode = cNode.AddChild(cRoot.Key & "_A", "2.2.1.1.1 level 5 with extra text to test scrollwidth")
Cut the text that follows “level 5”
2) Add a button to the form with the following code:
Private Sub CommandButton1_Click()
mcTree.ActiveNode.Caption = "This very long text is used to test if the horizontal scroll bar will be activated automatically!"
mcTree.Refresh
End Sub
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
mcTree.SetScrollbars bRecalcMaxTextWidth:=True
SetScrollbars(False) will also cater for updating scrollbars if resizing the treeview, eg while resizing the form with the mouse.
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
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.
@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.
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)
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)
Set rng = Evaluate(Mid(ws.Names(sName).RefersTo, 2))
and it works fine. This is certainly very minor problem and doesn't concern treeview at all.
The problem is with D&D within the same tree and with 2 trees (as Frank Russel mentioned). I understand that for 2 trees there are many obstacles (like "incomapatibility" of trees definition), so it requires quite a work for validation copying etc. and it is difficult to include these methods in general model object. I would be very grateful however for some code for the begining (leaving validation apart). I've got also some idea regarding right click context menu but it requires API functions. Is there a way to avoid this approach?
Regards
Jerzy
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!
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.
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.
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?
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.
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.
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.
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" (?)
Hi Jerzy,
First, adding and "positioning" a new node can be done with the tree.NodeAdd method, eg
With mcTree ' also tvFirst tvPrevious
.NodeAdd .ActiveNode, tvNext, , "new node"
' .Refresh ' if no more to add
End With
All your other objectives, if I follow, can be done with Move. You could put buttons on your form to "move" a node (with its children) vertically up/down the tree, or promote/demote nodes between levels, including rootnodes, even making a child a rootnode or a rootnode a child.
However, I see now the documentation is not correct about vBefore/vAfter for the Move method, these optional arguments should be a "Numeric index of an existing Node in the Destination node's collection of ChildNodes" but not a string Key. Eg to move a node with its children up or down:
idx = GetColIndex(cMove)
Set cParent = cMove.ParentNode
mcTree.Move cMove, cParent, idx -1 ' up 1
mcTree.Move cMove, cParent, , idx +1 ' down 1
This function will return the position of a node between it's siblings (need additionally to check not trying to move above the first or below the last child)
Function GetColIndex(cNode As clsNode) As Long
Dim i As Long
Dim cSibling As clsNode
If Not cNode.ParentNode.ChildNodes Is Nothing Then
For Each cSibling In cNode.ParentNode.ChildNodes
i = i + 1
If cNode Is cSibling Then
GetColIndex = i
Exit For
End If
Next
End If
End Function
If you need to position next to a new sibling when only its Key is known, get its index between its siblings with this GetColIndex function.
The "RootHolder" is not directly exposed but can get it as the .ParentNode of any RootNode. It's a clsNode though not in the Nodes collection. It's what holds it all together!
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
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:
gives child node instead of sibling (works ok for non-root nodes)
Second:
Call mcTree.NodeAdd(, tvprevious, skey, skey)
(omitting vRelative – rather tricky anyway – not a word in doc) works but adds node as a last nodes’ collection item.
I use workaround (addroot aggregated with move).
Copy and Move work fine with root nodes, so (including workaround) I have all basic functions working correctly.
Thanks
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.
Hi Peter,
below is the part of cmdAddSibling_Click() of your demo:
If cNode.ParentNode.Caption = "RootHolder" Then
'We have a root node, add another root node
Set cSibling = mctree.NodeAdd(, , _
"MyUniqueSiblingKey" & lNewItem, _
"New Sibling of " & cNode.Caption & " #" & lNewItem, _
vIcon1, vIcon2)
Any value of the second parameter - "Relation" (also tvchild) gives the same result: adds csibling as last.
Attempt to set the first parameter cnode.parentnode gives run-time error.
Regards
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
'If vRelationship = tvChild Or cRelative Is cRelative.Root
If vRelationship = tvChild Then
then call it like this
Set cSibling = mcTree.NodeAdd(cSomeRootNode, tvNext, etc
' or tvPrevious
Could you confirm if this works for your needs, and/or if the change causes a different problem. We will probably change this for the next version though probably not soon.
Alternatively you could use .AddRoot() as normal then use the Move method to place in the order required.
The possibility to add multiple Root nodes was added during development, prior to which we needed to prevent accidental adding of sibling roots. Your particular scenario got overlooked!
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:
If IsMissing(vRelative) Then
Should be:
If IsMissing(vRelative) Or mcolNodes Is Nothing Then
Now it works (tested).
Anyway, it was not a problem before (I found workaround) and if I wrote about it, that was becouse I think that kind of remarks might have some value for you. I work intensively with treeview and I keep collecting remarks (mainly regarding documentation). When I finish, if you wish, I will send them to you.
Regards
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.
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?
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...
You can use this function traverse the nodes and check for their key (if you gave them one) or their Caption:
Private function GetNode(sCaptionOrKey As String, bUseKey As Boolean) As clsNode
Dim cNode As clsNode
For Each cNode in mcTree.Nodes
If bUseKey Then
If cNode.Key = sCaptionOrKey Then
Set GetNode = cNode
Exit Function
End If
Else
If cNode.Caption = sCaptionOrKey Then
Set GetNode = cNode
Exit Function
End If
End If
Next
End Function
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!
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? ;)
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:
Private Sub Form_Resize()
' Disable screen updating
Application.Echo False
' Bypass errors
On Error Resume Next
So, I'm stumped. For now I'm disabling the detail size update and moving on, but I'm wondering if anyone might have some insight into what might be causing this issue.
@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.
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!!
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?
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.
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?
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!
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
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:
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
You would read the information of tblStudents and extract the gender information belonging to each node and color the nodes accordingly. Some air code:
Sub ColorNodes()
Dim oRS As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sSQL As String
Dim cNode As clsNode
Set cn = CurrentProject.Connection
Dim mcTree As clsTreeView
For Each cNode In mcTree.Nodes
Set oRS = New ADODB.Recordset
sSQL = "SELECT Gender FROM tblStudent WHERE StudentID = " & cNode.Key
oRS.Open sSQL, cn, adOpenStatic, adLockReadOnly
If Not (oRS.BOF And oRS.EOF) Then
'Student found
If oRS.Fields("Gender").Value = "Male" Then
cNode.ForeColor = vbBlue
Else
cNode.ForeColor = vbRed
End If
End If
Next
End Sub
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!
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.
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
' in the NodeCheck event
If cNode Is mcTree.ActiveNode Then
To ensure the user changed checkbox node is the ActiveNode look at mctlCheckBox_Click() in clsNode. There are 3 If/End-If's, swap the 2nd and 3rd If's to ensure the node is active before NodeCheck event is triggered.
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.
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
' adapt for Office 64
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Sub ToggleSubFormVis()
Static ShowHide As Long
ShowWindow Form_subTreeView.hwnd, ShowHide
ShowHide = IIf(ShowHide = 0, 1, 0)
End Sub
Keep in mind we are Excel guys here and this is the first thing that came to mind, maybe the experts in the Access forums may have a more conventional solution!
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.
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'.
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.
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.
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.
@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!
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)?
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?
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!
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.
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.
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.
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.
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.
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.
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
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)
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
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.
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.
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.
I want to click the button and show for example my knod "ABC899" in my treeview. And i also want to the mcTree_click event to run the action behind my knod.
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.
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.
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.
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,
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.
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
On clsNode code, Build 025, there is a bug (error) in the Previous Property Get procedure.
Yours is:
With Me.ParentNode
For Each cNode In Me.ParentNode.ChildNodes
i = i + 1
If cNode Is Me Then
Exit For
End If
Next
If i > 1 Then
Set NextNode = .ChildNodes(i - 1)
End If
End With
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.
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.
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.
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 :-)
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 :-)
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
Seems there is some roomm for improvement :-)
This appears to work:
'*************************************************
cNode.ImageExpanded = "GreenTick"
'*************************************************
mcTree.Refresh
'Kludge to update the icon
cNode.Expanded = Not cNode.Expanded
cNode.Expanded = Not cNode.Expanded
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.
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?
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!
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?
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.
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?
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' ?
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?
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
' -----------------------
' a) PT, original code DrpDwnProcedures_Change
' -----------------------
' sProc = ActiveSheet.Names("ProcsList").RefersToRange(idx, 1)
sProc = Range(Sheets(ActiveSheet.Name).Names("ProcsList").RefersTo)(idx, 1)
' -----------------------
' b) PT, original code SortProcListing
' -----------------------
' Set rng = ws.Names(sName).RefersToRange
Set rng = Range(Sheets(ws.Name).Names(sName).RefersTo)
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.
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?
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'.
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!
You can read the data as dumped from the tree with code like this:
Private Sub cmdReadData_Click()
Dim vData As Variant
Dim lLevel As Long 'levels are in columns
Dim lRow As Long
Dim nRoot As clsNode
Dim nNode As clsNode
Dim nParent() As clsNode
Dim lPrevLevel As Long
With mcTree
.TerminateTree
vData = ActiveSheet.UsedRange.Value
ReDim nParent(1 To UBound(vData, 2))
For lRow = LBound(vData, 1) To UBound(vData, 1)
If Len(vData(lRow, 1)) > 0 Then 'new rootnode
Set nRoot = .AddRoot("Root_" & Format(lRow, "00001"), CStr(vData(lRow, 1)))
End If
For lLevel = LBound(vData, 2) + 1 To UBound(vData, 2)
If Len(vData(lRow, lLevel)) > 0 Then
If lLevel = 2 Then
Set nNode = nRoot.AddChild(, CStr(vData(lRow, lLevel)))
Set nParent(lLevel) = nNode
Else
Set nNode = nParent(lLevel - 1).AddChild(, CStr(vData(lRow, lLevel)))
Set nParent(lLevel) = nNode
End If
lPrevLevel = lLevel
End If
Next
Next
.Refresh
End With
End Sub
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?
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?
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!
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.
/Icon Part/ | /additional Tag Label with fixed length showing the XML tag/ | /Node Caption/
It would be of great help to receive some code assistance how to integrate this new Tag Label control into your clsNode and clsTreeView classes.
The easier task would be to shift the clsNode controls and related EditBox to the right, but how to provide for a consistant representation of this new Tag Label and ist display?
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.
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
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.
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
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 ?
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.
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'.
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.
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?
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.
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.
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.
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!
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.
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!
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
Private Sub mcTree_NodeCheck(cNode As clsNode)
With cNode
If .Checked Then
.Tag = "Checked"
ElseIf .Tag = "Checked" Then
.Checked = True ' explain to the user?
End If
End With
End Sub
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?
Public Function FullCaptionPath(Optional separator as String = " ") As String
' PT, get all the grand/parent Captions
' assumes use of caption
Dim s As String
Dim cNode As clsNode
On Error GoTo errDone
s = Me.Caption
Set cNode = Me
While Err.Number = 0
Set cNode = cNode.ParentNode
s = cNode.Caption & separator & s
Wend
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).
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?
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.
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?
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.
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?
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.
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
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.
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! :)
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?
'clsTreeView - Build 0.26
Private Sub BuildRoot(bInit As Boolean)
...
With cRoot.Control
...
If msngTopLabel < mcFullWidth Then
.Width = mcFullWidth
.AutoSize = False
End If
...
Also, would you like me to post my updates when I'm done?
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.
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.
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.
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.
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.
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!)
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)
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.
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.
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
Private Sub mcTree_Click(cNode As clsNode)
Clicking or opening a node does not seem to do anything. Using another trigger like click on the frame does bring me the selected value from the treeview
@ 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.
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.
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.
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?
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.
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.
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.
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.
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.
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 -
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.
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)
mcTree.ExpandToLevel lExpansionLevel:=1, bReActivate:=False ' don't activate before calling refresh
mcTree.Refresh
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)
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
If Not IsMissing(cRoot.Caption) Then .Caption = cRoot.Caption
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)
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?
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 :)
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
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
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.
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.
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.
- 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
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.
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;
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.
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?
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"
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).
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
.ExpandToLevel 1
in the initialize section, I get an error of 'Object variable or With block variable not set'. Can you provide any suggestions as to what I am doing wrong? Thanks for any information you can provide.
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.
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 -
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.
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.
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.
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'.
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?
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.
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.'
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.
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:
' if the subForm contains a frame of images
Set mfrImages = Me.subTreeView.Form.Controls("frImages").Object
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?
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.
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
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.
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!
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
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.
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?
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.
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.
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
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.
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.
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?
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?
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:
' PT toggle expand/collapse with key Enter
If KeyCode = vbKeyReturn Then
If ActiveNode.Expanded Then
KeyCode = vbKeyLeft
Else
KeyCode = vbKeyRight
End If
End If
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
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.
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):
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.
'Populate the treeview with data from a table
Private Function GetDataFromTable()
On Error GoTo Error_Trap
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryMenu")
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
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
Set cNode = mcTree.NodeAdd(vRelative:=rst.Fields("Parent"), _
vRelationship:=tvChild, _
sKey:=rst.Fields("ID"), _
vCaption:=rst.Fields("Designation"))
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.
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
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?
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
Code like this adds the # of childnodes to the parent nodes:
Private Sub CommandButton1_Click()
Dim cNode As clsNode
For Each cNode In mcTree.Nodes
If Not cNode.ChildNodes Is Nothing Then
cNode.Caption = "(" & cNode.ChildNodes.Count & ")" & cNode.Caption
End If
Next
End Sub
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:
_______________________
| Node Bla (3)|
| Node Bla Bla (2)|
| Node Bla (10)|
| Node Bla Bla Bla (5)|
|______________________|
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.
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,
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:
Dim rootnode As clsNode
Set csTree = New clsTreeView
With csTree
Set .TreeControl = Me.frTreeControl ' tell it where to live
Call .NodesClear
.CheckBoxes = True
.RootButton = False
End With
Set rootnode = csTree.Nodes.item(1)
rootnode.Checked = True ' causes a crash if csTree.Refresh not done before this.
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:
Public Property Get ChildCount() As Long
If Me.ChildNodes Is Nothing Then
ChildCount = 0
Else
ChildCount = Me.ChildNodes.Count
End If
End Property
B. To clsTreeView I added two events to correspond to the MS treeview
Expand and Collapse events, as follows:
1. at the top clsTreeView, add statements:
Event NodeExpand(cNode As clsNode)
Event NodeCollapse(cNode As clsNode)
2. in Sub NodeEventRouter, add 3 statements to
the "If cNode.Expanded" block as follows:
If cNode.Expanded Then
<10 lines of code omitted>
RaiseEvent NodeExpand(cNode) 'new
Else 'new
RaiseEvent NodeCollapse(cNode) 'new
End If
In my form module, I catch these events with:
Private WithEvents csTree As clsTreeView
...
Private Sub csTree_NodeCollapse(node As clsNode)
...
End Sub
Private Sub csTree_NodeExpand(node As clsNode)
...
End Sub
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:
Dim rootnode As clsNode
Set csTree = New clsTreeView
With csTree
Set .TreeControl = Me.frTreeControl ' tell it where to live
Call .NodesClear
.CheckBoxes = True
.RootButton = False
End With
Set rootnode = csTree.AddRoot(sKey:=strID, vCaption:="ChartSet " & CSname)
Set csTree.ActiveNode = rootnode ' causes a crash if csTree.Refresh not done before this.
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.
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 :
if some_trigger_met then cNode.Checked = True
2. I have significant slowdown of treeview initialization on 400 rows. Mainly its occur with .Refresh . Is there any way to increase performance ? .
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).
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
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.
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
...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.
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.
The Label control caption does not support mixed font colours or anything like 'rich text'. Although nothing is impossible it would be extremely difficult!
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.
Private Function SearchTreeview()
Dim cNode As clsNode
Dim cParent As clsNode
For Each cNode In mcTree.Nodes
If InStr(1, cNode.Caption, Name of Textbox, vbTextCompare) Then
'Sets the backcolor of all corresponding nodes to light yellow
cNode.BackColor = RGB(255, 230, 153)
'Expands all parent nodes within the same hierarchy as the current node
Set cParent = cNode.ParentNode
While Not cParent Is Nothing
cParent.Expanded = True
Set cParent = cParent.ParentNode
Wend
'Checks whether the current node has child nodes and, if so, expands it
If Not cNode.GetChild(-1) Is Nothing Then
cNode.Expanded = True
End If
Else
'For non-matching nodes, collapses them and changes their back and fore
'colors to match the ones they had before
If Not cNode.GetChild(-1) Is Nothing Then
cNode.Expanded = False
'Blue color for parent nodes
cNode.ForeColor = RGB(86, 118, 157)
Else
'Gray color for child nodes
cNode.ForeColor = RGB(110, 110, 110)
End If
'White color that match with treeview's backcolor
cNode.BackColor = RGB(255, 255, 255)
End If
Next cNode
'Display all changes made above
mcTree.Refresh
'Clear memory
Set cNode = Nothing
Set cParent = Nothing
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!
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:
Dim cNode As clsNode
Dim cNodeExists As clsNode
Dim bNodeExists As Boolean
Dim lNewNodes As Long
Dim bNodeExists As Boolean
Set rst = CurrentDb.OpenRecordset("qryMenu")
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
Do
lNewNodes = 0
For Each cNode In .Nodes
rst.movefirst
While Not rst.EOF
If rst.Fields("Parent") > 0 Then
If cNode.Key = rst.Fields("Parent") Then
bNodeExists = False
For Each cNodeExists In mcTree.Nodes
If cNodeExists.Key = rst.Fields("ID") Then
bNodeExists = True
Exit For
End If
Next
If bNodeExists = False Then
cNode.AddChild rst.Fields("ID"), rst.Fields("Designation")
lNewNodes = lNewNodes + 1
End If
End If
End If
rst.MoveNext
Wend
Next
Loop Until lNewNodes = 0
End If
'Display the tree
.Refresh
End With
rst.Close
Set rst = Nothing
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.
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.
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?
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!!
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.
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.
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.
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
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
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.
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
Set mfrImages = Me.subTreeView.Form.Controls("frImages").Object
When I comment this line out, it next stops at
Me.axSpinButton1.Value = 20
with the same error 2683
Also, the code can't be compiled. This renders the entire demo unusable. Have I missed something? Hope you can enlighten me.
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?
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!
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).
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.
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.
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.
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?
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)
' If cNode.Level >= 3 Then
cNode.Expanded = False
A different way which you can do just after populating and before calling Refresh, or during runtime and also call Refresh
mcTree.ExpandToLevel 2
' mcTree.Refresh ' to update
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.
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.
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.
Set cChild = cNode.AddChild(sKey:=DLookup("ID", "TBL_Categories", "Parent=" & cNode.Key & " " AND Designation='New Category'"), vCaption:="New Category")
Then I let the user enter edit mode to change the node caption from "New Category" to something else
With mcTree
.EditMode(mcTree.ActiveNode) = True
.ActiveNode.EditBox True
End With
I have a function that will create the path to that child node:
Private Function BuildCategoryDescription() As String
Dim i As Integer
Set cNode = mcTree.ActiveNode
Do While Not cNode.ParentNode Is Nothing
i = i + 1
If i = 1 Then
BuildCategoryDescription = "[" & cNode.Caption & "]" & BuildCategoryDescription
Else
BuildCategoryDescription = "[" & cNode.Caption & "]" & " > " & BuildCategoryDescription
End If
Set cNode = cNode.ParentNode
Loop
End Function
But it keeps adding the "New Category" string instead of the one user edited.
How can code continue only after user edit child node caption?
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
Set cChild = mcTree.ActiveNode.AddChild(, "New Category")
Set mcTree.ActiveNode = cChild
mcTree.Refresh
'' set keyboard focus to the treeview if necessary
' Me.subTreeView.SetFocus ' < Excel
' Me.frTreeControl.SetFocus ' < Access
cChild.EditBox True
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.
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:
intVisIndex = cNode.VisIndex
For Each cNode In mcTree.Nodes
If cNode.VisIndex = intVisIndex + 1 Then
You could also use the built-in NextVisibleNodeInTree function, if calling from outside the clsTreeview change it from Private to Friend.
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.
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"
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)?
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
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" :)
With images stored as binary in a table you can write them back to file and load into the treeview, something like this:
'with ImageName and Blob as the field names for the image-names and binaries
sName = rst![ImageName]
sFile = "C:\" & sName & ".bmp"
byteArray = rst![Blob]
ff = FreeFile
Open sFile For Binary Access Write As ff
Put #ff, 1, byteArray
Close #ff
mcTree.ImageAdd LoadPicture(sFile), sName
In passing, with the 'pro' Treeview no need to round-trip to file, simply:
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.
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.
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.
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).
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.
Hello.
I know how to get all node's parent nodes by doing this:
Dim cNode As clsNode
Dim cParent As clsNode
Set cParent = cNode.ParentNode
Do While Not cParent Is Nothing
Debug.print cParent.caption
Set cParent = cParent.ParentNode
Loop
But how do I loop through all node's child nodes recursively?
With this code I can only list the first sublevel child nodes of some given node:
Dim cChild As clsNode
For each cChild in cNode..ChildNodes
Debug.print cChild.caption
next cChild
I would like to have something like "CheckTriState" that when we check a node, all child nodes and their own child nodes are also indefinitely checked.
You loop through childnodes recursively as you said:
Sub LoopChildren(cNode As clsNode)
Dim cChild as clsNode
If Not cNode.ChildNodes Is Nothing Then
For each cChild in cNode.ChildNodes
Debug.print cChild.caption
LoopChildren cChild
Next cChild
End If
End Sub
The (un)checking of childnodes is already available, try the Excel demo and you'll see it in action. The demo without icons uses tristate checkboxes, the demo with icons uses non-tristate checkboxes.
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.
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?
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.
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.
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?
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?
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!
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 :-)
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?
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.
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.
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?
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.
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 :
Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' PT a node label has been double-clicked, enter edit-mode if manual editing is enabled
Dim bDummy As Boolean
If moTree.EnableLabelEdit(bDummy) Then
moTree.EditMode(Me) = True
EditBox bEnterEdit:=True
Else
If Me.Tree.AppName = "frmSelectDocType" Then
Call frmSelectDocType.tvwFiles_DblClick
End If
' moTree.NodeEventRouter Me, "Caption", tvDblClick
End If
End Sub
#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.
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.
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
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.
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
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.
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.
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?
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?
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)))
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.
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
With rs
While Not .EOF
strKey = !NodeID & ";" & Nz(!NodeObjectType) & ";" & Nz(!NodeObjectName)
If !ParentID > 0 Then
TreeView0.Nodes.Add(getNodeIndex(!ParentID), tvwChild, strKey, !NodeText).ForeColor = 16711680 ', CStr(!NodeImage)
Else
TreeView0.Nodes.Add , tvwLast, strKey, !NodeText, CStr(!NodeImage)
End If
.MoveNext
Wend
End With
Is there a way to adapt this tool to load a treeview off of a single table set up in this manner? Using this method allows us to add new items to the tree very easily, with no worry as to whether we have enough levels built into the VBA code.
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
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.
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.
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.
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.
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.
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
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.
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:
'Some code that has set variable cNode to a node
cNode.ForeColor = vbRed
mcTree.Refresh
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.
You could loop all the Image controls and exit if/when you find it to return found, or test if it exists like this:
Function GotImage(sName) As Boolean
On Error GoTo errExit
GotImage = Not Me.frmImageBox.Controls(sName) Is Nothing
errExit:
End Function
As written frmImageBox is the name of the Frame on the demo form, so for your purposes adapt to refer to the Frame on your second form, myForm2.frmImageBox perhaps.
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.
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.
' PT string name or numeric index for the main icon key
On Error GoTo errExit
If Not IsMissing(vImageMain) Then
If Not IsEmpty(vImageMain) Then
mvIconMainKey = vImageMain
If mlIconCnt = 0 Then mlIconCnt = 1
End If
End If
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.
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
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.
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
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.
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
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)
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?
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?
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?
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 :)
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
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.
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:
#If VBA7 And Not Mac Then
Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" ( _
ByRef lpPoint As POINTAPI) As Long
Private Declare PtrSafe Function SetCursorPos Lib "user32.dll" ( _
ByVal X As Long, _
ByVal Y As Long) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32.dll" Alias "GetTickCount" () As Long
#Else
Private Declare Function GetCursorPos Lib "user32.dll" ( _
ByRef lpPoint As POINTAPI) As Long
Private Declare Function SetCursorPos Lib "user32.dll" ( _
ByVal X As Long, _
ByVal Y As Long) As Long
Private Declare Function getTickCount Lib "kernel32.dll" Alias "GetTickCount" () As Long
#End If
Also, "Statements and labels invalid between Select Case and the first Case" for:
If Not ActiveNode Is Nothing Then
Select Case KeyCode
#If Mac Then
If Shift = 2 And KeyCode = 85 Then
KeyCode = vbKeyF2 ' Ctrl-U = F2 edit
End If
#End If
Case vbKeyLeft
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)
Thanks for the prompt attention. Deleting "And Not Mac" and moving the "Select Case" statements fixed those problems.
#If Win32 Or Win64 Then
sngTickCnt = getTickCount
#Else ' Mac
sngTickCnt = Timer
#End If
failed because Wind64 is True on Macs. Inverting the test like:
#If Mac Then
sngTickCnt = Timer
#Else ' Win32 or Win64
sngTickCnt = getTickCount
#End If
#End If
appears to work. At least the Demos run on both my 32 bit Windows and 64 bit Mac systems. What a great bit of software! Thanks for making it available. Donation to follow.
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
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.
#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.
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.
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 :-)
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.
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?
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.
If you mean set a node as the active node without clicking on it -
Set mcTree.ActiveNode = cNode
where cNode refers to the node you want to activate.
In the pro version you can also do simply -
cNode.Activate
With both versions you might also want to call mcTree.ScrollToView to ensure the new activenode is in view.
Yes, mouse wheel-scroll is included in the pro treeview although there is a caveat for a small number of x64 users. Contact us if you want to try it and mention which version, Excel or Access.
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:
For Each cRoot In mcTree.RootNodes
GetData3 cRoot, 0, 0, 0, ws.Range("A1")
Next
Sub GetData3(cParent As clsNode, lCt As Long, _
ByVal lLevel As Long, lCount As Long, rng As Range)
' PT, a recursively retrieve the node's decendent data
' whether visible or not
Dim cChild As clsNode
lLevel = lLevel + 1
lCt = lCt + 1
rng(lCt, lLevel) = cParent.Caption
If Not cParent.ChildNodes Is Nothing Then
'If cParent.Expanded Then
For Each cChild In cParent.ChildNodes
If cChild.Checked Then
lCount = lCount + 1
GetData3 cChild, lCt, lLevel, lCount, rng
End If
Next
'End If
End If
If I follow you want to count all checked 'leaf' nodes in the entire tree(?). Try this -
' For Each cRoot In mcTree.RootNodes
' GetData3 cRoot, 0, 0, 0, ws.Range("A1")
' Next
Sub GetData3(cNode As clsNode, lCt As Long, _
ByVal lLevel As Long, lCntChecked As Long, rng As Range)
Dim cChild As clsNode
lLevel = lLevel + 1
lCt = lCt + 1
' rng(lCt, lLevel) = cNode.Caption '' dump all nodes
If Not cNode.ChildNodes Is Nothing Then
' a parent node, don't count if checked
For Each cChild In cNode.ChildNodes
GetData3 cChild, lCt, lLevel, lCntChecked, rng
Next
ElseIf cNode.Checked Then
' a 'leaf' node, count if checked
lCntChecked = lCntChecked + 1
'' dump checked leaf nodes only
'rng(lCntChecked, 1) = cNode.ParentNode.Key
'rng(lCntChecked, 2) = cNode.Key
'rng(lCntChecked, 3) = cNode.Caption
End If
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.
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.
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.
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")
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?
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.
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?
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.
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?
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
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.
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
If using FullWidth, in the declarations try increasing the mcFullWidth value, for example
Private Const mcFullWidth As Long = 1000
If not using FullWidth I don't think the truncated issue would occur, but let me know if that's not the case.
Yes, with variable sized or 'too large' images use of fmPictureSizeModeZoom is a good solution.
You might want to change mcIconSize As Long = 12 (12 is good for 16x16 pixels in a 100% display)
FWIW the Pro Treeview handles different screen display % sizes much better, and doesn't use a fixed width with mcFullWidth which can extend labels unnecessarily to the right of the treeview (albeit not visibly). Optionally the pro automatically resizes images for larger display settings.
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?
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.
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?
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.
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.
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.
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
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.
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.
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.
However what is happening is that the image is being inserted over the caption, pushing the caption onto the next line which is then obscured by the next node, rather than to the left of the caption.
Thanks! If you look at the Treeview Excel demo file, you'll see the images of a node are set when adding the nodes in question, have a look at "cmdAddChild_Click" in the userform code. I haven't tried adding node images after a node has been created, it might fail at that point perhaps?
I am trying to figure out how to use the demo. I have a simple access db that uploads files, trying to build something similar to Explorer structure or sharepoint. The ability to upload files in a tree structure, view the files, download, delete, etc. with the ouptu showing a interactive tree structure. How do I go about doing that with your demo? Thanks.
The demo shows you the technicalities behind how to implement this treeview into an Access project: What are the steps you need to take to add the treeview to the VBA project, how you populate the treeview, and how do you show the tree on a form.
How exactly you would integrate it into your own project is up to you of course.
Hi Jan,
first of all i'd like to thank you for the effort you do in Treeview For Access module
however, if you please create a video about how to add the tree view to a new form in a new database with a steps of all nessesaries needed to make it run
as you know not all users are profissional enouph to dive deep into the code
thank you
First, thank you for providing this alterantive to the TreeView control.
Second, after playing a bit with the treeview, I noticed that the cTreeView class do not offer any Sort method, thus, in case of multiple roots, it is impossible to sort the root nodes.
Hello, thank you for a great alternative I am thinking of converting to.
I have one issue that I am struggeling with, and that has to do with selecting the treeview nodes in code, like:
set cnode=mcTree.Nodes (SelectedValue)
set mcTree.ActiveNode=cnode
This works fine when I am on the forms code.
However, how can I do this from the code on a sheet? (I am making the line on the sheet and the treeview syncronize)
I cannot get the reference to the forms control right.
Appriciates any help or suggestion.
"..I am making the line on the sheet and the treeview syncronize"
Do you mean with code in your worksheet module you want to control the Treeview in the Userform, or perhaps do you mean you want to put the Treeview itself on the worksheet. Also what do you mean by "the line on the sheet" - what sort of line, and how would this line trigger code?
Hi,
It is very useful and inspiring. After redesigning the demo. Can i download or export the view for future use?
For example as an excel or word file.
Best regards
The demo has a button to write the current tree items to and Excel sheet.
If that isn't what you mean: If you've made changes to any item in the VBA project of the treeview workbook, all you have to do to keep them is save the Excel file.
We looked atyour tree controls some tome ago and found that only one line of the data in the node was displayed. Does it now wrap the text data? This is a requirement for the data we carry in the tree nodes. I look forward to hearing from you.
I'm afraid our treeview doesn't cater for multi-line labels. But because we offer you access to the code, you are welcome to add that functionality! You would probably have to start in the clsTreeview class, BuildNodeControls subroutine, starting at the comment that says "'The node itself".
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.
Comments
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.
ByVal lLevel As Long, rng As Range)
' PT, a recursively retrieve the node's decendent data
' but only if visiblle
Dim cChild As clsNode
lLevel = lLevel + 1
lCt = lCt + 1
rng(lCt, lLevel) = cParent.Caption
If Not cParent.ChildNodes Is Nothing Then
If cParent.Expanded Then
For Each cChild In cParent.ChildNodes
GetData3 cChild, lCt, lLevel, rng
Next
End If
End If
End Sub
call it like this -
Dim lCnt As Long, lLevel As Long
For Each cRoot In mcTree.RootNodes
GetData3 cRoot, lCnt, lLevel, ws.Range("A1")
Next
You can simply adapt that in the "cmdGetData_Click()" demo
Re 4# If I follow I think that would be well beyond the scope of our Treeview.
Great comments and thanks for your interest.
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:
Dim cRoot As clsNode
Dim cNode As clsNode
Dim cTree As clsTreeView
Set cTree = New clsTreeView
With cTree
Set cRoot = .AddRoot(sKey:="Root", sCaption:="Root Node")
Set cNode = cRoot.AddChild("1", "1")
Set cNode = cNode.AddChild("2", "2")
Set cNode = FindNode(cRoot, "2")
End With
End Sub
Public Function FindNode(cNode As clsNode, vKey As Variant) As clsNode
Dim cFoundNode As clsNode
Dim cNextNode As clsNode
Set cFoundNode = cNode.GetChild(vKey)
If cFoundNode Is Nothing Then
If Not cNode.ChildNodes Is Nothing Then
For Each cNextNode In cNode.ChildNodes
Set FindNode = FindNode(cNextNode, vKey)
If Not FindNode Is Nothing Then Exit Function
Next
End If
Else
Set FindNode = cFoundNode
End If
End Function
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
mcTree.NodeAdd cNode.Key, tvChild, "my key", "my caption" ' optional icons
' or
mcTree.NodeAdd cNode, tvChild, "my key", "my caption"
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:
' Set mcTree.ActiveNode = cChild ' could activate the new child
mcTree.ScrollToView cChild, 2 ' reset scrolltop if necessary to view the new child
Neither of these seems to do anything.
Can you give a tip?
We have managed to get every other item on our wishlist working and this one has us stuck.
We are not using checkboxes, or icons.
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 -
With mcTree ' a reference to the treeview
' code
.Refresh
Set .ActiveNode = cNode 'refers to a clsNode
'or in the demo
Set .ActiveNode = .Nodes("2.1.1")
.ScrollToView
If you notice in the demo the node with key "2.1.1" is the only node that's not shown at startup, hidden behind a collapsed button. However setting it to the activenode will ensure all it's parents are expanded.
Also, setting the activenode like this will highlight it with choice of colours depending on if the Treeview itself is or is not in focus.
Final detail, call .ScrollToView (see the documentation sheet for optional arg's) to ensure the activenode is in view (though not necessary with the node "2.1.1").
There are lots of other ways to refer to an existing node besides its key. For your purposes, while creating the nodes, assign and maintain a reference to the node you will eventually want to make active, then when done Set it to the Activenode to it as above.
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:
With TreeControl
sngVisHt = .Height <<< Error Line
If .ScrollBars = fmScrollBarsBoth Or .ScrollBars = fmScrollBarsHorizontal Then
sngVisHt = sngVisHt - 15 ' roughly(?) width of horizontal scrollbar
End If
MS Access version: 2003 SP3
Excel Demo works OK.
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:
2. Edit clsTreeview and add the following items:
In the declaration section at the top of teh class, add:
Anywhere convenient, add:
RaiseEvent DoubleClick(cNode)
End Sub
3. Now you can add a mcTree_doubleclick event in the userform, e.g. like this one:
MsgBox cNode.Caption
End Sub
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
in our VBA treeview is
Unless you are keeping very careful track of the index numbers of all your nodes, rather than .Nodes(4) set a reference to your particular node in one of several ways (see the documentation in the Excel download). You might also want to ensure the new Active node is both visible (it's parents are expanded) and is scrolled within view, eg
mcTree.ScrollToView myNode
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
Regards
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:
Dim cNode As clsNode
For Each cNode In mcolNodes
If cNode.Key = sKey Then
Set ActiveNode = cNode
Exit Sub
End If
Next
End Sub
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:
Set mfrTreeControl = mUF.Controls.Add("Forms.Frame.1", "frTreeControl", True)
in the Load_Form event of subtreeview.
Would you have a work around for this?
Thanks very much in advance and keep up the great work!
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:
and modify it like so:
Then also change all occurences of:
and also change this:
To:
Now make sure that you add that button variable to all instances of NodeCLick in both classes.
However, the mctlCheckBox_Click event that is supposed to trigger these events does not return the mousebutton. For that, you would have to use the MouseUp (or down) event instead.
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
If Len(sKey) Then
100 .Add cChild, sKey
I use the treeview control to display the contents of a CustomXMLPart in a Microsoft Word project.
I may have a node with an xPath "...Apples[1]" and another node with the xPath "...APPLES[1]" The first node passes the code above without problems, however when attempting to add the second node the duplicate key error is thrown. It seems the control does not see a difference between "Apples" and "APPLES."
I'm not sure what kind of compare you key is using, but is there some way to modify your code so the key compare would distinguish this difference?
I've stetted this line:
100 .Add cChild, sKey