Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Treeview control

An MSForms (all VBA) treeview

Introduction

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:

  1. 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.
  2. 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 it is time to develop a custom-made Treeview "control", that only uses the native Office forms controls. I started building this a couple of weeks ago and after some time 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):

Treeview controls
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 was tested (and works) on 64 bit Office. It also works on MAC office. Tested applications include:

Excel 2000
Excel 2003
Excel 2010
Excel 2010 (64 bit)
Excel 2011 (MAC Office)
Excel 2013 (32 bit)

Access 2003
Access 2010 (32 bit)
Access 2010 (64 bit)

Windows screenshot:

Treeview demo on Windows Excel
Treeview demo on Windows Excel

Mac screenshot:

MAC screenshot of our treeview demo form
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 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 have some rules:

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.

Download the treeview sample Excel workbook (including documentation) (build 026, 15 Oct 2015, downloaded 25,394 times)

Download the treeview sample Word document (build 026, 15 Oct 2015, downloaded 7,539 times)

Download the treeview sample Access database (build 025, 16 Oct 2013, downloaded 20,444 times)

‘Pro’ Treeview and ListGrid VBA controls

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.

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.

Professional ListGrid control

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 to try either of these ‘controls’ please contact us:

Pro Treeview enquiry

Pro Listgrid enquiry

Please note that the pro versions are paid versions. Pricing available upon request by using the links above.

Content

 

Other controls

Another often used control is the calendar control. This control has the added problem that it has been deprecated with Office 2010 (where we're supposed to use the date picker control). Frankens Team created an all-vba alternative using very similar techniques to what we've done here.

Ron de Bruin created a Date Picker control for MAC Excel.

We are currently developing a listgrid/listview control. Contact us if you would like to participate in the beta program. Note that this control will not be made available as freeware.

 


Comments

All comments about this page:


Comment by: Benzadeus (2/22/2013 11:12:43 PM)

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 (2/23/2013 11:09:01 AM)

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 (2/23/2013 11:52:15 AM)

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 (2/23/2013 3:29:20 PM)

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 (2/23/2013 7:19:09 PM)

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 (2/23/2013 7:59:14 PM)

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 (2/23/2013 8:44:25 PM)

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 (2/25/2013 11:04:22 AM)

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.

Great comments and thanks for your interest.

 


Comment by: Carlos Manteigas (3/1/2013 12:02:09 AM)

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 (3/1/2013 3:33:40 PM)

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 5:57:38 PM)

Stupendously excellent ultra mega hyper Good!!

Love it!

Lisa

 


Comment by: Jan Karel Pieterse (3/3/2013 7:37:51 PM)

Hi Lisa,

Thanks! :-)

 


Comment by: Stefan Hellwig (3/6/2013 3:26:48 PM)

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 (3/6/2013 7:31:54 PM)

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 (3/7/2013 11:31:21 PM)

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 (3/8/2013 1:20:21 PM)

Hi Hermanito,

Thanks for reporting!

 


Comment by: Peter Thornton (3/8/2013 5:10:03 PM)

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 (3/14/2013 4:12:17 AM)

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 (3/14/2013 8:59:06 AM)

Hi Comet,

Thanks for reporting, we'll fix that for sure!

 


Comment by: conim (4/2/2013 12:04:45 PM)

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 (4/2/2013 1:54:31 PM)

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 (4/3/2013 10:19:08 AM)

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 (4/3/2013 12:01:23 PM)

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:
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

 


Comment by: Peter Thornton (4/3/2013 1:29:40 PM)

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 (4/3/2013 1:36:44 PM)

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 (4/3/2013 8:13:56 PM)

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 9:08:55 AM)

Hi Alex,

Thanks a lot for your comments, much appreciated!

 


Comment by: Peter Thornton (4/4/2013 1:04:01 PM)

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 (4/17/2013 12:41:52 PM)

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 (4/17/2013 1:43:18 PM)

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 (4/20/2013 10:24:29 AM)

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 (4/20/2013 9:13:06 PM)

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 (4/21/2013 9:10:11 PM)

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 (4/25/2013 1:01:37 PM)

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 (4/25/2013 2:12:55 PM)

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 (4/25/2013 4:12:46 PM)

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 (4/25/2013 4:14:39 PM)

Yes, it is better to stop.

I'll ask Ben to send you a copy.

 


Comment by: A Programmer (4/29/2013 7:27:43 AM)

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 (4/29/2013 9:44:14 AM)

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 (4/29/2013 2:12:43 PM)

@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 (4/29/2013 10:23:30 PM)

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 (5/1/2013 1:40:30 PM)

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 (5/19/2013 3:56:20 AM)

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 (5/20/2013 12:19:11 PM)

Hi mGRIFO,

Can you perhaps try to explain why you would want that?

 


Comment by: Roy Lasris (5/22/2013 2:26:06 PM)

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 (5/22/2013 5:07:09 PM)

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 (5/22/2013 5:24:09 PM)

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 (5/22/2013 5:45:17 PM)

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 (5/24/2013 11:45:46 AM)

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 (5/24/2013 12:42:56 PM)

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 (5/24/2013 12:55:38 PM)

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:

moTree.NodeDoubleClick Me


2. Edit clsTreeview and add the following items:

In the declaration section at the top of teh class, add:

Event DoubleClick(cNode As clsNode)


Anywhere convenient, add:

Friend Sub NodeDoubleClick(ByRef cNode As clsNode)
    RaiseEvent DoubleClick(cNode)
End Sub


3. Now you can add a mcTree_doubleclick event in the userform, e.g. like this one:

Private Sub mcTree_DoubleClick(cNode As clsNode)
    MsgBox cNode.Caption
End Sub

 


Comment by: Claude Berman (5/27/2013 5:17:23 AM)

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 (5/27/2013 8:32:24 PM)

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 (5/27/2013 9:00:10 PM)

Hi Roy,

Thanks for letting me know (and you're welcome!)

 


Comment by: Jan Karel Pieterse (5/28/2013 3:49:59 PM)

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 (5/30/2013 11:34:14 PM)

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 (5/31/2013 12:09:50 PM)

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 (6/2/2013 1:29:44 PM)

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







 


Comment by: Jan Karel Pieterse (6/5/2013 8:01:37 AM)

Hi Billy,

Thanks, problem solved.

 


Comment by: Steve (6/13/2013 6:41:35 PM)

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 (6/14/2013 3:01:35 PM)

Hi Steve,

Third download link above perhaps?

 


Comment by: Andy Litchfield (6/16/2013 2:37:58 PM)

Perfect! I'll be a treeview expert by the end of the day... Thanks very much

 


Comment by: Jeff Silcock (6/20/2013 3:18:13 PM)

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

 


Comment by: Jan Karel Pieterse (6/20/2013 4:34:51 PM)

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 (6/20/2013 5:02:12 PM)

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 (6/26/2013 6:44:20 PM)

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 (6/27/2013 7:33:24 AM)

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 (6/27/2013 4:24:58 PM)

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 (6/27/2013 5:43:20 PM)

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 (6/28/2013 11:50:39 AM)

@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:

cNode.Expanded = false

 


Comment by: Jeff Silcock (6/28/2013 5:15:52 PM)

Yes that's done it. Added
cNodex.Expanded = false
immediately after the code to add the node (where x = node level) and it works a treat! Much appreciate the work you have done on this.

Regards

 


Comment by: José (7/8/2013 2:31:58 PM)

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 (7/8/2013 5:30:58 PM)

Hi José,

I'll ask my colleague who has more knowledge of Access to contact you.

 


Comment by: Rasmus (7/10/2013 10:08:37 AM)

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 (7/10/2013 4:15:19 PM)

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 (7/26/2013 2:44:04 PM)

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 (7/30/2013 8:29:05 PM)

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 (8/1/2013 4:57:08 PM)

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 (8/7/2013 4:51:56 PM)

I was wondering if you plan to do the same for listview control...

 


Comment by: Jan Karel Pieterse (8/8/2013 8:57:04 PM)

@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 (8/14/2013 4:08:45 PM)

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 (8/19/2013 4:48:23 PM)

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 (8/20/2013 2:13:15 AM)

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 (8/20/2013 8:52:58 AM)

Hi Don,

I guess you could add this to the clsTree class:

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

 


Comment by: Jan Karel Pieterse (8/20/2013 8:53:50 AM)

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 (9/1/2013 11:21:41 AM)

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 (9/1/2013 8:12:22 PM)

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 (9/11/2013 12:10:30 PM)

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 (9/11/2013 4:30:46 PM)

Hi Richard,

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.

 


Comment by: Justin Glasgow (9/11/2013 10:52:33 PM)

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 (9/12/2013 10:44:04 AM)

Hi Justin,

Good point!

 


Comment by: Richard Tawn (9/12/2013 11:01:46 AM)

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 (9/12/2013 11:11:34 AM)

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 (9/12/2013 2:55:00 PM)

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 (9/12/2013 4:29:25 PM)

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 (9/13/2013 7:50:49 PM)

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
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.    

 


Comment by: Adrian Price (9/15/2013 2:34:47 PM)

I want to add my name to the numbers of people that would like you to produce a ListView control :)
I do hope that you find the time to produce it - you must have spent hours on the TreeView control which I would like to use.
Many thanks for the work you have done.
Adrian

 


Comment by: Jan Karel Pieterse (9/16/2013 6:37:39 AM)

Hi Greg,

I'm afraid not much can be done, since we're using the add method of a collection with a key if you provide one. The add method is case insensitive unfortunately.

You can try changing the code in the NodeAdd sub in clsTreeview so it does not use the key (linenumber is indicated and equals 100) when adding the new node to the collection, it will still add the key to the clsNode class instance.

 


Comment by: Peter Thornton (9/16/2013 4:07:01 PM)

@ Justin
Simulating a ControlTipText window is not simple, if you mean say to show a borderless/captionless form near the node or mouse. That said it can be done with APIs. A different approach to force user to select from a choice might be to show a dropdown (combobox) over the node, populated with choices.

Much of the code to do that can be adapted from the “edit-box” code (the textbox that appears if you double click or press F2). It would be straightforward to add a node property to store an array of choices for the combo or pick up from elsewhere depending on selected node.

@Richard
Instead of working with the click event could you just simply use the mouse-up event for your purposes. As Jan Karel explained the mouse-up exposes the button directly (as does mouse-down). Typically there’s little effective difference in timing between click & mouse-up events, but generally better to use mouse-up. Reason, mouse-down “provisionally” selects the new node, but it's only fully activated if mouse is released while still over the node, IOW if user drags mouse off the node and releases mouse the previous active node is restored without exposing an unwanted click event. As Jan Karel flagged consider use of the keyboard and equivalent events.

@Greg
If you don’t particularly need Keys there’s no need to add them (they're optional), and best not if there’s a risk of attempting to add duplicates (unless deliberately testing). However if you want to include your Apples & APPLES as additional data you could use the node’s Tag property. Thereafter you could loop and search (case sensitive) all nodes for a given case sensitive Tag and in turn retrieve your node.

 


Comment by: Jan Karel Pieterse (9/16/2013 6:33:32 PM)

@Peter: You misunderstood :-)
Adding a controltiptext is straightforward, we're working on it.

 


Comment by: Peter Maclaurin (9/23/2013 12:54:22 AM)

Hi,

Just found your control and it looks just what I am looking for to replace the Microsoft Treeview in my Access application.

I have got the code to work in Access 2013 and managed to get it to read Major and Minor Categories from tables in the database (just two levels) with checkboxes. I'm now trying to set the checkboxes by code (ie. reading in from another table which were previously selected and saved - saves the unique key of the Minor Categories) and then testing the checkboxes on exit to see if any have changed and saving the changes (if necessary). I just can't figure out how to do this. I'm sure it's simple to a lot of people but I'm brain dead on this.

Great control by the way!
Peter

 


Comment by: Jan Karel Pieterse (9/23/2013 6:37:39 AM)

Hi Peter,

Not sure if this is what you're after, but the clsNode class has a Checked property you can interrogate. Also, the clsTreeview class has a nodes collection. So:

For Each cNode in mcTree
    If cNode.Checked Then
        'Do something
    End If
Next

 


Comment by: Peter Maclaurin (9/23/2013 10:48:40 AM)

Hi Jan,

Not sure what I'm doing wrong. I have attached the code to the click event of a button on the form and added:
Dim cNode as clsNode
What I get is Run-time error '438'
Object doesn't support this property or method

 


Comment by: Jan Karel Pieterse (9/23/2013 11:25:19 AM)

Hi Peter,

Hmm, my code should have read:

For Each cNode in mcTree.Nodes
    If cNode.Checked Then
        'Do something
    End If
Next

 


Comment by: Peter Maclaurin (9/23/2013 11:33:37 AM)

Hi Jan,

Works perfectly in my test database! Now to try to incorporate it into my database.

Thanks for everything.

Peter

 


Comment by: Richard (9/30/2013 12:18:08 PM)

Might be a big ask but... is there any possibility to add functionality to clsNode to enable/dissable a check box for the particular set node ?

Regards,

Richard.

 


Comment by: Jan Karel Pieterse (9/30/2013 1:47:14 PM)

Hi Richard,

You're right, it is quite a bit of work, as it involves:

- Adding the property
- changing the checkbox color to denote a disabled checkbox (if you're not using icons)
- Adding pictures for a disabled checkbox so it is visible to a user that checkbox is unavailable (if you're using icons) and making sure the correct picture is shown.
- Adding a reference to that new picture
- Checking for the property in the click event of the checkbox so we can cancel the click where needed

 


Comment by: Richard (9/30/2013 2:42:01 PM)

Actually all im after is to set a property during populate that determines before adding a node weather a checkbox is visible or not for said node, as I am not using images im not bothered about that side of things, what im saying is i want the checkbox completely absent for certain nodes and visible for others, for example setting or forgetting msngChkBoxPad before setting a node ? not sure if im in the correct place or not ?

Regards,

Richard.

 


Comment by: Richard (9/30/2013 5:45:23 PM)

Done it ! I added a property to clsnode called exp
changed the node poplulate if statement to if exp etc
I removed the checkbox function from root as i did not require it

Cheers for your work sir !
Richard

 


Comment by: Peter Maclaurin (10/1/2013 1:30:18 PM)

Hi Jan,

I have successfully incorporated the treeview into my database but I have one form that used to have both a treeview and a listview (both with checkboxes). I have replaced the treeview with your one but the listview is now the problem. I could also replace it with your treeview but I'm not sure if it is possible to have two on the same form?

Regards Peter

 


Comment by: Jan Karel Pieterse (10/1/2013 3:19:48 PM)

Hi Peter,

Yes, having two should be possible. Probably best to put them both on the userform, but I have not enough Access forms knowledge to know this. I'll ask Ben, who enabled the treeview to work on Access.

 


Comment by: Ben (10/1/2013 3:24:40 PM)

Peter -

Yes, it is possible - you would simply put down two subform controls, and set each subform's SoureObject = "subTreeView". Though this would point to single form named "subTreeView", it will be instantiated as a 2nd object and you can manage each subform and therefore the treeview independently of each other.

 


Comment by: Peter Maclaurin (10/2/2013 11:24:05 AM)

Hi Jan and Ben,

Took me a little while to figure out how to initialize both Treeviews on the same form but I can report it does work. Many thanks for that.

 


Comment by: Peter Maclaurin (10/7/2013 12:50:00 PM)

Hi Ben,

One further question. The tree is on one form and I need to be able to read through the nodes, getting their description if their checkbox is checked, from another form, which then adds these node description into a listbox on it. I do check to see if the form with the tree on it is open prior to trying to read the tree. This is the Listview control that I have replaced with your Treeview control. The old code used to read:
If IsLoaded("frmSales") Then
    For i = 1 To Forms!frmsales!ListView0.ListItems.count
     Me.lstFields.AddItem Forms!frmsales!ListView0.ListItems(i).Text _
     & ";" & Forms!frmsales!ListView0.ListItems.Item(i).SubItems(1)
    
    Next i

Hope that makes sense.

Regards Peter

 


Comment by: Jan Karel Pieterse (10/7/2013 2:56:10 PM)

Hi Peter,

You should be traversing the Nodes collection of the treeview on the other form. They have a Checked property.

 


Comment by: Peter Maclaurin (10/8/2013 10:35:57 AM)

Hi Jan,

Yes, I knew that but was having trouble working out how to get to the treeview from another form. I eventually managed to get a few brain cells to work and figured it out. In case anyone else has this problem the code looks like:
If IsLoaded("frmSales") Then
    For Each cNode In Forms!frmSales.mcTreeSalesFields.Nodes
     If cNode.Checked Then
Where IsLoaded is a function that checks if frmSales is loaded.

Many thanks for all your help. Peter

 


Comment by: Jan Karel Pieterse (10/8/2013 11:47:50 AM)

Hi Peter,

Thanks. I assume this means mcTreeSalesFields should be declared as a public variable?

 


Comment by: Peter Maclaurin (10/9/2013 3:15:23 AM)

Hi Jan,

Yes. If use the coding in the example you provided the TreeView is declared as a Public Variable in the declarations section. I just modified the name as I actually have two treeviews on that form.

Regards Peter

 


Comment by: Ben (10/10/2013 1:43:43 PM)

Peter,

Just as a FYI -

If you want to "early bind" your treeview variables, you could do so like this:

<code>
Dim frm As Form_NameOfMyForm

If IsLoaded("NameOfMyForm") Then
Set frm = Forms("NameOfMyForm")
For Each cNode In frm.mcTreeSalesFields.Nodes
...
Next
End If
</code>

The advantage is that you have assurance from VBA compiler that the form's property exists and can be validated. This coding pattern is effective in a larger VB project where you might have many forms and treeviews to keep track of and don't want to get runtime errors due to form being not loaded or change to the forms.

You also can 'early-bind' a subform in like manner:
<code>
Dim sfrm As Form_NameOfMySubForm
Set sfrm = frm.MySubFormControlName.Form
</code>

You can then reference sfrm.mcTreeview and this also enable you to disambiguate two things that would from same subform loaded twice such as sfrm1.mcTreeview and sfrm2.mcTreeview. You then don't have to change name on account of loading it more than once.

I hope that helps.

 


Comment by: Peter Maclaurin (10/11/2013 11:11:54 AM)

Thank you Ben. I agree that it is probably better to early bind.

Regards Peter

 


Comment by: Claude (10/20/2013 1:48:59 PM)

Hi, and thanks for publishing this, it's worked beautifully for managing a large space inventory (customers > buildings > floors > rooms). I didn't notice the recent update and am experiencing some corruption issues that may be caused by an old common controls treeview I haven't gotten around to fixing. It may also be related to the sheer size of the application, and it may be hitting the limits, just haven't figured it out yet.

I do have one silly question, or at least I feel that way about my inability to figure it out. I'd like to load the tree (4 levels) but initially just have the upper three nodes expanded. I've searched on code for the word "expand", but can't figure out the syntax to collapse the a node level for initial presentation. Is there a way to do this?

 


Comment by: Jan Karel Pieterse (10/21/2013 9:33:23 AM)

Hi Clause,

It is simple really. Nodes are expanded by default, so just for the nodes that you want not to be expanded:

cNode.Expanded = False

 


Comment by: Claude Berman (10/21/2013 1:18:11 PM)

Thanks Jan, I tried that: cNode4.Expanded = False in various places within the procedure that builds the tree. I tried it:
1. Before the .refresh at the end
2. After the .refresh at the end
3. Immediately after the set cnode4= statement

And just can't get it to work in the Access code. Is there somewhere else I can try?

 


Comment by: Peter Thornton (10/21/2013 3:20:15 PM)

Hi Claude,
Curiosity, could you give an idea of what you mean by "the sheer size of the application". How many nodes and how many controls are being created. If you have the "DebugMode = 1" compile constant in place (in project properties) the stats will appear on the form's caption.

 


Comment by: Peter Thornotn (10/22/2013 12:29:21 PM)

Hi Claude,

About your expansion problem, if you want to show only the first three levels you need to set the expansion property of all nodes in the 3rd level, like when you click an expander on the 3rd level you show or hide its parents in the 4th level. So instead of cNode4 try
cNode3.Expanded = False

Also keep in mind internally the levels are numbered from zero for root nodes.

 


Comment by: Claude Berman (10/31/2013 3:07:03 PM)

Thanks Peter, I'm having all kinds of fun with this thing, such as setting the colors of the last (detail) nodes according to state (green=inspection passed, etc.). Now, I'm wondering if there is a way to control the expansion level from outside the tree without actually rebuilding it. I'd like to have an option group with three or four toggle buttons meaning (account, building, floor, all) corresponding to the four nodes in the tree (disregarding the root). Thus, if the user clicks the toggle "building", the tree would expand all nodes higher and collapse all nodes lower. I could easily do this via rebuilding the tree (now that you showed the way), but in working with large data sets, it would help if there is a more efficient way. Thanks for all your free help, you really should sell this thing, it would be worth a reasonable sum.

 


Comment by: Jan Karel Pieterse (10/31/2013 4:34:16 PM)

Hi Claude,

Thanks! Not sure I understand what you're after though.

Regarding selling the tool: you can always make a donation :-)

 


Comment by: Peter Thornton (10/31/2013 5:31:44 PM)

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!

 


Comment by: Claude Berman (10/31/2013 9:36:21 PM)

Thanks Peter, and Jan I sent you guys a pitcher or two of Ale since you'll probably need it dealing with all my questions. Ok, how do I pass a reference to a particular node to this function? I have a tree (mcTree) sitting on the form. Sorry, I really need an Ale too.

 


Comment by: Claude Berman (10/31/2013 9:58:39 PM)

Peter, I forgot to answer your question concerning size vs. performance. I have a test database with 8000 rooms (showing in the tree) and performance really isn't bad. This corresponds to a very large hospital or a mid-size university. But, I'm not sure how it run on a normal user's machine since I run a very fast workstation with solid state drives in a stripped array (mozy.com backs it up nightly since this is dangerous). It slowed down a bit when I added some formatting via conditions to change the final node color, but I think it's more the impact of the if/then/else statements to determine the formatting. To further confuse the issue, each room can have multiple inspections and I created a "demo" tool for distributors that generates any number of randomly scored inspection records. These are then used prior to processing the tree to determine which space inventory to show. In other words, select a particular inspector, then I've got to run the tree with a different set of queries. I'm also doing similar stuff to generate a chart on the same form. It takes a couple of seconds on my machine usually, worst case 5-10 seconds to rebuild the tree and paint the chart. I really don't think I'd need or want a ground-up rebuild since the real work and time is mostly in the pre-processing I have to do. And, blush, when I started 20 years ago my profession was mechanical engineering, so I didn't completely normalize the data, this propagated over the years as this app was developed, so I have to waste some CPU cycles normalizing the data on the fly.

 


Comment by: Peter Thornton (11/1/2013 3:33:50 PM)

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!

Hey thanks for the beer - cheers :-)

 


Comment by: Claude Berman (11/8/2013 1:34:29 PM)

Hi and thanks Peter. I'm still stuck on this. In your second example, which is what I need since I'm trying to collapse the tree to a user-selected level (i.e. all node levels below a certain one - pick building from the option group and all buildings collapse so the only account and building nodes show:

My question is what is the syntax for reference the tree from outside the tree? I've tried everything I could think of, such as mcTree.nodes.cnode1, etc.? I'd composed a note the other day in which I asked if you would do this as an example for a fee. Jan has the email sent with download instructions and install instructions if you want to take a whack at it. But, perhaps I got distracted and failed to hit the submit button.

Otherwise, assume a reasonable price on an enhanced (pro) version, and you've got at least one customer. The tree itself seems to handle a large number of nodes quickly, I've slowed it down a bit with conditional expressions to determine formatting, and some expressions to calculate values (displayed in the tree itself), but even then, it's pretty quick. I have a partner application running on a lame Atom processor device used for inspections (an Acer W510 tablet). It synchronizes data and displayed the same inventory in your tree for selection of the room to inspect. It's the slowest device I have and, without any internal tree enhancements, such as expressions running during the tree-build loops, it displays the entire data set it a second or two.

 


Comment by: Dieter Samel (11/13/2013 6:16:48 PM)

Hi Jan,

My problem is the same as:

Comment by Mark Scicluna (01/09/2013 11:21:41 AM)
 
Unfortunately, your recommendation does not:
You probably have to remove the userform altogether and use the code in the example file to re-create it using Access 2000/2002.

Could you send Mark my email address or post the solution?

 


Comment by: Jan Karel Pieterse (11/14/2013 6:34:37 AM)

Hi Dieter,

I suggest you to open the Access demo file and click the "How do I...?" button on its main form. It has a lot of information on how to implement the treeview.

 


Comment by: Peter Thornton (11/14/2013 1:51:01 PM)

@Claude
I understand Jan Karel has contacted you and hopefully all is working for you now.

I'm surprised you can load and display 8000 nodes so quickly, particularly if lines are shown. But if so the current version should be fine for your needs and no need to 'pay' for our faster version! (still testing it)

@Dieter & Mark
We haven't tested in Access 2002 but I couldn't get it to work in 2000 even in a new project, at least not with the current internal arrangement. It works fine in 2003 but it seems only if built in 2003 (which is what the demo is compiled in).

IOW, you can distribute from compiled in 2003 to 2007+ but not (necessarily) from 2007+ to 2003. Not sure why but it seems to vary with different systems. There might be a simple trick for 2000/2 so if you or anyone figures it please let us know!

It is written up top only tested in Access 2003 and later. (Excel & Word all since 2000, Mac only tested in 2011)

 


Comment by: Dieter Samel (11/14/2013 3:04:05 PM)

my approach :
System : Win 7 with off 2003 SP3
An empty 2003 mdb created.
All objects without the forms subTreeview , ufTreeView imported.
Then the following references to FM20.DLL and it created the new ufTreeView .
Then I also have the subTreeView imported.

The same error !

Analogous to :
Set mfrTreeControl = mUF.Controls.Add ( " Forms.Frame.1 ", " frTreeControl ", True )
I have manually tried to insert a frame in the ufTreeView .
I got an error message:
 
Microsoft Visual Basic
! not implemented

The VBA code throws the following error: -2147286785 Invalid option

This works in all other development environments !

Appears frequently in conjunction with the FM20.dll a fault :
Not enough memory !

I put on the line during debugging
Set mfrTreeControl = mUF.Controls.Add ( " Forms.Frame.1 ", " frTreeControl ", True )
a different value for the number 1 a , the error changes to :
Invalid class string

It seems as if in this particular installation , the frame is not supported!

Translation with Google from the German

 


Comment by: Dieter Samel (11/15/2013 10:05:02 AM)

Hello again,
A new installation with Win XP and Off2003 was created by me.
Now I'm sure the UserForm does not support FRAMES in MDB format MS Access 2002-2003.
In a 2002-2003-MDB I can not create a frame, and when I put it in a 2000 MDB, and then convert to the 2002-2003 MDB, leads the appeal or change the Frams errors!
As long as I'm on the same system using the same MDB than 2000 everything works. Unfortunately, I have but the 2003 format change to create an MDE!

 


Comment by: Peter Thornton (11/15/2013 11:28:08 AM)

Hi Dieter,

Does your system with Access 2003 also include multiple and later versions of Office. If so there might be some sort of conflict.

 


Comment by: Dieter Samel (11/15/2013 1:16:51 PM)


Hi Peter,

No my systems are pure virtual Fittings are installed with exactly 1 Office.
With friends adding frames is not even in the UserForm with a 2003 mdb with Off2003!

Incidentally created on this system 2003 MDE (yes, it is created) does not work there. But they can be used on any newer MS-Access without problems!

I have my clients now told him to install the runtime Acc2010!
Thus I will not provide support for this application in Off2003 first on 8 April ;o)

 


Comment by: TheSmileyCoder (11/18/2013 10:30:20 AM)

Very impressive piece of work.

Would it be possible to make the node click event handler include arguments on which mousebutton was clicked?

I would also hope to see a guide on how to add my own icons to the treeview.

Great job! Thank you all for sharing.

 


Comment by: Jan Karel Pieterse (11/18/2013 1:20:18 PM)

Hi TheSmileyCoder,

The default click event does not pass along mouse button states. Instead you should use either the MouseUp or the MouseDown event.
As it is now, in the MouseUp event we call the click event, which as I said does not pass mousebutton information. However, you can simply change that implementation by adding your own mouseUp event to the clsTree class and call that.

To find out how to add events, check out the clsTree class declaration section, there are 4 events set up there. Each one has a corresponding RaiseEvent codeline somewhere in the class.

This raiseevent line should be within a public sub in the class which in turn is called from the appropriate control event in clsNode.

Clear as mud?

Adding icons is very simple: add image controls to the (hidden) frame with icons, name them uniquely and use that same name when creating the nodes.

 


Comment by: TheSmileyCoder (11/18/2013 2:05:14 PM)

Hi Jan
Thank you for your swift reply. I must admit, at present I have only been playing with your treeview, to see if it is a potential solution for when/if my organisation switches to Access 2013 or office 64bit. I run alot of applications that use the treeview as a main component. I shall try to play with both the icons and the events.

Do you have any idea on the long term availability of the MS form? My fear would be spending lots of time implementing your treeview as part of my app, only to see MS forms be removed in OfficeNext.

 


Comment by: Jan Karel Pieterse (11/18/2013 2:36:06 PM)

Hi TheSmileyCoder,

I don't think anytime soon. msforms getting removed will only happen if VBA is omitted from Office. That is highly unlikely to happen soon.

 


Comment by: Sujoy (11/19/2013 9:27:21 AM)

Hi jan,

It seems that the 'control' property of node has not been implemented properly. I cannot access any properties of 'node.control' like "MyNode.control.backcolor" etc.

Kindly suggest a way to do it. I am working upon a wonderful VBA Code Review tool. I may share it in this site after completing.



Regards,
Sujoy (+91 8010189196)

 


Comment by: Jan Karel Pieterse (11/19/2013 11:09:59 AM)

Hi Sujoy,

Timing is important. Before the treeview has been refreshed, none of the controls exist so the Control's properties can only be accessed After the Refresh command was done.

To set properties of a control when you are building the nodes, you need a different syntax. Instead of:

cNode.Control.BackColor = vbRed


use:

cNode.BackColor = vbRed

 


Comment by: Duke Carey (11/26/2013 4:13:36 PM)

Jan/Peter -

After years & years of using the MS common controls tree view, I slapped together a quick Excel file this AM using yours and have to say I'm very impressed. Performance while loading and then traversing a nearly 1,000 node tree was pretty darned good. I'll be using it a lot more in the future.

Kudos to you both for the quality of this utility and for freely sharing the work product of what had to be many, many development hours.

 


Comment by: Jan Karel Pieterse (11/26/2013 5:30:42 PM)

Hi Duke,

Thanks!

 


Comment by: The Only Sheet (11/30/2013 6:16:07 PM)

This control is awesome to replace the annoying version from Microsoft!

There are two functionality that the original control has, that I have not seen (please correct me if I am wrong - and please let me know where to look to to find this!)

A) Is it possible to have the Mouse Wheel 'scroll' the scroll bar? Is there an option to turn this ON?

B) The original control allows the used to type part of an word to 'jump' to the first occurrence (if any) of this word. This is VERY useful for long lists. Is this doable? Is it available already?

MANY THANKS for your work on this!!

 


Comment by: Jan Karel Pieterse (12/1/2013 7:41:48 PM)

Hi The Only Street,

Very useful suggestions. Unfortunately, mousewheel scroll is quite hard to add. The other options shouldn't be too difficult.

 


Comment by: Seth (12/2/2013 7:42:23 AM)

I tried this with Windows 8.1 and Access 2013 64-bit and it gets run-time errors. I know you haven't certified it for this, but I am wondering if you are working on getting it running in this environment.

The first runtime error occurs in Form_subTreeView in the Form Load. I have a comment below on the line that gets the run-time error (there is no TabIndex property for ctl).

If Not moSubControl Is Nothing Then
    moSubControl.OnEnter = "[Event Procedure]"
    moSubControl.OnExit = "[Event Procedure]"
    Set mcolTabControls = New VBA.Collection
    On Error Resume Next
    For Each ctl In moSubControl.Parent.Section(moSubControl.Section).Controls
    lngIndex = ctl.TabIndex ' RUN-TIME ERROR OCCURS HERE
    If Err.Number = 0 Then
        If ctl.TabIndex > mlMaxTabIndex Then
            mlMaxTabIndex = ctl.TabIndex
        End If
        mcolTabControls.Add ctl.Name, CStr(ctl.TabIndex)
    Else
        Err.Clear
    End If
Next

 


Comment by: Jan Karel Pieterse (12/2/2013 4:26:43 PM)

Hi Seth,

I'll ping Ben Clothier (he has done the Access integration work on the control), see if he knows what is going on (I don't have a 64 bit 2013 installation).

 


Comment by: Ben Clothier (12/4/2013 3:47:51 PM)

Thanks for the comment, Seth.

There is something off here because if you look at the code, it's in a "On Error Resume Next" -- the fact you are getting a runtime error inside a Resume Next block suggests to me that there is something off with the VBE setting -- check your setting in the VBA editor:

Tools -> Options -> General tab

Ensure you've selected "Break on Unhandled Errors"

That should then let the code run through the Resume Next -- the error you saw is expected because not all controls have the TabIndex property which is why we're using the Resume Next & Err.Number = 0 to determine if we're going to add that control to the collection or not.

HTH.

 


Comment by: Paul Sondervan (12/17/2013 5:17:41 PM)

Is it possible to change add more space between the icon an the text?

 


Comment by: Jan Karel Pieterse (12/17/2013 6:13:58 PM)

Hi Paul,

Sure, in clsTreeView, at the top there is a number of constants which control the looks, this one seems to be what you need:

Const mcIconPad As Single = 14.25

 


Comment by: Paul Sondervan (12/18/2013 9:08:03 AM)

Hi Jan Karel,

I've adjusted the mcIconPad constant, even to a large value, but I didn't see any change.
After I changed FullWidth to true, it worked.
I would like to have it work when FullWidth = False.

 


Comment by: Paul Sondervan (12/18/2013 9:47:36 AM)

Hi Jan Karel,

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.

 


Comment by: Jan Karel Pieterse (12/18/2013 11:50:21 AM)

Hi Paul,

Thanks for the comments and compliments, much appreciated.
Of course you can implement the suggested improvements yourself :-)

 


Comment by: Peter Thornton (12/18/2013 3:37:33 PM)

Hi Paul,

Re Icon spacing: With the FullWidth option we're obliged to use a separate control for the icon, but it gives the advantage of being able to place the icon wherever we want. So for padding simply change the constant as Jan Karel suggested. But with larger treeviews with icons it's best not to use the fullwidth option, then the icon can 'share' the same control as the text, but we can't pad it. A workaround might be to add extra spaces before the text.

However, if treeview size and overheads is not the issue but you simply don't want fullwidth, continue to use the fullwidth option but comment anything that applies that full width value, eg


' .Width = mcFullWidth
' .AutoSize = False ' ensure autosize is always true


Re SpecialEffect: You could remove that altogether and configure the Frame's properties at design time.

 


Comment by: David Cushing (12/20/2013 8:14:29 AM)

I have been in the IT industry for almost 40 years and have designed many databases using Access and several mainframe DBMs.
Since my semi-retirement this year, I have not been doing much coding and I found I missed it.
All of that is a verbose explanation of my reason for developing a genealogy database for my family :)
I was using Microsoft's Treeview control and I got it working reasonably well but while I was browsing for help with making it work really well I tripped across the discussion of the Treeview control at eileenslounge and that led me to your Treeview replacement.

Downloaded it and spent a couple of hours earlier tonight getting it to work in my database.
Probably the easiest re-write I have ever done; your code is clean, clear and intuitively elegant. Great Job!!!!

My questions are:
1. Can I trigger a refresh of your embedded subform from another subform in the same parent?
It was easy to use the Click event in your subform to modify the data in my subform but now I want to click a button in my subform and force an event in your so I can rebuild the tree from a new starting point.
I feel like this should be relatively easy but I have been up too long and it is escaping me.

2. Can I get the control to work in a report?

Thanks for developing this control and for any help you may be able to offer.
Dave

 


Comment by: Peter Thornton (12/21/2013 12:29:36 PM)

Hi David,

Thanks for our comments. In answer to your questions -

1. All you need in your second form is a reference to the treeview, then you can control it exactly the same as in the main form. One way would be to start with this property in the main form


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

 


Comment by: Helen Feddema (12/28/2013 12:22:12 AM)

It took a lot of work to convert my old Treeview code to work with yours, but I got it working in 64-bit Access, so thank you for making this possible! Since I have a number of forms with similar Treeview controls in my database, I had planned to make a public Sub called from the forms' Load event which would build the Treeview, with arguments for the queries to use (the only thing that differs between forms). But when I placed the Public WithEvents mcTree As clsTreeview in a standard module, it turned red. I tried putting it into the clsTreeview class module, and it didn't turn red, but the code didn't run. Does this feature only work in a form module?

 


Comment by: Jan Karel Pieterse (12/28/2013 9:59:53 AM)

Hi Helen,

Events can only be declared in class modules and a userform is a class too. Modules are not.

 


Comment by: Helen Feddema (1/1/2014 11:02:50 PM)

I have tested my sample database that uses your Treeview alternative in 32-bit Access 2013 running on 64-bit Windows 7, and it works fine in that environment. I may be able to test it in 64-bit Access 2013 soon, and I will post a message about that test.

 


Comment by: Jan Karel Pieterse (1/2/2014 6:46:00 AM)

Hi Helen,

Thanks for letting us know!

 


Comment by: Ian (1/6/2014 6:22:21 PM)

Jan, this is awesome. It would be amazing if you are ever able to find the time to create a version of the listview control.

 


Comment by: Duncan Broom (1/9/2014 9:22:14 PM)

This is great.

We're starting to have new machines introduced with Access 2013 (which doesn't work with Treeviews, presumably a mscomctl issue...). The test file works great, so I'm very hopeful!

I'm surprised the NodesAdd functions were housed in the treeview class, rather than creating a custom Nodes class (Collection wrapper) and storing the Add/Remove/Clear/etc methods to that class.

 


Comment by: Jan Karel Pieterse (1/10/2014 11:16:59 AM)

Hi Duncan,

Perhaps splitting this into three classes might have been neater, I don't know. As a side effect, it currently only takes copying two classes into your project to make it work :-)
I would expect the old treeview to work in Access 2013, as long as you install the 32 bit version.

 


Comment by: Thanapol Rojanapanpat (1/17/2014 2:45:10 AM)

It is easy to integrate and works in both 32bits and 64bits MS Word 2010 on WinXP and Win8.

 


Comment by: Tony Matyas (1/23/2014 9:04:17 AM)

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

 


Comment by: Jan Karel Pieterse (1/23/2014 10:08:00 AM)

Hi Tony,

Am I right that you now have two subs called "InitializeDemoIcons"? That is not allowed. You should combine the code in both subs into one.

 


Comment by: Tony (1/23/2014 7:25:55 PM)

Hi Karel,
no, of course I did name the second sub differently InitializeDemoIcons2(), just omitted the '2' in my posting.
I would appreciate testing your ufDemo Code with this small modifications.

 


Comment by: Jan Karel Pieterse (1/24/2014 7:06:48 AM)

Hi Tony,

If you can send your modified demo to me I'll have a look.

 


Comment by: Steve (2/14/2014 9:14:15 PM)

Getting your Treeview control working in my Access application was straightforward. Just took a few edits to replace the ActiveX Treeview control properties and methods. Thank you so much for this wonderful work.

My application dynamically populates the child nodes when the parent nodes are expanded, instead of populating the entire tree at once. For this strategy to work, I rely on the Expand and Collapse Events of the existing ActiveX control. How could I trap these events using your control?

 


Comment by: Jan Karel Pieterse (2/17/2014 6:58:40 AM)

Hi Steve,

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.

 


Comment by: Daniel (2/19/2014 10:31:58 PM)

Hi there,

great tool. Is is possible to store this as a dll file or something similiar so that I can load it as a library (Tools => References).
You know, like the old form was stored in the "Microsoft Windows Common Controls 6.0" if i"m not mistaken.

Thanks a lot!
Daniel

 


Comment by: Jan Karel Pieterse (2/20/2014 9:20:09 AM)

Hi Daniel,

Well, not easily that's for sure. We tried to package this as much as we could so implementing it is as easy as possible.
One great advantage of having to include it in your VBA project is that it makes your solution self-contained. No need to ensure your users have any special dll's installed.

 


Comment by: Steve (2/21/2014 3:49:20 AM)

Wanted to share my experience after experimenting with the Treeview control for about a week.

Consider the scenario where the Treeview Userform is embedded on a form (just like frmDemo in the sample file), call this Form A.

Form B is a data entry form bound to a table.

Both Form A and Form B are subForms on a Master form. The Treeview nodes are populated on the load event of the Master form.

If the user first clicks on a field in Form B, everything functions as expected.

If the user first clicks on a node on the Treeview in Form A, the fields on Form B become locked. More accurately, the fields on Form B can receive focus, but they cannot be updated.

This behaviour has something to do with the Treeview container receiving focus before any other field does. It behaves as if it won't release the focus to any other control.

I've developed a bit of an exotic workaround, but was wondering if anyone has seen this strange scenario?

 


Comment by: Peter Thornton (2/24/2014 3:32:00 PM)

Hi Steve,

I'm not sure what's going on there wrt "the fields on Form B can receive focus, but they cannot be updated" or "It [the treeview container] behaves as if it won't release the focus to any other control".

We deliberately include stuff related to focus for two reasons, if I briefly explain maybe that might shed some light on your situation:

1 When the treeview container is clicked that does not of itself set focus to its subform host, hence moSubControl.SetFocus in the container's click event.

2 To enable keyboard navigation with tab. When user tabs to the subform and it receives focus the userfom window is brought to the top so it can receive the keyboard. Then if user keys tab (on the treeview) focus will be passed to the next control in the "tab order" in the main form. See moSubControl.Parent.SetFocus (switch focus to the main form) and ctl.SetFocus (activate the adjacent control in the tab order)

If user with the mouse activates a different subform, or any control on the main form, it should gain focus in the normal way (the subform's exit event lets the container know its lost focus but only to give the active node a different highlight).

It's possible to have multiple subforms each with a treeview and switch focus between them, either with the mouse or tab. IOW there should be no problem with the treeview container or its subform releasing focus.

If the above does not lead to an obvious fix by all means send a file that replicates the problem you described, but please give full and clear instructions.

 


Comment by: Dennis (3/12/2014 1:39:17 PM)

I am running Access 2003 on a Windows 7 64bit machine.
When I try to open one of the treeview forms I get the error "Invalid flag error" and the code stops at "Set mfrTreeControl = mUF.Controls.Add("Forms.Frame.1", "frTreeControl", True)". When I reset in the code window the form opens showing just the icons in the treeview area. When I close the form I get another error "Object variable or With block variable not set". When I click debug the code stops at the form "fsubTreeview" Close procedure at "Unload mUF".

Any ideas would be greatly appreciated.

 


Comment by: Jan Karel Pieterse (3/12/2014 1:57:56 PM)

Hi Dennis,

Is this with an mdb of yourself or with the download example mdb?
If you open the demo mdb, press the "How do I?" button to get an elaborate explanation on how to implement the treeview in your projects.

 


Comment by: Diego F. Pereira-Perdomo (3/15/2014 5:42:04 PM)

Hi Jan,
Forget my previous comments.
Something happened with my Office, and after using the repair tool everything started working again as expected.
Regards,
Diego

 


Comment by: Jan Karel Pieterse (3/15/2014 5:46:03 PM)

Hi Diego,

Glad you could sort it out!

 


Comment by: Hakan (3/19/2014 8:10:43 PM)

Hi,

How can I select any node exist in tree with code ?

I used

.
.
.

set myTree.ActiveNode= anyNode
myTree.ScrollToView

.
.
.



but it is not highlight the item like when you click this item with mouse.Therefore not fire mouse click event of TreeView.

Simulate the click event

I added following line to above code



call myTree_click(anyNode)


Ok! It fires the event but still selected item is not highlighted.

Is there any solution for it ?

 


Comment by: Hakan (3/19/2014 8:23:31 PM)

Hi,

Forgot my last question.

I set focus to subform of treeview berfore set activenode. It's fixed

Thanx

Hakan

 


Comment by: Paul Sondervan (3/20/2014 9:59:28 AM)

The "download the treeview sample Access database" indicates that its build 025, 16 Oct 2013.
When I download this version it turns out to be build 024, 20 May 2013.
Is it possible to put the right download on the website?
Is there a history with the changes in build 025?

Regards,
Paul Sondervan

 


Comment by: Ermanno (3/25/2014 12:08:09 PM)

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.

Thanks again for your TreeView control.

Best Regards

Ermanno

 


Comment by: Jan Karel Pieterse (3/25/2014 12:23:15 PM)

Hi Ermanno,

Thanks!

I suspect if you put the Refresh command BEFORE setting the activenode it should work, but I'm not sure :-)

 


Comment by: Ermanno (3/25/2014 3:20:57 PM)

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


....
Set mcTree.ActiveNode = nodFound
nodFound.Checked = True
mcTree.ScrollToView nodFoumd
....


I get the node selected but no mouseUp event (nor nodeClick one).

On contrary if I apply the following code

....
Set mcTree.ActiveNode = nodFound
nodFound.Checked = True
nodFound.BackColor = vbGreen


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.

Thank you again

Ermanno

 


Comment by: Peter Thornton (3/26/2014 2:29:49 PM)

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.

 


Comment by: Peter Thornton (3/26/2014 4:47:49 PM)

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.

 


Comment by: Chris (4/9/2014 6:39:07 AM)

Last year there was a comment about you working on a version with ControlTipText. Is there any update? Has anyone else got a working example with the ControlTipText property?

 


Comment by: Jan Karel Pieterse (4/9/2014 7:14:31 AM)

Hi Chris,

The current build 025 has a controltipText property in the clsNode class.

 


Comment by: alberto (4/10/2014 5:10:24 PM)

Hi!
how to populate the tree with a recursive function? Using traditional ms tree control I use
Set objNode2 = tvw.Nodes.Add(id_father, tvwChild, id_child2, child2)
.

alberto

 


Comment by: Charles Sestrem (4/10/2014 9:05:34 PM)

Hello Guys, congratulations class, is very functional.

I am using the Treeview class, and am having difficulty changing the icon image selected at runtime.

For the name I am updating DESTS way.

Set cNode = mcTree.ActiveNode

cNode.Caption = "Charles".

And for image I'm using as well;

cNode.ImageMain = "My Icon"

but does not update

Can anyone help me ?

 


Comment by: Chris (4/10/2014 10:41:32 PM)

Yes, I found it in the Excel project - thank you. I have it working fine now. BTW, The Access example database doesn't contain it and is still on build 024.

 


Comment by: Jan Karel Pieterse (4/11/2014 11:46:47 AM)

Hi Charles,

You probably have to refresh the tree after changing the icon: mcTree.Refresh

 


Comment by: Jan Karel Pieterse (4/11/2014 11:47:40 AM)

Hi Alberto,

I see no reason why that shouldn't work with our treeview?

 


Comment by: Charles Sestrem (4/11/2014 4:01:40 PM)

Thanks for the feedback Jan Karel.

I'm using the mcTree.Refresh line, just had not posted the previous comment.

is a pity unable to update the icon.

My project need to use dynamic icons when the user makes a change of status.

example:

Status = Free -----> green ball
Status = Closed -----> red ball

My Code Example:



'Mycode Example

Sub REfreshTree()

If mcTree Is Nothing Then Exit Sub

Set cNode = mcTree.ActiveNode

Select Case Forms!FormPrincipal!Status.Column(0) 'Set Status

Case 1

cNode.Caption = Forms!FormPrincipal!Name
cNode.ImageMain = "ICON1"
cNode.ImageExpanded = "ICON1"

Case 2

cNode.Caption = Forms!FormPrincipal!Name
cNode.ImageMain = "ICON2"
cNode.ImageExpanded = "ICON2"

Case Else

cNode.Caption = Forms!FormPrincipal!Name
cNode.ImageMain = "ICON3"
cNode.ImageExpanded = "ICON3"

End Select

mcTree.Refresh

End Sub

 


Comment by: Jan Karel Pieterse (4/11/2014 4:35:10 PM)

Hi Charles,

Actually, you can update the icon, it just takes a bit of convincing to have the tree update it.

This seems to work:

cNode.ImageMain = "FLGUSA02"
cNode.Expanded = Not cNode.Expanded
cNode.Expanded = Not cNode.Expanded

 


Comment by: Greg Maxey (4/18/2014 3:37:21 PM)

I have used your tree view control in a utility for mapping content controls in Word. Mapped controls (their XML) can then be displayed in a userform using your control.

I am getting an error "duplicate key" and the source is in your clsNode Public Function AddChild procedure whenever my XML contains nodes with "similar" but "different" base names. For example the following XML will cause the error:

<?xml version="1.0"?>
<CCInstaMap xmlns="http://USAOCCInstaMap/GKM">
<Name xmlns=""/>
<name xmlns=""/>
</CCInstaMap>

Is their any way to make the key in your moTree.Nodes collection case sensitive? Currently it treats
"/ns0:CCInstaMap[1]/Name[1]" and "/ns0:CCInstaMap[1]/name[1]" as the same key when in fact they are different.

I'll be happy to share the utility with you if you need to conduct any test. Thanks.


    

 


Comment by: Jan Karel Pieterse (4/18/2014 7:24:22 PM)

Hi Greg,

Do you happen to have "Break in class modules" turned on?

 


Comment by: Peter Thornton (4/19/2014 12:01:16 PM)

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]"

c.Add 100, s1
c.Add 200, s2 ' error #457 duplicate key


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).

 


Comment by: David (5/12/2014 3:10:22 AM)

This functionality you have created is fantastic. For what I need, I would also need a 64 bit grid control with hierarchy capability (I currently use the iGrid).

Is this functionality available by any chance?

Thanks

 


Comment by: Jan Karel Pieterse (5/12/2014 10:03:31 AM)

Hi David,

Thanks!

I have no real plans to create such a control, it is a heck of a lot of work.

However, I've recently seen a chap who has managed to use (heaps and heaps of) API functions to backdoor himself into the Windows common controls after all, even with 64 bit Office:

http://homepage.bluewin.ch/submax/5201.html

 


Comment by: Eduardo (5/17/2014 10:51:15 PM)

Hi, sorry by my english, I speak spanish.
First,thanks you for sharing your knowledge. I have a question: How can I add an event by e.g (open a new form) making double click on a last child child node or on a check box (but the check box appears at all nodes, not only at last child node)

Thanks in advance

Regards

Eduardo

 


Comment by: Jan Karel Pieterse (5/18/2014 8:51:29 PM)

Hi Eduardo,

Do you mean have something respond to a click on a checkbox in the treeview?

 


Comment by: Eduardo (5/19/2014 2:07:56 PM)

Hi Jan,

Yes, e.g open a form or file after select a check box o double click at node. The form has a reference to the node or after select the node, this node refer to a path file

Thansks

Eduardo

 


Comment by: Jan Karel Pieterse (5/19/2014 2:34:25 PM)

Hi Eduardo,

Please see this comment:

Comment by: Jan Karel Pieterse (5/24/2013 12:55:38 PM)

To view the comment, click:

http://www.jkp-ads.com/Articles/treeview.asp?AllComments=True

 


Comment by: Jaap van der Sijp (6/3/2014 4:22:12 PM)

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?

 


Comment by: Jan Karel Pieterse (6/4/2014 7:57:33 AM)

Hi Jaap,

If I understand correctly, indeed setting the ActiveNode property does not fire a click event. You can do that by adding the raiseEvent line to the appropriate property in clsTree:

Public Property Set ActiveNode(oActiveNode As clsNode)
'Existing code
'Rais event goes here

 


Comment by: Jaap van der Sijp (6/4/2014 1:24:57 PM)

No, you understood wrong. But with a bit more testing I have found that it is a general issue and not related to TreeView particular:

When the user clicks on a control, and this first triggers a messagebox or dialog (for instance when an onExit check is performed for the previous control), then Windows only sets the focus of the control clicked, by raising a MouseDown event, but does not raise a MouseUp event for the control.

The user will have to click again to get the proper event chain going.

So to get the behaviour I want I can call the MouseUp event from the Mousedown event sub, but in the (less likely) case that the user didn't want to click and is trying to move the mouse away, (s)he gets the wrong behaviour (The onExit event is raised before the MouseDown event anyway).

Consider this topic closed/solved as it is a Windows issue, not a treeView issue

 


Comment by: Jan Karel Pieterse (6/4/2014 2:48:12 PM)

Hi Jaap,

Form events can be challenging indeed :-)

 


Comment by: Gilad (6/26/2014 6:12:38 AM)

This is so great. But is it safe to assume that the required references will work under any system? Different versions of Windows or of Office?
Thanks
Gilad

 


Comment by: Jan Karel Pieterse (6/26/2014 7:04:13 AM)

Hi Gilad,

Yes, because there are no references to external libraries involved! Just the default MSForms, Office and Excel

 


Comment by: Stephen Campain (7/1/2014 3:32:45 AM)

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)


Why is this?

Regards, Stephen

 


Comment by: Jan Karel Pieterse (7/1/2014 7:09:02 AM)

Hi Stephen,

The proper syntax is:

levelCurr = 1
strSQL = "select LocAsset, LocAssetDesc from dtbl_Hierarchy where LocAssetLevel = " & levelCurr"
Set rst1 = dbs.OpenRecordset(strSQL)

 


Comment by: Jaap van der Sijp (7/1/2014 4:08:04 PM)

Jan Karel, as promised a donation. I have a good working version.
image: https://lh5.googleusercontent.com/rjLHo-6sqzkzml1QDAm0aESxfzzRyypRb3Wqw9tCFi4=w333-h226-p-no

Any pointers as how to make the nodes mouse drag-able for move or copy? The Office VBA TreeView has a OLEDrag/Drop property, but simple controls don't have this.

 


Comment by: Jan Karel Pieterse (7/2/2014 11:26:52 AM)

Hi Jaap,

Thanks for the screenshot, looking good.

Peter Thornton has been working on a pro version of the treeview which would include drag&drop, but I'm not sure of the status.

 


Comment by: Peter Thornton (7/3/2014 9:47:26 AM)

Hi Jaap,

Drag and drop can work well with the treeview but have held back from making a demo for various reasons, partly as it's difficult to make a generic demo which typically will need considerable adapting for specific applications. In the meantime we'd be happy to help implement D&D in your app on a consultancy basis. If that might be of interest please contact either of us (emails inside the demo).

 


Comment by: luke (7/9/2014 8:10:30 AM)

hi,

I am trying to use the MSAccess version; this is/was an MDB. Whenever I tried to Design the main form frmDemo I get the error 'You do not have exclusive access to the database at this time'.

I am the only one opening it, is only open once on my local drive.

I tried upgrading it to Access2010 accdb , still same problem. Then tried to build the accdb from scratch and import all the forms and code (added the FM20.dll reference). Everything compiles ok, but still same message ;do not have exclusive access...'

I am using Win7 Pro on a 64bit machine, with MSAccess 2010 32bit

 


Comment by: Jan Karel Pieterse (7/9/2014 10:35:01 AM)

Hi Luke,

I'm no Access expert, but the Access demofile contains specific instructions on how exactly to implement the treeview in your own Access database. Perhaps that description gives you some pointers?

 


Comment by: Davide (7/18/2014 10:46:21 PM)

I cannot change or insert icons in the access version.
I can select the properties and try to change the image by selecting "..." but the new image is not loaded and the icon doesn't change.
Ther's a way to do that?

Bye

 


Comment by: Muneeb Mansoor (7/21/2014 9:04:26 AM)

Hi Guys,

First of all, amazing work on this. Exactly what I was looking for!

I have one question though. Is it possible to use a drop down menu as a child instead of static values? For example, I would have a parent node called Attributes and then multiple child nodes with the options to select values from a drop down. Possible?

Thank you.

 


Comment by: Jan Karel Pieterse (7/21/2014 9:01:38 PM)

Hi Davide,

I'm sorry, I am not sure how you are trying to add pictures, can you detail out the steps?

 


Comment by: Jan Karel Pieterse (7/21/2014 9:02:33 PM)

Hi Muneeb,

You could use the click event of a node to add new nodes to it at runtime if that is what you mean?

 


Comment by: Muneeb Mansoor (7/21/2014 9:29:52 PM)

Let me try to demonstrate what I'm try to say:

+ Parent 1
     |
     |
     - - - - Child 1 <-- I want this to be a drop down menu.

 


Comment by: Steven Elliott (7/28/2014 6:23:00 AM)

Thank you so much for this awesome treeview control!

I have a VBA app I developed in AutoCAD and I needed to update it for the 64-bit version. It is a block librarian that made extensive use of the MS Treeview control, which is not an option for 64-bit, as you well know.

Your all VBA Treeview is a blessing. It works great in AutoCAD. The only place I had any problem (so far -lol) is where you use the variable 'HostProject' such as Private Sub BuildRoot. That seems to be an MS Office variable that is not available in AutoCAD. I simply REMmed out the offending lines and hard-coded what I needed there, since this app won't run on other host programs anyway.

Great job on this accomplishment! And many, many thanks for making this available to others!

Sincerely,
Steven Elliott

 


Comment by: Hannu Metso (8/5/2014 3:21:54 AM)

How to make initalisation from worksheet data same data structure as dumped data in excel demo?
I want to builsd some default parts to make treeview
Thanks
Hannu

 


Comment by: Jan Karel Pieterse (8/12/2014 10:40:13 AM)

Hi Hannu,

The demo already shows how to add items to the tree. It is a matter of cleverly reading the worksheet and adding the items in a similar fashion.

Something like this:

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

 


Comment by: Jan Karel Pieterse (8/12/2014 10:54:24 AM)

Hi Steven,

Thanks and you're welcome!!

 


Comment by: Jan Karel Pieterse (8/12/2014 11:17:29 AM)

Hi Muneeb,

That would require modification of the control. It can be done, but we would have to start a (paid) project if you want assistance with that.

 


Comment by: Hannu Metso (8/14/2014 8:22:45 AM)

Thanks for code

Comment by: Muneeb Mansoor (7/21/2014 9:29:52 PM)
Let me try to demonstrate what I'm try to say:

+ Parent 1
     |
     |
     - - - - Child 1 <-- I want this to be a drop down menu

I am also keen on that

 


Comment by: Jan Karel Pieterse (8/14/2014 11:40:23 AM)

Hi Hannu,

Noted. But as I replied above, this would need to be a paid project as it deviates significantly from a normal treeview control.

 


Comment by: Siegfried Wieser (8/18/2014 8:08:34 AM)

Thank you very much for this awesome treeview control!

Is there a way to make the expander to "+" without children? At the moment i make "fakechildren" and remove them, when i expand to add other nodes.

Or maybe im completly wrong how i do this?


Sincerely

Siegfried Wieser

 


Comment by: Jan Karel Pieterse (8/18/2014 9:50:13 AM)

Hi Siegfried,

I'm not sure what you need; are you saying you'd like to have expanders regardless of whether the node has childnodes? I think that isn't how the Common controls treeview works, hence it isn't the way ours works.

 


Comment by: Siegfried Wieser (8/18/2014 11:38:46 AM)

Thank you for your quick response!

Yes, I need expanders regardless of whether the node has childnodes. Thats because i want to make the childnodes before the node expands. The Node_click event does a different job in my treeview. I have a lot of nodes and the childnodes are only needed when you expand them.


Thank you again for your awful treeview control!

Siegfried Wieser


 


Comment by: Jan Karel Pieterse (8/18/2014 11:41:37 AM)

Hi Siegfried,

You can set a nodes Expanded property to control whether or not it's childnodes are visible, is that what you are after perhaps?

Indeed, it is an awfully nice control isn't it? :-)

 


Comment by: Siegfried Wieser (8/18/2014 12:04:41 PM)

Hi Jan Karel,

if there is no Expander, the user has no Control to click at for expanding. As i mentioned, the Node_click event does a different job in my treeview. So how can the user expand the node - so that i can make the children?

Thank you very much for your quick Answers!

Siegfried Wieser

 


Comment by: Jan Karel Pieterse (8/18/2014 2:21:23 PM)

Hi Siegfried,

Ah, the treeview expects the childnodes to already be there.
The code is currently in a state that that is not easy to change.

This is the routine where the expander is added:

Private Sub BuildTree(.....

It is only called when a node has childnodes.

 


Comment by: Peter Thornton (8/20/2014 12:14:14 PM)

Hi Siedried,
I don't follow if the objective is to make nodes at design but not expanded, or "to make children"..?

A method to add new nodes during runtime is already built-in to the treeview, see the buttons on the demo form "Add Sibling" and "Add Child".

There are several ways you can provide on the UI for the user to add new nodes, eg a button on the form (as in the demo but with more details), a right click context menu on node, press a shortcut key on the activenode, etc.

 


Comment by: Frans Truyens (8/20/2014 6:38:09 PM)

Is it possible to use a double-click event in the treeview? I would like the user to be able to double-click on a node, and intercept that event

 


Comment by: Jan Karel Pieterse (8/20/2014 7:02:12 PM)

Hi Frans,

There already is a double-click event, but it is currently in use for node editing. You can change that relatively easy though.

 


Comment by: Miguel Mezquita (8/26/2014 9:05:38 PM)

Hi Jan,
Before anything, let me say thanks for sharing such an amazing piece of work.
I am working in a project where the user needs to compare three similar products and as the products are not identical, I think that I will show three trees at the same time. Also, every node has a picture.
Do you think that your component could implement these requirements?
Thanks again and look forward to hearing from you.
Cheers!

 


Comment by: Jan Karel Pieterse (8/27/2014 10:22:55 AM)

Hi Miguel,

You're welcome! Nice to hear that people enjoy our work.

Yes, you can have as many treeviews side-by-side as you like, as long as you do not exceed whatever it is a userform can handle regarding the total number of controls (which limit I do not really know). Three should not be a problem at all.

To do that, each tree needs its own frame (e.g. frmTree1, frmTree2, frmTree3) and its own module level variable, e.g. mcTree1, mcTree2, mcTree3.

The documentation describes how to handle icons.

 


Comment by: Hannu (8/29/2014 4:45:10 AM)

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

End Sub


Hannu

 


Comment by: Jan Karel Pieterse (8/30/2014 11:47:39 AM)

HI Hannu,

You should not be getting duplicate key errors, they are triggered by a setting in your VBA editor "Break in class modules" is not the usual setting it should be "Break on unhandled errors".

 


Comment by: Tobias Johnson (9/2/2014 6:08:10 PM)

Dear Jan,

first off all great, no outstanding work!!

One question. I would like to use it as a hierarchie for different countries within a region. Is there a way that a user can just douple click on an element ( Sibling or Child) and this will be lets say linked to a certain cell within Excel so I can perform some other calculations?

Thank you in advance for your feedback.

Tobias Johnson

 


Comment by: Jan Karel Pieterse (9/3/2014 11:51:08 AM)

Hi Tobias,

Right now the double-click event is used to enable editing a node in the treeview.

This is handled in the clsNode class by this event sub:
Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


You could remove this part:

        If moTree.EnableLabelEdit(bDummy) Then
            moTree.EditMode(Me) = True
            EditBox bEnterEdit:=True
        End If

and replace it by a call to a new sub in the clsTree class:

moTree.DblClick Me


In the mcTree class you would have to add (at the top):
Event DblClick(cNode As clsNode)     'Node dblclick event


Also in clsTree, add:

Friend Sub DblClick(cNode As clsNode)
    RaiseEvent DblClick(cNode)
End Sub


Finally, in your userform you should now have a dblClick event available to the variable you used to hold the instance of the clsTree. E.g.:

Private Sub mcTree_DblClick(cNode As clsNode)

End Sub

 


Comment by: Andy Barber (9/9/2014 1:03:11 PM)

the 'How Do I' section for the MS Access version of this states we need the MS Forms 2.0 library, however this is not installed on some of the machines I need to run this code on and Microsopft states the the FM20.dll file is not a re-distributable file. Any ideas how I can get round this. your tool is excelent and I would really like to use it.

Please reply to attached email

 


Comment by: Yves (9/9/2014 3:32:58 PM)

Hi Jan,
Using your treeview to prototype a development in access...
Do you see a way to use images from files instead of having to glue them in form control?

 


Comment by: Jan Karel Pieterse (9/9/2014 9:24:12 PM)

Hi Yves,

You should be able to load an image from a file, but you would have to do the plumbing yourself:

Me.Image1.Picture = LoadPicture(strPath & "DeerPicture.jpg")

 


Comment by: Jan Karel Pieterse (9/9/2014 9:25:21 PM)

Hi Lex,

Odd, any Office installation should have the Forms dll installed. If you insert a userform in e.g. Excel VBA, does the reference not turn up?

 


Comment by: Yves (9/10/2014 10:29:47 AM)

Hi Jan,
thanks for your reply.
Do you mean image should first be added to form control?
Assigning image in tree don't seems to be accepted...

I wrote
cRoot.ImageMain = "c:\xxx.bmp"

and got error 5 Invalid procedure call or argument...

 


Comment by: Jan Karel Pieterse (9/10/2014 11:16:40 AM)

Hi Yves,

I mean that the control currently does not support adding images from file, but that you can add that support yourself :-)

 


Comment by: Yves (9/10/2014 11:38:37 AM)

OK, will do if/when I have time to do :-)

Your tree code is already big help for me and I understand he can not do everything!

 


Comment by: Alex (9/24/2014 1:45:16 PM)

Hi Jan,

I just discovered your Treeview, and it's really great. I'm playing around with it, to find out how it works, and have just one question :
I can't figure out how to display treelines in the treeview. Whether I check the 'Show lines' box in the demo application or not, no line is displayed between nodes...

Using build 025 under Access 2010 64 bits.

Any idea where this can come from ?

 


Comment by: Jan Karel Pieterse (9/24/2014 1:58:26 PM)

Hi Alex,

If you expect to see horizontal lines between the nodes, then you are right, those are not in the treview (nor are they in the original AFAIK). The LInes checkbox controls whether or not the tree connection lines display.

 


Comment by: Alex (9/24/2014 3:27:08 PM)

I was talking about the lines that links one node to an other, like in the screenshots at the top of this page.

So, something like that :

- Root
|
+-- Node
|
+-- Node


I get more something like this :

- Root

+ Node

+ Node

so no vertical lines, nor horizontal ones.

Here is a screenshot of the demo application :
http://content.wuala.com/contents/DenisS/PartagePublic/TreeView.png

 


Comment by: Jan Karel Pieterse (9/24/2014 3:54:56 PM)

Hi Alex,

Odd, they should show. They do on my system. What Excel version and Windows version are you on?

 


Comment by: Alex (9/24/2014 4:10:48 PM)

I'm working on Windows 7 64 bits, Access 2010 64 bits.

But it works fine on Windows XP 32 bits with Access 2010 32 bits.

Oh, by the way, there are two small bugs (at least under Access) :
Application.Version is as String value ("14.0"), so Application.Version >= 12#
throws an error in frmFelp.Form_Load and in the AutoExec macro.

 


Comment by: Jan Karel Pieterse (9/24/2014 4:51:12 PM)

Thanks for letting me know about the bug.

The tree shows up just fine on my system, I'll ask Ben to take a look.

 


Comment by: Dale Fye (9/28/2014 3:14:53 PM)

Peter,

In your response at:Peter Thornton (8/20/2014 12:14:14 PM)

you mentioned "right click context menus". How would you implement these? I've got my menus built, use them all the time with the ActiveX treeview control, but now that I need something that works in 64 bit Office. Unfortunately, I have not figured out how to test for the MouseUp event and detect whether the mouse button that was clicked is the left or right button.

 


Comment by: Peter Thornton (9/29/2014 1:10:23 PM)

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.

 


Comment by: WOJTEK (9/30/2014 5:30:13 PM)

It's very usefull tool for programmer. I must admit it is the best solution found by myself in this area.
I'd like to use the solution in my job. But my question is:
what do you mean by statement "The code may not be republished without prior written consent of the authors."?


 


Comment by: Jan Karel Pieterse (9/30/2014 7:38:11 PM)

It just means that if you want to make the code publicly available by writing about it (for instance an article on your blog) and including code from it, you need our permission.
You can use the tool in your projects without having to ask permission. But if you add useful things to it, we ask you to send them to us.

 


Comment by: Andy Barber (10/5/2014 11:42:42 AM)

Has anyone used drag and drop with the tree view to add/Move nodes? If so can you point me in the right direction to implement in my system.
I have a list of entries which I wish to drag to a node in the tree and add the items as children of that node

Many thanks

Andy

 


Comment by: Alex (10/8/2014 9:47:09 AM)

Hi Jan,

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 ?

 


Comment by: Jan Karel Pieterse (10/8/2014 10:23:12 AM)

Hi Alex,

Thanks for bringing that to our attention!

 


Comment by: Peter Thornton (10/9/2014 1:35:45 PM)

@ Andy Barker
We've held back from posting a generic Drag & Drop example as it's best adapted to individual projects and can be a bit complicated, we could spend a long time fielding questions! However we can probably put D&D together for your project privately but it would depend on what and where your "list of entries" is that you want to drag from.

@ Alex
My thanks too for flagging your line colour issue. I think normally a lighter colour than black is more appropriate for lines, but clearly the scrollbar colour is not good if almost the same as the background colour in some user's schemes.

Can you see another system colour in your scheme that would be suitable and also likely work well in more typical setups. Feel free to email me privately.

 


Comment by: Alex (10/9/2014 4:52:14 PM)

Hi Peter,

Well, I'm afraid there is no safe choice apart from vbWindowText, assuming that nobody will set the same color for text and background...

If I choose another system color based on my color scheme, it will probably fail with other schemes...

The only workaround I could see would be to check the scrollbar color against the windows background color, and, if they are too close, then apply another color. And choosing this 'other' color could also be tricky...

Or, possibly, falling back to a vbWindowText dotted line when scrollbar color is too close to window background (if the dotted line attribute can be set for a label) ?

 


Comment by: Andy Barber (10/10/2014 4:00:30 PM)

Thanks Peter,

I'm using the Treeview in MSAccess.

On the left of my screen I have the tree view and on the right I have a list box which allows multiple selections. I need to be able to drag the information in the list box (will be the key and caption info) and drop it into a node on the tree.

Many thanks for you assistance with this and I hope the above is enough info for you.


Andy

 


Comment by: Peter Thornton (10/10/2014 4:19:19 PM)

Hi Alex,
No we can't do dotted lines. But we can compare the normal default colour for lines to the background, and if fairly similar change to a new default of black (or white if a dark background), all automatically. In addition include a new exposed "LineColor" property to override.

We'll (probably) include this in the next version (026), not sure when that'll be so in the meantime contact me if anyone needs this.

 


Comment by: Alex (10/13/2014 1:19:11 PM)

Hi Peter,

Yes, I think it's the only way to do it. And the override property will help to get rid of the most difficult cases.

I also saw that you have a fallback to RGB(180, 180, 180) for the checkboxes ForeColor in some cases. This could lead to the same problem if the background nears this color.

If you need some more testing or information, just drop me a mail.

Thanks !

 


Comment by: Peter Thornotn (10/13/2014 4:49:26 PM)

@Andy,
Although the Access Listbox doesn't support D&D it does seem to respond to the built-in MSForms approach and in turn the DragOver and Drop events on individual nodes can be trapped.

Alternatively, can simulate D&D which would need to include the equivalent a "HitTest" (eg to toggle cursor and/or highlight when over a suitable node and then to id the drop target). However with this approach MultiSelect=Extended doesn't look viable but "Simple" seems OK.

@Alex,
Well spotted! The RGB(180, 180, 180) forecolor would only affect "textbox" style checkboxes (on a similar grey background), which we kept for compatibility before figuring the icons not only look better but counter intuitively don't use more resources. I suspect less important to do as I described for Lines, not sure, maybe we'll wait for complaints before deciding <g>

 


Comment by: Alex (10/13/2014 7:21:17 PM)

Yes, that sounds quite reasonnable.

No reason to fix it if nobody finds it broken. :)

 


Comment by: Barry C (10/17/2014 12:30:46 AM)

First of all - Amazing piece of work!

I'm in the middle of working this into a project and I'm loving it.

But... I'm having the same issue as Davide (a couple of months back).

Using Access (tried it in versions 2k, 2k3 and 2010) I can not add a new image or change an existing image in the image frame.

If I select an existing image or add a new one, the "Picture" property in the property sheet is read only.
Other properties (name, size etc) are fine.

Works fine in Excel (all versions), but not in Access.
Any ideas?

In the meantime, I'll try a work around - do that work in Excel, then export it to Access.

Thanks
BC

 


Comment by: Peter Thornton (10/19/2014 6:22:47 PM)

Hi Barry,
Strange, I can't load a new picture to an image control in MSForm frame container either, thought I could!

OK, rather than "export" you can add the image controls to a form in Excel (or Word) and simply copy the control with it's picture to the Frame container in Access (can copy/paste multiple image controls at the same time). The Frame in the demo was added as an ActiveX "Microsoft Forms 2.0 Frame".

Note you can also pass a Collection of picture 'handles' as an alternative to passing the Frame control if more convenient to store your images elsewhere.

You say you've tried it in Access 2000 - interesting, does the demo actually work for you? In light testing although in 2k it all compiles OK trying to load in resulted in a bunch of "Object of class does not support the set of events" errors, no idea why!

Untested in 2002. 2003 should be fine but it seems only if compiled in 2003 (may get similar error in 2003 if compiled in 2007 or later), again no idea why. (For anyone else reading this concerns Access only.)

 


Comment by: Dale Fye (10/21/2014 2:09:37 PM)

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.

 


Comment by: Dale Fye (10/21/2014 2:51:30 PM)

Is there a way to implement the MouseUp event in the main form? I notice in the frmDemo that there are Click, KeyDown, and NodeCheck events listed, but don't see the MouseUp event.

I've added code to the mctlControl_MouseUp subroutine in clsNode, but would like to leave that code unchanged and simply be able to refer to the mcTree_MouseUp event in my main forms code module.

Is there something special that I need to do to make that event visible in the main form?

 


Comment by: Jan Karel Pieterse (10/21/2014 3:30:58 PM)

Hi Dale,

The easiest way is to call the mcTree.NodeClick method with the Node's Expander control as the first argument. Like so:

        nd.Expanded = Not nd.Expanded
        mcTree.NodeClick nd.Expander, nd

 


Comment by: Dale Fye (10/21/2014 4:10:34 PM)

That seems to have done it. Thanks.

 


Comment by: Dale Fye (10/22/2014 4:05:38 PM)

Guys,

I'm trying to add some images to the image frImages in Access 2007. I was able to drill down to the image control on the form, and then select properties, but am unable to change the actual image.

I noticed a thread above that you cannot currently edit the images in Access, but I'm the first to admit that I'm an idiot when it comes to working with Excel UserForms.

I added a user form to an Excel file, added an image control to it, but cannot figure out how to actually assign an image (bmp, png, ico) to that image control.

 


Comment by: Jan Karel Pieterse (10/23/2014 7:07:56 AM)

Hi Dale,

There are various ways to add an image to an image control on a VBA Userform:

- At runtime you can use the LoadPicture method
- At design time:
Copy the picture and paste it into the picture property of the image control
Click the three-dotted button next to the image property and select an image file

 


Comment by: Isabella Tsoglin (10/27/2014 2:25:49 PM)

Thanks for your amazing work.
Works good with Access 2010.
I'll try to add "dblclick" event, not yet done.

 


Comment by: Jan Karel Pieterse (10/27/2014 2:46:29 PM)

Hi Isabella,

You're welcome!

 


Comment by: Jon (11/1/2014 4:33:55 PM)

Regarding the comments from Barry C (10/17/2014) and earlier from Davide (7/18/2014) I too, just now, am not able to modify the Picture property in an existing Image control, or set the Picture property of a new Image control.
In fact I added 4 new Image controls and was able to set the Picture property just 3 days ago.
My steps: Open the subTreeView form, display the Toolbox and Properties sheet for the MSForms Frame, add a Image control to the Frame and attempt to set the Picture property. The Picture property does not get set. Nor can I change the Picture property of the existing Images. I can however still do this successfully in a copy of your demo database that I used to learn with. To my knowledge nothing has changed on my computer (Windows updates, etc), but I wonder if it is some sort of Access/Current database security issue.

BTW this is an excellent piece of work and I hope to use it more.

Thanks, Jon

 


Comment by: Jon (11/3/2014 4:39:54 PM)

To followup my last comment:
I actually remember, now, that I added the images to the frame in a copy of the Access demo database and imported it to my application. I have tried to add/edit image pictures to 2 different Access 2007 databases on 2 different machines. There must be some setting in the demo database that makes this possible.

It is possible, as mentioned in a different comment by Dale to change the image picture property in code with LoadPicture. This might actually be a better way because it adds some flexibility when initializing your app.

 


Comment by: Francisco Sciorilli Jr (11/6/2014 8:44:41 PM)

Great work !

 


Comment by: Nicole (11/7/2014 5:00:36 PM)

I have the Access build 25 treeview control and everything has been working very smoothly. For our mixed 32 and 64 bit environment this tool is perfect!

The information being loaded into the tree view is at a level that a search feature would be helpful to the end user. With that in mind I would like to search and set the active node based on the unique key value or caption assigned when loading. I can set the active node based on the item number but can't figure out how to set the active node based on the unique key. Based on all the features in this tool it is probably a simple task but any feedback would be appreciated. Thanks!

 


Comment by: Nicole (11/7/2014 5:18:42 PM)

I have the Access build 25 treeview control and everything has been working very smoothly. For our mixed 32 and 64 bit environment this tool is perfect!

The information being loaded into the tree view is at a level that a search feature would be helpful to the end user. With that in mind I would like to search and set the active node based on the unique key value or caption assigned when loading. I can set the active node based on the item number but can't figure out how to set the active node based on the unique key. Based on all the features in this tool it is probably a simple task but any feedback would be appreciated. Thanks!

 


Comment by: Michael (11/7/2014 8:15:01 PM)

I have added this control to my workbook, but I am being prompted for the VBA project password when Excel quits. I checked Microsoft support, and they say (Q280454) the cause is I am "running a macro that passes a reference for a workbook containing a password-protected VBA project to an ActiveX dynamic-link library (DLL), and the ActiveX DLL does not properly release the reference to the workbook that contains the password-protected VBA project."

Has anyone else experienced this? Can you help me fix it? I'm pretty sure it has something to do with how I coded my function to use these classes.

Any help is appreciated.

 


Comment by: Kurt Bergman (11/7/2014 9:37:15 PM)

Re: Collapsing all nodes...

In the populate Tree Procedure I set cnode1.Expanded to false.

This add-in rocks!!

 


Comment by: Peter Thornton (11/8/2014 12:15:28 PM)

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.

 


Comment by: Jan Karel Pieterse (11/10/2014 11:32:05 AM)

Hi Michael,

This is a problem that may be caused by an add-in loaded on your system. Adobe has a reputation causing this for example.

 


Comment by: Nikola Petanjko (11/10/2014 3:44:12 PM)

Hi,
Thanks ! thanks ! Thanks for your wonderfull treview

I need to add 3 features
- collapse all from root (or from selected node)
- expand all from root (or from selected node)

- collapse all + expand up to deep level N
keeping collapse since any branch from level N

I will start them now, if you already have such features, thanks for sharing.

Regards

Nikola Petanjko
Switzerland


 


Comment by: Jan Karel Pieterse (11/10/2014 4:04:30 PM)

Hi Nikolai,

Yes, such functionality is already present. Check out the ExpandToLevel method of the tree class.

 


Comment by: Lance Duvenhage (11/14/2014 11:31:53 AM)

Hi Nicole, not sure if you found a solution for your problem. I used the code to read from different tables with one to many relationships. And can do the surge function you need, use the unique ID to run a query based on the selected node. May be along way of doing things but it works very well. Can share if it needed. What I would like to be able to do is when you rename the a node, how can this be saved in the table? My second question will be almost the same, If I add a new node how can this be saved in the table. My last question will be, the tag function, how to use multi select in to run the query. (Have an idea but have not tried it) Any advice will be helpful.

 


Comment by: Ake (11/14/2014 11:14:39 PM)

Excellent job!

 


Comment by: Peter Thornton (11/17/2014 11:30:47 AM)

Hi Lance,
1. By "rename a node" I assume you mean if the user manually edits a node with double-click or F2. Look at the AfterLabelEdit Event, slso see the example in the demo if you try and edit the caption to include "123" or "456".

2. You didn't explain if adding a new node is instigated by the user or forced by your code. Either way you should be able to know the details of any new node and update your table, either in real time or perhaps after user presses an update button.

3. "MultiSelect" is not implemented. Suggest use checkboxes, depending on your scenario the TriState option might be useful (see the demo).

 


Comment by: Aaron (12/10/2014 12:42:32 AM)

Was this control written in VB.Net? I have created a ActiveX Control in C# and successfully made the dll, which I am able to bring into a VB form, but would like to use it directly on a excel worksheet. When I select the ActiveX control and try putting it on the worksheet is gives the error message "cannot insert object". Do you know how to get ActiveX objects to work on worksheets?

 


Comment by: Jan Karel Pieterse (12/10/2014 9:24:38 AM)

Hi Aaron,

As the page title says: it is entirely done in VBA.
In fact, it may even be possible to update the code so it works on a worksheet as well.

 


Comment by: GotQuestion (1/6/2015 3:52:54 PM)

After looking through the code, I notice you are not storing node references (parent/child) as pointers. Doesn't storing them as objects create the potential for memory leaks?

(See Rob Bruce's comment on this page: http://dailydoseofexcel.com/archives/2007/12/28/terminating-dependent-classes/ )

 


Comment by: Jan Karel Pieterse (1/6/2015 4:10:49 PM)

Hi GotQuestion,

We spent quite some time figuring out how to oproperly terminate all objects and in fact (in the demo) even have some checking code in place that keeps score of all objects (as long as you are in debug mode). The tool currently does the right stuff: all objects are properly terminated at the right moment as far as we know.

 


Comment by: Paul Sondervan (1/21/2015 8:30:31 PM)

I have a dropdown menu with recent choices.
When the user selects one of the recent items, I would like to set the corresponding menu choice in the Treeview.
Is it possible to set this menu choice by using vba?

 


Comment by: Jan Karel Pieterse (1/22/2015 7:01:46 AM)

Hi Paul,

You can set the active node by using code similar to:

Set mcTree.ActiveNode = MyNode


MyNode is an object variable pointing to any node in the tree. This object variable you would have filled from your dropdown somehow.

 


Comment by: Hesham (1/22/2015 12:25:16 PM)

I added the subform to mail form the displays detail infor on students, but How to use the tree control to filter the main form?

 


Comment by: Lucio (2/23/2015 5:20:03 PM)

Ciao
Ho visto il Treeview e mi interessa nella versione full..
quanto costa?

 


Comment by: Jan Karel Pieterse (2/23/2015 5:35:44 PM)

Hi Lucio,

Can you please explain a little bit more? The download already contains the full version?

NB: I'm afraid I do not speak Italian :-)

 


Comment by: Jerzy Orysiak (2/24/2015 7:52:12 PM)

Hi,
Great piece of code, congratulations. It is a pity "drag and drop" functionality is not included (anyway I'll try to add this). I'd be grateful for hints how to handle context menu (declaring in standard module public variable containing ObjPtr? to clsTreeview object?) What is a simplest approach?
Also, I've spotted weird behaviour of checkboxes (Build number 25). Is it the last version?
1) On preloaded nodes checking node causes checking this node and all subtree "hanging" on that node and "Third state" for all direct ancestors (not sure why) and it is OK.
2) After adding some nodes, checks made on new ones goes crazy! All added ancestors of checked node are checked as well which I suppouse is not correct.
3) "checks" without icons appears as "a" letter.

The second question is: is there any property like "that node cannot be used as a parent"?

And last at this moment. I havn't found public types for export/import data for populating treeview. One for treeview object (1 data record only) and array of variables containg available nodes data (key, parent?, text, tag, font , Path?, etc). More of that it also tells which properties might be manipulated (background colors for instance should be used with care since they show for instance "selected node" , "cut subtrees") WITHOUT digging deeply inside your code.
brgrds

 


Comment by: Peter Thornton (2/25/2015 1:11:37 PM)

Hi Jerzy, lots of points!
Drag & Drop is not builtin, we had to stop somewhere but it’s possible to implement. There are two approaches: you can work with ‘BeforeDragOver’ and ‘BeforeDropOrPaste’ events or “simulate” D&D which is more complicated but can use custom icons. The hard work is already included in the treeview, your D&D will end up calling the builtin Copy or Move methods.

A right click context menu is more straightforward. Trap node’s MouseDown (or Up) with Button=2 and “Raise” it as an Event similar to the other events. Validate as required then create a normal CommandBars popup menu (don’t forget to destroy it). Buttons in the popup can 'OnAction' ordinary macros in a normal module, you may need to temporarily pass node objects to the normal module before showing the popup but be sure to destroy any additional treeview/node references.

I don’t follow what you are saying in 1) and 2), but if it’s about TriState checkboxes some changes to the logic when nodes are added and events for ‘indirectly’ changed checkboxes will be included in the next version (026).
Re 3) If you are not using checkbox icons the checkbox is simulated with Label controls to look like a small square, and with the Marlett font the “a” should appear as a tick. Is Marlett is installed in your system?

To retrieve data see the code in the demo behind the button “Dump Data”, cmdGetData_Click, GetData1 and GetData2. These are only examples, adapt to selectively retrieve the data you want.

Indeed colours are temporarily changed with Selection, Move & Copy, but the default or your applied colours should be automatically restored, ie after changing the selection or done with Move or Copy. Play with the 'Icons' demo and in particular the alternate shaded nodes.

 


Comment by: Jerzy Orysiak (2/26/2015 12:57:12 AM)

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)
.Caption = IIf(mlChecked, "a", ""): Debug.Print "%%" & mctlCheckBox.Name, mctlCheckBox.Font, mctlCheckBox.Caption

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

 


Comment by: Peter Thornton (2/26/2015 12:20:23 PM)

I replicate your "a" in Excel 2000. Strange, I'm sure we tested in 2000/2003 but it seems when the Label is added at runtime, although Marlett is correctly applied the default font continues to display, hence "a" not a tick.

Normally this VBA Treeview should work in all Excel & Word versions since 2000 and Mac 2011 (though Access only since 2003). However for your 2003 I suggest you use checkbox icons, see the demo code and documentation. There are two sets of checkbox images bundled in the demo or you can find your own.

Yes there is a difference in the way the checkboxes work in the two demos, with TriState and not with TriState.

 


Comment by: Jerzy Orysiak (2/26/2015 6:12:50 PM)

I found the reason for "a" tick.
Setting font to "Marlett" is not enough.
lines 1626, 2053(about)
.Font.Name = "Marlett"

should be followed by:
.Font.Charset = 2

(at least it works on my system

 


Comment by: Peter Thornton (2/27/2015 11:04:15 AM)

Thanks Jerzy. Font.Charset = SYMBOL_CHARSET or 2 also fixes the Mareltt tick for me in old Office versions. Not sure why it's not necessary in 2007 and newer, at least not for me and I assume not for most users or it would have been flagged here before!

 


Comment by: Thomas magdahl (3/6/2015 7:46:47 PM)

Hi guys,

Nice work and I am definitely using it.

I am checking the AfterLabelEdit subrutine, simply overriding the edited text. The routine is at the end of this text.
My problem is that on the node "Hello" is diplayed but the node Caption keeps the value that was on NewString before the override (modified version of what was before editing). In your demo code 456 is replaced with 789 and both the visual text and the Caption are the same.

Any clue of what I am doing wrong?

Regards

Thomas Magdahl


Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode)

    NewString = "Hello"
    Cancel = False
End Sub

 


Comment by: Thomas Magdahl (3/6/2015 10:06:50 PM)

Hi,

As a matter of fact the untouched demo is now behaving in my machine with the same problem.
In my previous posting I siad the problem was only on my code but not on the demo and now I realize they both have this same apparent inconsitency. Not sure if it is an erratic behaviour or I am going nuts!
When you edit a Node in the demo and add 456 at the end, it gets replaced by 789 but the text on the node in the tree window does not match with the text displayed in the labInfo label. The problem will not disapear with a Refresh or by selecting different nodes and returning to the appatently "offending" one.
Not sure if this is of any help!

Thomas

 


Comment by: Peter Thornton (3/7/2015 1:09:02 PM)

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

 


Comment by: Thomas Magdahl (3/9/2015 1:38:38 PM)

Hi Peter,

Sorry that I comming back with this same issue, but I think there are two posibillities:
1) I still do not get something that might be abvious
2) Your code behaves in my machine in a different way

From your explanation, after I edit a label and the AfterLabelEdit routine makes a change to the string (for example changes 456 to 789) and I keep on traversing and editing other labels/nodes, when I return or select the label/node whose string was chenged by AfterLabelEdit, I should see the same string in the display and in the Caption property. That is not happening in my machine.

A more detailed explanation follows.

Regards
Thomas

 


Comment by: Thomas Magdahl (3/9/2015 1:39:21 PM)

Hi Peter,
I just unpacked the file again from the original downloaded ZIP file, enabeled macros in EXCEL and run both demos.
I double click on the first non root node 1.A and add at the end 456 and press return. The text visualized in the tree node, displayed in the frame (just after pressing return) effectively displays 1.A789, and labInfo displays 1.A456. Based on your explanation this is explained because the labInfo label is updated with the Click routine before 456 was changed to 789. So I understand that if I move to another node and regardles if I did an edit or not in that other node (for example 2.B), when I return to 1.A789, labInfo will display 1.A789 too. That is not happening on my machine. I still get 1.A456. If after moving through a bunch of the remaining nodes I stop the VBA code and inspect mcTree, and look into mcTree->Nodes->Item2->Caption, 1.A456 is stored (the screen is still showing 1.A789 as expected). If I look at mcTree->Nodes->Item2->Control->_Value, 1.A789 is stored. Not sure if _Value is of any relevance, but at some place in the structure 1.A789 is stored and displayed correctly on screen, but the Caption property is still showing 1.A456 which in my oppinion not correct, because AfterLabelEdit made the change.

I made the decription as thorough as possible so you might better diagnose if this is case 1) or 2).

I hope I am not making a fool of myself :-)

Regards

Thomas

 


Comment by: Peter Thornton (3/9/2015 3:32:06 PM)

Hi Thomas,
You are right! I don't know how that crept it, it's not in the version I'm working with or most others. Please edit clsNode as indicated


Private Sub moEditBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

' code

    bCancel = moTree.RaiseAfterLabelEdit(Me, sNewText)
    If Not bCancel Then
        ' Me.Caption = moEditBox.Value ' << remove this
        ' Control.Caption = sNewText ' << remove this
        Me.Caption = sNewText ' << add this


This will be fixed in the next build 026 in the next week or two.

Thanks!

 


Comment by: Thomas Magdahl (3/9/2015 4:59:57 PM)

Hi Peter,

Worked fine!
Thanks for your help!

Thomas

 


Comment by: Thomas Magdahl (3/11/2015 5:21:36 PM)

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


Any suggestion on how to solve this?

Regards

Thomas

 


Comment by: Peter Thornton (3/11/2015 9:00:15 PM)

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.

 


Comment by: Frank Russell (3/19/2015 5:18:21 PM)

I think that what you have done is great for the MS Access user/developer. There appears to be only one thing missing that I would greatly appreciate if you could incorporate and that is the ability to Drag and Drop between nodes and another treeview. I have tried, unsuccessfully to develop my own drag and drop routines.
Thanks again for a great product.
Frank

 


Comment by: Jan Karel Pieterse (3/19/2015 8:15:11 PM)

Hi Frank,

Thanks! There is some plumbing in there already to handle drag and drop if I recall correctly.

Peter Thornton has been investing quite some time in that feature, but I'm not entirely sure of the current status.

Watch this place, as Peter does visit here now and then to post comments too.

 


Comment by: tino (3/20/2015 3:39:47 AM)

I can't open any of the files with Microsoft Office 2003.

Excel 2003, gives... invalid file format

Word 2003.. nothing happens when you click the button. I think it gets converted to a doc with no macros.

The only one I could open is the Access file, but it gives a code error when the demo starts to run upon opening the file.
I can see the code though in here.

 


Comment by: Jan Karel Pieterse (3/20/2015 1:42:16 PM)

Hi Tino,

Try reinstalling Office.

 


Comment by: Peter Thornton (3/23/2015 11:07:38 AM)

@Frank,
As Jan Karel mentioned quite a lot of work has been done with Drag & Drop. It's not included in the demo as some of the code is external to the intrinsic treeview and how best to adapt depends of the individual project. We'd be happy to help implement D&D privately if that's an option for you. Alternatively, to point you in the right direction, there are two approaches: using the built-in D&D MSForms events or "simulating", as Jan Karel also said some of the "plumbing" is included.

@tino,
I've just double checked and demo v026 works fine for me in Excel 2003 and 2000, albeit after saving the file as an .xls. There can be problems with the Access 2003 if the file was not compiled in 2003, eg if used in 2007+ then attempted in 2003.
I wonder if the Dec-2014 Windows update that affects MSForms controls has hit you. With Office apps closed try deleting or renaming all MSForms.exd files in your system, restart windows.

 


Comment by: Jerzy Orysiak (4/22/2015 4:46:34 PM)

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)

sProc = ActiveSheet.Names("ProcsList").RefersToRange(idx, 1)

It seems vba6 can't use names defined by formula.
I suggest sollution (works probably also with vba7)

sProc = Evaluate(Mid(ActiveSheet.Names("ProcsList").RefersTo, 2))(idx, 1)

Jerzy

 


Comment by: Jerzy Orysiak (4/23/2015 3:22:29 AM)

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)
sProc = ActiveSheet.Names("ProcsList").RefersToRange(idx, 1)

to:
sProc = Evaluate( Mid(ActiveSheet.Names("ProcsList").RefersTo, 2))(idx, 1)

and respectively (line 90)
from
Set rng = ws.Names(sName).RefersToRange

to
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

 


Comment by: Peter Thornton (4/25/2015 12:33:03 PM)

Hi Jerzy,
Set rng = .Names.RefersToRange(name) should work in all versions since Excel 97, I've just double checked the demo file in 2000 and 2003, all working fine. I'm not sure why you need the Evaluate workaround in your system, have you tried the example in VBA help for RefersToRange. If there really does appear to be an issue contact me privately.

D&D can be configured to work across multiple treeviews and you can use the built-in Move or Copy methods, if indeed the objective is to Cut or Copy a node and its children. The intrinsic class objects are the same in both treeviews so "incompatibility" doesn't apply. At least not as far as the treeview is concerned though of course most likely you'd need appropriate validation to ensure a node can only be dropped on a suitable target. The logic will be specific to your project. Without knowing quite a lot about your project it's hard to give code examples, there are different approaches and unfortunately not straightforward!

For a right click context you can use the Office Commandbars popup which does not require any APIs, plenty of examples out there. You can also create a Windows popup which requires eight APIs including CreatePopupMenu, look at CommandBars!

 


Comment by: Jerzy Orysiak (4/26/2015 5:51:31 PM)

Hi Peter,
1) Set rng = .Names.RefersToRange(name)works fine but only when name is defined as range directly, i.e. not like in this case formula "=OFFSET(...)". This is not a problem for me and I think it's not worth to examine that further.
2) D&D Thanks for tips. The main reason I need multitreeview D&D is to create "commandbar manager" which consists of 3 treeviews:
i) target treeview - for keeping current application user commandbars, references and basic call function information (short help)
ii) source treeview - excel "Worksheet Menu Bar" commandbar
iii) source/target my "universal" fully tested functions (placed in commandbars) from other excel applications
3) Right click context menu. It's not a problem to create popup commandbar. Problem is: how to avoid public functions/variables.

Regards
Jerzy

 


Comment by: Peter Thornton (4/27/2015 7:33:14 PM)

1) RefersToRange should also work in all versions since '97 with a Dynamic name.
2) Sounds like an interesting project!
3) You can avoid public functions in normal modules by prefixing the functions and variables with Private. There's also Option Private Module.
Alternatively put everything relating to the context menu in a Class, including object variables and action code. Instead of OnAction macros trap respective button click events with WithEvents.

 


Comment by: Jerzy Orysiak (4/29/2015 1:32:34 AM)

Hi,
1) I didn't express myself correctly what I meant "public". Commanbarcontrol .OnAction requires PUBLIC function/sub placed in standard module. I want to avoid this. I want to take all actions in class module either in userform or another "wrapping" withevents class.
What I do NOT KNOW is how to trap popup menu choice (item selected) whithin class ...

2) I want to keep tree in a recordset (sheet) (RecID, ParentID, field3, field2...) of simple data type (images represented by keys).
I need to update image frame each session with userform (pictures can be added/removed. It requires VB project Designer action (I don't know any other solution). Once I execute designer's images updating, I can also save current treevieew settings like "showlines", "Show expanders" in for instance in userform "tag". Is this acceptable approach?

 


Comment by: Peter Thornton (4/29/2015 11:38:30 AM)

1) The OnAction macro in a normal module can be Private. Or, in a class module when adding the buttons assign then to 'WithEvents' CommandBarButton object variables, which will expose trappable Click events.

2) Icon images do not need to be hard coded, and no need to use the Designer object. Although the simple way is to pass the frame of images, you can also create and pass a 'collection' of images. The collection could include a combination of 'picture' handles from existing image controls, from file with LoadPicture, or created in various other ways, eg with APIs.
Set mcTree.Images = colIcons
See "Collection Method" and "Frame Method" in the demo form.

New in v026 are Treeview.ImageAdd and Node.ImageUpate methods. You can now also add a new images to the treeview's internal collection and update or change node icons while the treeview is running. The new images could be sourced from anywhere with a variety of methods.

 


Comment by: Jerzy Orysiak (4/30/2015 7:01:07 PM)

1) Thanks as lot!. It's exactly what I want! (although I've substituted WithEvents commandbarbutton declaration for each commandbarbutton in shortcut menu with a collection of objects - class designed to trap commandbarbutton click event and returning to the parent userform the "clicked" commandbarbutton caption)
2) That solution is fine until I have to transfer workbook to another PC...
To avoid transfering icon files along with workbook I need one way or another to "save" them in a file. The only way I know is to keep them "hard way" is: either using Designer or keeping them in Shapes collection.
Also, I have developed fine "Iconpicker" from office icons and spent quite a lot of time to create db table with fields(faceid, cathegory, tag) for few hundreds of icons out of more then 7000. Sooner or later I will have to migrate to the newer windows and office. I don't know whether next versions of office keep icons faceid the same
way and I am afraid of loosing everything.

 


Comment by: Peter Thornton (5/4/2015 9:29:54 AM)

You can maintain and distribute your icon images any way that's convenient. That might be in a say a separate folder of image files and get with LoadPicture, probably better with a large number.
With a smaller number you could store them in a workbook in ActiveX Image controls on a sheet, or in say a second userform. You can also store as hex "blobs" in cells (text) though it requires a fair amount of API stuff to convert them. No point to store as Shapes, not simple to convert to StdPicture objects, use aX images.

 


Comment by: Jerzy Orysiak (5/6/2015 2:59:47 PM)

Hi,
1) I've tried to use standalone Move method (for changing order of nodes having the same parent in this case) with Node.key as a vAfter /vBefore parameter. It seems it doesn't work that way. It does work when vBefore /vAfter is set to childnodes collection index of destination parent node, available through not included in documentation ChildIndex function.
Am I missing something?
Move sub can be easily modified but I don't want to make any changes in your code.

2) There is no "public" way to change order of root nodes in multiroot treeview. There is no "public" way to add new root before/after (if I have to do it: rewriting roonodes collection with desired order is a solution?)

3) There is no "public" way to cut node and paste as a new root - child of "RootHolder" (?)

 


Comment by: Peter Thornton (5/7/2015 1:22:11 PM)

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!

 


Comment by: Juan (5/7/2015 4:43:30 PM)

Hi!
Your class is great! Thanks for share it!!
How can i get the caption of each node?
i've tried using
for i=1 to mcTree.nodes.count
var=mcTree.nodes.item(i)
next i

but it doesn't works......

Thanks a lot!!!!

 


Comment by: Jan Karel Pieterse (5/7/2015 5:47:21 PM)

Hi Juan,

Something like:

Dim cNode as clsNode
For Each cNode in mcTree.Nodes
    var = cNode.Caption
Next

 


Comment by: jerz (5/8/2015 12:51:38 AM)

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:
Call mcTree.NodeAdd(mcTree.ActiveNode, tvprevious, skey, skey)

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

 


Comment by: Peter Thornton (5/9/2015 12:20:50 PM)

Hi Jerzy,
I don't follow all of what you're doing there but it sounds like you've got it all working.

Indeed the activenode's highlight formats change as the treecontrol gains or looses focus if you included the frame's enter & exit events in the main form (see the demo) to make it consistent with Window's behaviour, it's a feature! However you can easily adapt or override the these highlight changes.

 


Comment by: Jerzy Orysiak (5/12/2015 1:47:02 PM)

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

 


Comment by: Peter Thornton (5/13/2015 1:06:47 PM)

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!

 


Comment by: Jerzy Orysiak (5/14/2015 1:15:55 AM)

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

 


Comment by: Peter Thornton (5/14/2015 8:54:01 PM)

Hi Jerzy,
I don't think we ever considered .NodeAdd for adding root nodes. Although it works with the change I mentioned for subsequent root nodes, I think at least for the first node it can be added with .AddRoot, and then never necessary to check if mcolNodes Is Nothing with all other nodes, but no problem to do that.

It looks like you are giving the treeview a very thorough work out! Indeed please send your remarks when done.

 


Comment by: Chris (5/15/2015 1:39:13 PM)

I'm wondering if you have a vba listview alternative that work both on 32 bit and 64 bit office?

 


Comment by: Edgar (6/1/2015 11:36:09 AM)

Hi JKP,
Will this treeview also work in Access 2013 32/64?
Thanks, Edgar

 


Comment by: Jan Karel Pieterse (6/1/2015 12:34:26 PM)

Hi Edgar,

Yes it should work on Access 2013 as well.
If you could download the Access sample and try it on Access 2013, that'd be great!

 


Comment by: Edgar (6/1/2015 1:28:55 PM)

Hi JKP,

Tested on Access 2013 64 bit... Works like a charm...
Many thanks!

Edgar

 


Comment by: Chris (6/2/2015 11:55:15 AM)

Steve wrote a comment back on (2/21/2014 3:49:20 AM) saying "If the user first clicks on a node on the Treeview in Form A, the fields on Form B become locked. More accurately, the fields on Form B can receive focus, but they cannot be updated." Peter Thornton came back on the (2/24/2014 3:32:00 PM) and replied.

Well, I'm experiencing a similar problem where the focus seems to be stuck on the Treeview. I can click on an unbound field on Form B (the cursor goes there) but its acting like the field is locked. Was there any further communication with Steve? Did he get his problem resolved?

 


Comment by: Eduardo (6/3/2015 8:05:41 PM)

Hi.
I'm really having a lot of trouble in populating the tree
Maybe you can make an entire instruction or tutorial:

I need to create this nods por example:

1. Item 1
2. Item 2
3. Item 3


Then I need to add the followings:

1.1. Item 11
1.2. Item 12
2.1. Item 21
3.1. Item 31

And then:

2.1.1 Item 211

.................................

So the tree must be:

1. Item 1
1.1. Item 11
1.2. Item 12
2. Item 2
2.1. Item 21
    2.1.1 Item 211
3. Item 3
3.1. Item 31

How can i locate a specific node (index) so i can add a new child into it?
Thanks

 


Comment by: Chris (6/3/2015 10:57:42 PM)

Just as a follow-up answer to my question about focus sticking on the Treeview and for anyone else that might experience this behaviour. I have resolved the problem by simply setting "Tab Stop = No" on the Treeview subform. Everything now works!

Interestingly, the same behaviour can be replicated with the demo project in Access 2000. Just place an unbound text control on frmDemo, press "Demo Treeview", click on any node on the Tree, click in the unbound text control and try and type anything – unbound text control acts if its locked (even though it's not). The same example running under Access 2013 with "Tab Stop = Yes or No" works fine. I haven't tested it under other Access versions so it could well be an Access 2000 special. I know Access 2000 - its still being used! Just thought I'd pass on my findings...

 


Comment by: Jan Karel Pieterse (6/4/2015 7:29:00 AM)

Hi Eduardo,

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

 


Comment by: Peter Thornton (6/4/2015 12:21:21 PM)

Hi Chris,
I couldn't replicate the problem in Access Steve and now you described. But I wonder if your "Tab Stop = No" workaround prevents key tab into the treeview? Ideally we'd want to prevent what you describe and allow tab in and out, and 'notify' the treeview so it can change the active highlight as I described (2/24/2014)

You say it works in Access 2000, we didn't know that! The earliest we thought was 2003, and only if compiled in 2003 but in a system without a later version. It's why we're slow to update the Access demo with the latest version without such a system to hand.

A belated response to your Q (5/15/2015) about do we have a listview, as the saying goes - watch this space!

 


Comment by: Chris (6/15/2015 7:29:24 AM)

Hi Peter,
Yes, that is the downside of setting “TAB Stop = No”, but I can live with that given the alternative is that every control on the form is locked! Apart from that little glitch it works perfectly fine under Access 2000 and Access 2000 runtime :)

Thanks for your response re: listview, say something was in the works... any chance of an idea of what a time frame could be? 2015, 2016? ;)

 


Comment by: Eric (6/15/2015 5:00:32 PM)

Hiya,

I've got an odd issue that crops up on form resize, specifically if the use minimizes the form and the form's detail height or width is updated. On detail size update the treeview will terminate itself. The other sub form doesn't appear to be affected, and if I disable the detail update the treeview doesn't terminate itself. So, the logical thing would be to disable the detail update on (max|min)imize, however that doesn't appear to be possible.

I thought this might be an issue with the treeview subform size dipping down to 0, but it never reaches that point. None of the form elements get below 300 twips. The treeview also doesn't terminate until the detail size update event.

The code:

Private Sub Form_Resize()

    ' Disable screen updating
    Application.Echo False
    ' Bypass errors
    On Error Resume Next
    
    With Me
        ' #### Some Stuff being sized ####

        ' Sub form height update
        .subDetails.Height = .Form.WindowHeight - 2595
        .subTreeView.Height = .subDetails.Height
        ' Detail size update
        .Detail.Height = .subDetails.Height + 150

        ' #### Other stuff being sized ####
    End With
    
    ' Reenabled errors
    On Error GoTo 0
    ' Enable screen updating
    Application.Echo True
    
End Sub


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.

 


Comment by: Peter Thornton (6/18/2015 10:04:40 AM)

@Chris,
Pleased if not surprised to hear it works in Access 2000! Could you clarify the tab issue you mentioned is only in 2000, or if not which versions?

The listview alternative is still coming soon, as it has been for a little while! If you drop me a line privately (see a module header) I'll keep you updated.

@Eric,
I couldn't replicate your resize problem in our demo. You say the "treeview terminates itself" - is that getting done 'nicely' because something's gone wrong? If not sure put a break in clsTreeview.TerminateTree and see what called it.

I take it your Form_Resize() is in the main form, wonder if that conflicts with the Sub ResizeUF() in the subTreeView, try putting a break in it and step through.

Another thing you could try: in Project Properties / Conditional Comp' Arg's
DebugMode = 1
In the VBE, tools/options/general - break on all errors
If there's any error in the class modules when you resize the code should break, step through and 'Resume' to the error line.
Be sure to remove or reset DebugMode = 0 before distributing.

Report back if not fixed.

 


Comment by: Chris (6/19/2015 7:20:08 AM)

Hi Peter,
Just confirming its only happening in Access 2000. It's a strange one but it can be replicated using your demo access project under Access 2000 easily. It would be great to know what the problem is but I can understand if you can't lay your hands on a copy off Access 2000 anymore!

I've also just emailed you privately regarding getting updates from you on the listview alternative when there is an update :)

Thanks for all your responses and indeed getting back to me – your great work is appreciated!!

 


Comment by: Andrew Glasgow (6/22/2015 4:43:32 PM)

How can I download the treeview control. There's no link associated with "Download". Do I have to donate first?

 


Comment by: Jan Karel Pieterse (6/22/2015 5:01:36 PM)

Hi Andrew,

"Download" is just a header, the download links are below that heading. There are three of them:
One for Excel, one for Word and one for Access.

 


Comment by: Eliton Elias (6/23/2015 7:21:27 PM)

Hi,
I'm struggling to create a macro to populate the treeview with data from the spreadsheet.
Thanks in advance

 


Comment by: Peter Thornton (6/24/2015 6:09:39 PM)

Hi Eliton,
Without knowing anything about how your data is arranged it's hard to suggest a macro. There are several approaches.

 


Comment by: Niels de Nies (6/29/2015 1:44:44 PM)

Jan Karel,
Ik heb -als prototype- in Access een database gebouwd waarmee een product breakdown van een te ontwerpen installatie gemaakt kan worden. Jullie Treeview werkt daarin prachtig.
Nu wordt mij gevraagd het prototype op te schalen naar SQL. Ik zou Access kunnen handhaven als client, maar mijn klant heeft dat liever niet.

Mijn vraag is: kan jullie Treeview ook in een VB omgeving draaien. De code zelf lijkt me niet zo'n probleem, maar ik zou zelf niet weten hoe ik met het ufTreeview scherm uit de voeten moet. Kan je me adviseren?

Vriendelijke groet, Niels de Nies

 


Comment by: Jan Karel Pieterse (6/29/2015 2:43:18 PM)

Hallo Niels,

VB heeft een eigen treeview die je kunt gebruiken voor zover ik weet. Ik neem overigens aan VB.NET?

 


Comment by: Niels de Nies (6/29/2015 3:14:24 PM)

Klopt: VB.NET.
Ik had er nog niet naar gezocht. Op is het zich prettig dat er een treeview in VB.NET is, maar ik ben erg tevreden met jullie Treeview. Die ken ik nu en doet alles waar de gebruiker behoefte aan heeft.

Maar goed, ik snap dat het niet opportuun is om naast de standaard van .NET nog een andere treeview te gaan maken.

Dank je wel. En nogmaals, voor Access is die van jullie TOP.

 


Comment by: Jonathan Brown (8/2/2015 8:57:17 AM)

Is there a way to programmatically change the state of a node without triggering the nodecheck event again?

This may not be necessary as I really just want the treeview to behave the way you have it in the tristate mode. Specifically when I click a parent node, all of it's childnodes update accordingly. But then I want it to execute some code after all the nodes are finished updating.

I've found that when I put code into the mctree_nodecheck event it repeats that code for every child node. Following the debug I can see that it raises the nodecheck event each time a checkbox changes its state.

I've tried using application.enableevents = false to prevent it from retriggering the nodecheck event when a checkbox's state changes but it doesn't seem to work the way I want it to.

Would I have to create something like a new node_aftercheck(cNode as clsNode) event that'll run after the nodecheck event? Is that possible?

 


Comment by: Jan Karel Pieterse (8/3/2015 4:23:54 PM)

Hi Jonathan,

That is the beauty of this control: you can massage it to do exactly what you want.

One way to have this work is by adding a public boolean to the clsTreeView class (at the top):

Public NoEvents As Boolean

Then in your userform you can temporarily switch off events by setting:

mcTree.NoEvents = True


Of course at the end of the code you need to reset:

mcTree.NoEvents = False


To make this work, the clsTreeview needs this line of code in any event, the most important one being:

Friend Sub NodeEventRouter


In that sub, the first line of code should become:

If NoEvent Then Exit Sub


Add that code to any routine that contains the RaiseEvent line of code and you should be good to go.

 


Comment by: Ken (8/6/2015 3:41:14 AM)

Sorry to be dense, but I am not seeing where I can locate some array or collection or similar from which I can extract a list of checked boxes? For example, I checked 4.0003 and 4.0005, which also selects the parent node...How can I iterate through and use these 3 checked items in my next operation?
Thanks!

 


Comment by: Jan Karel Pieterse (8/6/2015 11:04:45 AM)

Hi Ken,

You would traverse the nodes of the tree like so:

    Dim cNode As clsNode
    For Each cNode in mcTree.Nodes
        If cNode.Checked Then
            'Now do something
        Else
            'Or don't
        End If
    Next

 


Comment by: Anthony Powers (8/17/2015 3:28:59 PM)

I have studied your example for many hours. I am amazed at your work!
May I ask a question concerning the Access database?
If I add more columns to a table, just as an example, “Gender” to tblStudents, how can I change the forecolor of student names (nodes) based on their gender criteria, and without this criteria being in the treeview. Or if I add a true/false column to tblClasses, and do not want this to show up in the treeview, but want to change the forecore of “Class” nodes based on true/false criteria how can I do this? I’ve tried everything but can’t figure it out.
Thank you very much for your assistance.
Kind regards,
Anthony

 


Comment by: Jan Karel Pieterse (8/17/2015 5:30:33 PM)

Hi Anthony,

You would set the ForeColor property of the node in question when you are loading the nodes into the tree.
If you change any formatting of a node once the tree is already displaying, you need to refresh the tree:

mcTree.Refresh

 


Comment by: Anthony Powers (8/18/2015 12:29:35 AM)

Hi Jan,
Thank you for your response. Much appreciated!
My problem is with the criteria. How can I refer to information in the tables? For example: If Gender is Male then node text changes color. Can you provide a simple example?
Thank you for your support and kindness,
Anthony

 


Comment by: Jan Karel Pieterse (8/18/2015 10:05:17 AM)

Hi Anthony,

You would read the information of tblStudents and extract the gender information belonging to each node and color the nodes accordingly. Some air code:

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

 


Comment by: Anthony Powers (8/18/2015 10:31:41 AM)

Hi Jan,

This is what I'm looking for. Thank you so very much! You are wonderful!

Anthony

 


Comment by: Josh Feuerstein (8/18/2015 7:53:10 PM)

I've been emailing with the devs about some questions but I thought this one might get a more generally useful answer to all:

If my control is in TriState mode, and I check a box, is there any way to detect which checkbox was *actually* clicked by the user?
The
NodeCheck
event cascades and triggers for every single node up and down its branch, and that can be less than helpful.

 


Comment by: Peter Thornton (8/19/2015 1:45:14 PM)

Events to trap dependent TriState changes were added in v026, actually on request (see the Versions sheet in the Excel demo).

In typical scenarios which particular checkbox triggered the change is not important, but knowing all changes that have occured as a result is. The current arrangement can also help the developer, eg after programatically changing just one checkbox, all parents to the root and child checkboxes that may have changed as a result are returned in the chain of events. In turn a DB can be simply and fully updated in real time if required. Without that the developer would need to calculate the logic (not easy) or compare against record of previous checked nodes in the the entire treeview.

If the events are counter productive for your purposes (I'd be curious to know why) you could customize the treeview to only trigger the event for a single manually changed checkbox. Or, add an additional flag in the event to indicate it was the one that triggered the chain (possible but complicated). Alternatively in your own form record the time of the event but only if the time is more than say 0.2sec since the previous change; if more than 0.2sec assume the event is teh one clicked by the user as it will be the first event in the chain, otherwise it's dependent event. A third approach would be to use v025!

 


Comment by: Josh Feuerstein (8/19/2015 5:38:33 PM)

The simple reason why is that the ultimate state is good, but I might want to provide some feedback to the user, or log which node was clicked for debugging purposes, etc.

 


Comment by: Peter Thornton (8/20/2015 6:33:17 PM)

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.

 


Comment by: McPegasus (9/2/2015 8:57:02 AM)

Hello


How can I change the font size?


Thank you.

 


Comment by: Jan Karel Pieterse (9/2/2015 10:22:24 AM)

Hi McPegasus,

The Excel demo contains a Font size control, check out the control's event to find out how to change the font size.

 


Comment by: McPegasus (9/2/2015 12:06:59 PM)

Fantastic Jan!

Thank you very much. I am looking for a substitute to the TreeView and now, I prefer your system ;).

Just missing OLEDragDrop, OLECompleteDrag, etc.), but we will know how to live without it.

Built-in example: Treeview-25 compiled in 2003

Private Sub InitializeDemo1()

...

    With mcTree

        Call .NodesClear ' if reusing an exisiting treeclass

        .AppName = AppName

.TreeControl.Font.Size = 16


McPegasus
Valencia
Spain

 


Comment by: John Bingham (9/7/2015 5:35:39 AM)

Hi,

Nice work!

In design view the tree sub-form insists on being at the front; This can be very inconvenient when the tree is located on a tab control, and many other controls are actually sharing the same piece of screen real-estate.

Is there any way to control/prevent this?

Thanks

John

 


Comment by: Peter Thornton (9/7/2015 1:22:12 PM)

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!

 


Comment by: Tom (9/11/2015 10:25:11 PM)

Great job guys! I was looking for a small treeview example due to the 32/64 'broken' dll stuff.
This is not a small example, this is great work!

Chapeau!

Tom

 


Comment by: David Reid (9/18/2015 2:26:39 AM)

Could you please help me to understand why your Access Treeview sample is not loading properly? I'm getting the error message 438 (Ojbect doesn't support this property or method) at the line: lngIndex = ctl.TabIndex in the subTreeView module. That problem then cascades into other errors. I just downloaded the file a couple of minutes ago and my best guess is that a reference is not set--but I don't see that any are missing. Thank you.

 


Comment by: Peter Thornton (9/18/2015 1:16:36 PM)

David,
Strange the code breaks there as that line is under 'On Error Resume Next'.
In your VBE Tools/Options/General do you have 'Break on all errors' checked? By default that should be 'Break on unhandled errors'.

 


Comment by: David Reid (9/18/2015 6:16:22 PM)

You're right, Peter. All is good after I set the default back to 'Break on Unhandled Errors". Thank you.

 


Comment by: H.KILIÇ (10/4/2015 4:02:57 AM)

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&#351;ekkürler.

 


Comment by: Simon (10/14/2015 4:48:56 PM)

In the "Instructions" tab of the Excel files it has the following instruction:

Add a reference for the Treeview Frame container
Set mcTree.Control = Me.frmTreeControl


However, the line should be:
Set mcTree.TreeControl = Me.frmTreeControl


Please can you update. Thanks.

 


Comment by: Thomas Koester (10/15/2015 1:55:08 PM)

Hello,

I would like to scroll the treeview with the mouse wheel (I am on Access).

Do you think that it would be possible? How would you do it?

It would be sufficient if you could get me started with some hooks and hints. I am already fairly familiar with your code due to some other custom patches.

Thanks
Thomas

 


Comment by: Jan Karel Pieterse (10/15/2015 2:49:22 PM)

Hi Thomas,

Have a look at this thread on MrExcel:

http://www.ozgrid.com/forum/showthread.php?t=184493

 


Comment by: Thomas Koester (10/19/2015 8:11:32 AM)

Hi Jan,

thanks a lot. That's what I was looking for.

Thomas

 


Comment by: Jaap van der Sijp (10/20/2015 9:18:39 AM)

Tip:
If you use icons in the tree view and FullWidth=False, then the icons are placed in the label control. When clicking the label the background colour gets changed (default blue), but the all white pixels in the icon also become blue. Going to FullWidth=True solves that , but may not be what you want. An alternative is to edit the image and change all white pixels (FFFFFF) to "nearly" white (FEFEFE). Then only the text part background will turn blue. this looks a lot cleaner.

 


Comment by: Peter Thornton (10/20/2015 9:00:06 PM)

@Simon,
Thanks for flagging the typo in the instructions sheet, the demo has been updated.

@Jaap,
As you say with fullwidth=true icons appear in their own image controls, but with fullwidth=false icons are embedded in the node label.

With fullwidth=false, normally which RGB pixels in the icon adopt label's backcolor (eg when active and given vbHighlight) depend on which, if any, RGB was defined as the mask colour when the image was saved.

It seems the demo icons were given a white mask, I'm sure there was a good reason at the time!

 


Comment by: Paul Katz (10/24/2015 10:27:05 PM)

Hi - thank you for developing this control and making it publicly available. It seems that I have a good business need for it. I'm hoping you can answer a couple of questions before I dive in to decided if it's the right control to use.

I'm developing an Excel app, and have data which will source the TreeView control in a worksheet. The size of the table is about 7 columns x 4000 rows. The data are sorted left to right (root, lvl1, ..., lvl6). Users will be expected to navigate the tree and drag/drop different node selections to list boxes on my form.

(1) Will the control reasonably manage data of this size?
(2) Do you have a BuildTreeFromTable() type routine to automatically populate the control from this Excel range (or would I need to program that myself)?

Thanks again for your time,
Paul

 


Comment by: Jan Karel Pieterse (10/25/2015 2:24:49 PM)

Hi Paul,

That number of nodes should not be a problem for the control. But it is easy enough to test by just adding them all to a single rootnode.

Then if you're happy with the performance, writing a bit of code that reads your table and builds the tree is fairly straightforward. Perhaps if you expand all comments on this page you'll find some sample code?

See:
http://www.jkp-ads.com/articles/treeview.asp?AllComments=True

 


Comment by: Pavlo (11/12/2015 9:20:02 PM)

Does this tree view allows word wrap, if node text is too long?
If not, are there any chances to add such feature?

 


Comment by: Peter Thornton (11/16/2015 12:56:55 PM)

Hi Pavlo,
Normally treeviews do not display word-wrap or multi-line text, however the horizontal scrollbar should automatically adjust to accommodate the longest text in any node.

If your source text includes vbLf and/or vbCrLf (or in Mac vbCr), replace these with "¶ " (Chr(182) + space) before populating, and convert back if reading.

Try manually editing a node (EnableLabelEdit=true, then dbl-click or F2 on a node) and entering or pasting some long multi-line text. Then F2 again and have second look. We've done a lot of work to try and handle this nicely!

 


Comment by: Brain S. (11/28/2015 3:20:07 AM)

First, thanks for your great control and alternative to the common controls mess.

I'm trying to use your treeview in Access 2013 and it works fine with one exception. I have it inside a tab control page. When I select a node all other controls within the tab page flicker like they're being repainted a few times. It's very distracting. There is no flickering if the treeview is placed outside the tab control. I really need to place the treeview inside the tab control so moving it out isn't an option.

Any suggestions on how to eliminate the flicker?

Thanks

 


Comment by: Peter Thornton (12/2/2015 10:32:24 AM)

Hi Brian,
If you want to send me (or put on a file sharing site) a non sensitive file that replicates what you describe I'll have a look. My address is in the headers.

 


Comment by: Jeff Silcock (12/9/2015 3:17:50 PM)

Been using your TreeView control for a while now and it is brilliant! - thanks very much. Wanting to add a second control, and with this one would like to be able to expand/collapse all the nodes with one click. I presume this must be possible, but would be glad of some steer how to do it please.

 


Comment by: Jan Karel Pieterse (12/9/2015 4:42:44 PM)

Hi Jeff,

I think what you need is the ExpandToLevel method of the clsTreeview class, which you can call from within your userform.

 


Comment by: Joel (12/9/2015 7:51:26 PM)

Thank you so much for developing this tool. I only just learned that the ActiveX treeview control is not supported in 64 bit Access when a user of one my applications found that it was no longer working. This is a real help.

I can see that you can have multiple instances across multiple forms, however is it possible to have multiple instances in the same form? I typically use a single master form with a tab control. I would like to place the subTreeView form in multiple tabs on the same form but so far haven't been able to.

 


Comment by: Joel (12/9/2015 9:06:46 PM)

Just to follow up from my previous question re multiple treeviews in a tab control...
I can get two treeviews to load in the same form, however the click event (mcTree_Click) is only applied to one of the controls.

How does the mcTree_Click event become associated with the control? I would like to associate a different click event with the second control.

Many thanks if you are able to help.

 


Comment by: Jan Karel Pieterse (12/9/2015 9:15:25 PM)

Hi Joel,

You will have to define another variable for the second treeview, e.g. mcTree_2 and add it's click event (and any other events you need) to the userform's code.

 


Comment by: Joel (12/9/2015 9:46:10 PM)

Many thanks Jan. Your solution worked perfectly.

 


Comment by: Jeff Silcock (12/10/2015 12:29:41 PM)

Hello Jan that only works so far. Yes if the tree is closed and you call ExpandToLevel(2), the + gets changed to - on each top level, but you have to then click on the first node which closes that node but leaves all the others expanded. If you then issue a ExpandToLevel(0) all - get changed to +, but the lower levels on node remain visible until you again click the first one, which closes all other but expands that one!!

I must be missing something if you can help please

 


Comment by: Jan Karel Pieterse (12/10/2015 10:59:18 PM)

Hi Jeff,

Apologies for misguiding you a bit. After the expandtolevel you must force the tree to reload by using

mcTree.BuildRoot False

 


Comment by: Jeff Silcock (12/10/2015 11:08:35 PM)

Jan - Thanks, spot on, except that I had to make the BuildRoot routine Public before I could refer to it

 


Comment by: Jan Karel Pieterse (12/11/2015 12:01:10 PM)

Hi Jeff,

Sorry about that, I forgot that BuildRoot is private and that I should have advised to use the Refresh method.

 


Comment by: Joel (1/5/2016 7:14:27 PM)

Hi Jan,

I just found a small bug. In the Get Previous subroutine in the clsNode class module, the set statement should read:
    Set Previous= .ChildNodes(I - 1)
rather tham
    Set NextNode= .ChildNodes(I - 1)

Many thanks again for developing this.

 


Comment by: Peter Thornton (1/6/2016 3:47:02 PM)

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

#Const HostProject = "Access"


See the "Versions" sheet for other changes

 


Comment by: Pete Danes (1/8/2016 12:32:18 AM)

This looks insanely useful, but I haven't been able to make use of it. Your demo works beautifully, but I have been unable to import this stuff into another database. I can't import UserForm1, nor do I even see how that happens. All my databases have only three object categories: Microsoft Office Access Class Objects, Modules, and Class Modules. Your demo database also has a Forms category. Where does this come from? How do I get it into my database? My import efforts lead to an error: "Line 2: Property OleObjectBlob in ufTreeView could not be set." logged in the Temp folder. The database also says Out of Memory when I try this. The Forms category exists in Excel and Word, but not normally in Access, and I don't know how to get it there.

 


Comment by: Jan Karel Pieterse (1/8/2016 12:08:10 PM)

Hi Pete,

The Demo database has a "How do I?" button on its startup form, which tells you how to implement it in your own database. It includes some pointers on the userform bit too.

 


Comment by: Konstantin (1/18/2016 8:46:08 PM)

Good afternoon. I work with the program SolidWorks. This is a grand CAD software. But in terms of tools for user attributes (user properties), it is extremely unfortunate.
And above all, what I was looking for the last 15 years - a TreeView in Excel. Thank you very much. I still have to sort out the tool c. But the main thing for me are opportunities, which are still not there.
I understand that you do not have to know what SolidWorks. Therefore, I say that because of user data formed an overview of engineered project, which add and use, and accountant and economist, and the storekeeper and buyer, and the technology and master in production. With your instrument, I can make the right data available to all. And while I do not have to persuade everyone to buy a license. This is a huge deal.
And the question. Do you plan to expand your item for ListView element layering data. After all, as I understand it, at the moment TreeView - it's the same hierarchical ListView, but in one column.
And, if possible expansion of the TreeView, the second request - possibility of multi-line entry in the cell. This feature is especially painful for custom properties SolidWorks.
P.S. I know that there is a Microsoft Hierarchical FlexGrid, but connected to the VBA project it is difficult.
You can use the sheet like Excel, but these quickly grow beyond the screen. Additionally, the program itself is maximized. While the basic form to fill in the properties of the screen takes and leaves the visual access to the working model of the project in SolidWorks.

 


Comment by: Dominik (1/25/2016 9:27:17 PM)

Hello,

how is it possible in Access to call the

Private Sub mcTree_Click(cNode As clsNode)


from a button?

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.

 


Comment by: Jan Karel Pieterse (1/26/2016 7:28:40 AM)

Hi Dominik,

You can, if you pass to it the node you "clicked":

Dim cNode As clsNode
'Some code that sets the right Node, such as:
Set cNode = mcTree.ActiveNode
mcTree_Click cNode

 


Comment by: Paul Charbonnet (1/27/2016 6:58:47 PM)

Can your control be compiled into an OCX using VB6. I tried, but it is likely dependent on objects that are part of Excel VBA and not VB6

 


Comment by: Jan Karel Pieterse (1/27/2016 7:37:08 PM)

Hi Paul,

There is no point in using our control in VB6 as VB6 is strictly 32 bit and can hence use its own treeview control.

 


Comment by: Paul Charbonnet (1/27/2016 9:37:18 PM)

The MSCOMCTL.OCX that contains the treeview has serious compatibility problems with Windows 8 and above

 


Comment by: Jan Karel Pieterse (1/28/2016 7:35:25 AM)

Hi Paul,

Got it. I'm afraid we have no plans to port the thing to VB6. But you are free to use our ideas to try and port it yourself. I think the base principles would be very much the same.

 


Comment by: Joseph Volence (1/29/2016 7:50:34 PM)

I have this working very well in Access 2010, but there is a slight delay when building a large tree that I did not experience with the mscomctl tree.

I have the tree subform in a popup form, and this causes the whole form not to display until the tree is built.

Is there a way to build the tree async in the background, and then display when it is fully built - while, in the meantime, allowing the rest of the form to be usable?

I toyed with the idea of pre-loading the popup form and setting it to "visible=false" before it is used, but I can't get the form to stay hidden. Once the tree is loaded, it unhides the form.    I also tried the "acHidden" argument of docmd with the same result.

Thanks!

 


Comment by: Jan Karel Pieterse (1/29/2016 8:55:34 PM)

Hi Joseph,

I will ask our Access expert to have a look at this. In the mean time, we are also developing a high performance treeview control. This one will not be for free however.

If you are interested, let us know, so we can arrange a beta version for you to try.

 


Comment by: Yossi Dukes (2/3/2016 9:14:53 PM)

Hi, I am very interested in your treeview control. I'd like to be part of the BETA program if possible.
Regards,
Yossi

 


Comment by: Jan Karel Pieterse (2/4/2016 11:09:44 AM)

Hi Yossi,

You can already use the control. as far as I'm concerned it is past beta stage and ready for production use.

 


Comment by: Valton (2/5/2016 7:28:11 PM)

Very good development. They are at another level of programming.

 


Comment by: Anthony Warner (2/22/2016 3:51:04 AM)

Hi Jan, I am very interested in your high performance treeview control. I'd like to be part of the BETA program if possible and If you are at a production level I am very interested in purchasing it once it has been checked out.
Regards,

 


Comment by: Anthony Warner (2/22/2016 3:58:15 AM)

Hi Jan,

I overlooked in my previous comment that me end use of your production model "high performance treeview control" will be in access as I am experiencing extended load times in my forms with the winforms referenced coding of the demo.
thank you very much for having addressed the treeview issue in access as the MSCOMCTL.OCX has been extremely difficult to revise in production front ends as access has matured.

Many Thanks
Anthony Warner

 


Comment by: lukasz (2/24/2016 3:15:19 AM)

Hi Jan,

Have you found a way to speed up adding many (over 5000) elements to the userform?

 


Comment by: Jan Karel Pieterse (2/24/2016 8:28:18 AM)

Hi lukasz,

Yes we have, but that version is not available for free. Contact us at the email address below if you'd like to do beta testing.

 


Comment by: Gianluca (2/26/2016 3:34:49 PM)

Hi,
i'm geting in trouble because
'Private mUF As ufTreeView'
user-defined type not defined...
can you support on this?
I'm using access 2016 64 bit.
Thank you
Gianluca

 


Comment by: Jan Karel Pieterse (2/26/2016 4:01:17 PM)

Hi Gianluca,

Have you studied the docmentation that is in the Access demo file (the "How do I?..." button)? Especially the part about modStartup.

 


Comment by: Antonio Salva (3/10/2016 10:30:56 PM)

Hi.

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

And it must be:

Set Previous = .ChildNodes(i - 1)


Best regards.

Antonio Salvá (Spain).

 


Comment by: Jan Karel Pieterse (3/11/2016 10:28:27 AM)

Hi Antonio,

Thanks, we know and was fixed in the current build.

 


Comment by: Mike C (NZ) (3/15/2016 10:50:19 PM)

A great piece of work. Wish I had this 10 years ago working with treeview in MS Access

Well done guys.

 


Comment by: Jan Karel Pieterse (3/21/2016 7:08:26 AM)

Hi Brent,

Thanks for the tip.

 


Comment by: Loïc Le Roux (4/18/2016 4:44:06 PM)

Hi and thanks for sharing your work.

I'm far from being an expert but I'm trying to use this treeview in a project with Access 2016 64-bit and i'm facing some issues.

The main one is that the subform doesn't see its parent (in the following function), when I open the main form, and then closes by itself.


Private Function IsSubform() As Boolean
    On Error Resume Next
    IsSubform = (Not Me.Parent Is Nothing)
End Function


The demo works well but I can't figure what's wrong with my configuration.

Any help would be greatly appreciated.

Best regards,

 


Comment by: Loïc Le Roux (4/20/2016 10:47:29 AM)

Hello,

Thanks for publishing my comment but I finally found my mistake and I think you can remove it.

The issue was entirely my fault as I had pasted the line

Private mOtherFrm As Form_frmDemo

without changing form name.

I'm going deepest in using your treeview and i'm still very impressed by the work you done.

Keep on the good work !

Best regards,

 


Comment by: Jan Karel Pieterse (4/20/2016 11:28:46 AM)

Hi Loïc,

I'm glad you found the problem!

 


Comment by: Thomas Magdahl (4/29/2016 10:56:47 PM)

Hi,

Is there any way to change the ImageExpanded of a node.
I am using the expanded icon to show the state of a node.
It is not working and if it should I am not beeing able to figure it out.

        icono = "DiscardedIcon"
        mcTree.ActiveNode.ImageExpanded = icono
        mcTree.Refresh

If I reload the tree structure with the new icon reflecting the new state it displays ok. I am only trying to avoid replace the node with a new one, that has the same info but different expanded icon.

Regards

Thomas

 


Comment by: Jan Karel Pieterse (4/30/2016 11:38:45 AM)

Hi Thomas,

When you create a node, you can set its ExpanderImage property, to which you pass the collapsed and expanded icons. The control then automatically adjusts the image on click. See the demo file, routine called "InitializeDemoIcons" in ufDemo.

 


Comment by: Thomas Magdahl (5/2/2016 1:37:56 PM)

Hi Jan,

I know; it works fine when I buid up the tree at program start, from data on an Excel worksheet. At that time I figure out the state of the excel row (very simple, based on the date) and generate its associated node with the correct colapsed and expanded icon. My problem is with the code I provided in the previous post, which happens when the user interacts with the node of the displayed tree. He changes the state of the active node (date changes at the excel row) and the expanded icon of that node has to change acordingly. Sorry if I missunderstood your answer and it only can be done at node creation as in the demo?, but then the property is available and I suspect it should work and I am doing a mistake. At the demo the propperty is only used for retrieving the value but it is not changed at run time with the nodes already created.

I started to use your tree class a year ago and left the project by side for other needs. I started this days to continue on it, and I am again surprised by how good it is. My only wish would be to have 32x32 icons, but it is a minor issue for me. If it already had 32x32 I would probbably desire 64x64 :-)

 


Comment by: Jan Karel Pieterse (5/2/2016 1:55:47 PM)

Hi Thomas,

Theoretically it should suffice to refresh the tree after changing the expanded state of a node. But you do have to add the right icons to the nodes when you create them.

I expect if you create those icons yourself you can use any size you like :-)

 


Comment by: Thomas Magdahl (5/2/2016 2:22:45 PM)

Hi Jan,

Sorry for being such a pain in the neck.

If you add the line surrounded by asterixes in the demo code and at run time (with icons) double click on a node and type 456, I expect the expanded icon to change to "GreenTick".
If a refresh is necesary you can run the code and add a child to another node, and the ImageExpanded will be still the same and not "GreenTick", despite a refresh that must have be requested.

Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode)

' Validate user's manually edited node here

    If Len(NewString) >= 3 Then
        If NewString Like ("*123*") Then
            MsgBox "123 is not allowed !" & vbCr & "but try 456", , AppName
            Cancel = True    ' undo user's change
        ElseIf NewString Like "*456*" Then
            NewString = Replace(NewString, "456", "789")
'*************************************************
            cNode.ImageExpanded = "GreenTick"
'*************************************************
        End If
    End If

End Sub

Regards

Thomas

 


Comment by: Jan Karel Pieterse (5/2/2016 6:01:15 PM)

Hi Thomas,

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

 


Comment by: Thomas Magdahl (5/2/2016 7:19:07 PM)

Hi Jan,

Worked out fine. Thanks for your kind help.
Yo can not imagine how important your code (and the calendar you recomend) are for the portability of my simple application.

Regards

Thomas

 


Comment by: Jan Karel Pieterse (5/3/2016 8:56:34 AM)

Hi Thomas,

You're welcome!

 


Comment by: Jan Karel Pieterse (5/3/2016 4:58:15 PM)

Hi Thomas,

Seems I overlooked an important method and property. Changing the image of a node is as simple as:

cNode.ImageExpanded = "GreenTick"
cNode.ImageUpdate

 


Comment by: Tom (5/30/2016 9:04:35 AM)

Hi gentleman,
Thanks for your hard work!
I'm currently integrating XLS and Selenium Webdriver into Excel VBA, using your TreeView as running instance (or workbench).
Very nice to have a pleasant GUI.

One question: do you happen to have an alternative for cutcopy by keys, but using some draganddrop function?

Kind regards,

Tom

 


Comment by: Jan Karel Pieterse (5/30/2016 6:00:56 PM)

Hi Tom,

The pro version of the treeview features better performance and drag and drop. It is in Beta right now.

 


Comment by: Damian Mamani Castro (6/11/2016 11:53:10 AM)

it is good.

 


Comment by: David (6/12/2016 5:49:25 PM)

Hi, There is a way to move nodes between siblings (up/down)

I tried

If Not mcTree Is Nothing Then
        Set cNode = mcTree.ActiveNode
            If Not cNode Is Nothing Then
            
            idx = GetColIndex(cNode)
            Set cParent = cNode.ParentNode
            mcTree.Move cNode, cParent, idx - 1

but it don't work

 


Comment by: Peter Thornton (6/14/2016 10:28:56 AM)

Hi David,

Your example should work, though obviously need to ensure vBefore:=idx-1 or vAfter:=idx+1 are between 1 and count of siblings.

When done (after moving any nodes or anything else that's going to change what's displayed) need to call mcTree.Refresh

FWIW our long awaited Pro version (alpha due this week) includes built-in nudge up/down, promote/demote sideways functions, amongst far more important features!

 


Comment by: Bill Dowton (6/17/2016 2:34:14 PM)

Hi
A brilliant piece of work and I'll definitely make a donation! :) I have the control working but am struggling with the icons. If I reference the ones in the demo everything works fine but I don't know how to create a frame with images although I've spent several hours trying :( I've also tried loading my images into the frImages but this doesn't help either. Can you help?
Kind regards
ps I'd be interested in the Pro version. Any idea when it'll be available?

 


Comment by: Peter Thornton (6/18/2016 2:16:06 PM)

Hi Bill,

For some reason adding pictures to image controls in a Frame in Access don't seem to stick. Try doing same in a temporary form in say Excel, then select and copy the image controls to the Frame in Access, can do all in one go.

The treeview 'pro' and listview alternative are at alpha stage, contact us off-line if interested to test either.

 


Comment by: Bill (6/29/2016 4:06:28 PM)

Hi Peter

I now have the control working (almost) perfectly. I say "almost" because I can't get the icons to resize. I've tried resizing them myself, changing font sizes, node height etc. but they just stay the same. Any idea what I could be doing wrong?

 


Comment by: Jan Karel Pieterse (6/29/2016 4:57:06 PM)

Hi Bill,

You should create larger icons yourself and update the picture properties of each icon in the icon frame on the userform.

 


Comment by: Peter Thornton (6/29/2016 5:55:51 PM)

Hi Bill,
It depends what you mean by "get the icons to resize". If you mean size the control (label or separate image control depending on 'fullwidth'), change these constants to suit:
mcIconSize and mcIconPad (in points, typically in Windows 1 pixel = 0.75pt)

But if you mean resize the actual image you need to size the original source to suit and update as Jan Karel suggested.

FWIW sizing of icons to the supplied image is handled automatically in the 'pro' version. Did you get my reply in response to your enquiry about the 'pro' ?

 


Comment by: Pete Danes (6/30/2016 4:03:30 PM)

Is there a hard-coded limit in this control? I am working on an implementation that has around 20,000 nodes, up to 23 levels deep. Can I expect any difficulties with this?

 


Comment by: Jan Karel Pieterse (6/30/2016 4:08:03 PM)

Hi Pete,

No limits in the control, but with that many nodes the pro version is probably your best bet.

I'll forward your details to Peter Thornton.

 


Comment by: Tony Matyas (7/8/2016 8:45:26 AM)

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)

 


Comment by: Peter Thornton (7/8/2016 4:06:22 PM)

Hi Tony,
Not sure why you're getting that error. AFAIK it's been working for most users for a long time with no problem. Could you send me the file so I can have a look. Also say which Excel version you're using.

 


Comment by: Alan Santos (7/22/2016 11:35:24 PM)

Hi!Excellent job! In the TreeView I have a doubt:

How can I adjust for when I start the Form to return the tree that was exported for the last time and that is in the "dump data" sheet?

Thanks a lot and congratulations.

 


Comment by: Peter Thornton (8/1/2016 3:45:16 PM)

Hi Alan,
Thanks for your comments but not sure I understand your question - are you saying you want to reload the data that was dumped by the "dump data" example in Excel?

 


Comment by: Tony Matyas (8/2/2016 8:42:14 AM)

Hi Alan,

a hint to clarify your problem: it seems to me the Data Dump procedure has been deactivated in the 026 Demo Version just for temporary testing, so that no data dump is executed when clicking to the related command button. If you have a look at the ufDemo cmdGetData_Click, you should possibly find two inserted code lines, the second one stops the procedure with an 'Exit Sub'.

 


Comment by: John Murphy (8/5/2016 2:41:54 PM)

Hi,
I have commented out the 'Exit Sub' in 'cmdGetData_Click' procedure (as per Tony Matyas' suggestion) and 'dump data' now works beautifully but, how do I get it back?

Thanks and congratulation on a brilliant piece of work!

 


Comment by: Jan Karel Pieterse (8/5/2016 6:42:12 PM)

Hi John,

There is no code in the demo to read the range back into the treeview I'm afraid.

 


Comment by: Jan Karel Pieterse (8/8/2016 2:34:02 PM)

Hi John,

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

 


Comment by: John Murphy (8/8/2016 4:16:27 PM)

That's absolutely BRILLIANT!!!

I can now make changes within the TreeView, dump them and then reinstate them, which is exactly what I wanted.

Thanks very much for your help and efforts, and for responding so quickly.

Thanks a million!

 


Comment by: Jan Karel Pieterse (8/8/2016 4:43:43 PM)

Hi John,

You're welcome!

 


Comment by: Adam Taylor (8/8/2016 10:36:07 PM)

Hi - GREAT product, thanks for sharing. I have an Access-related issue in relation to this. I've got the treeview shown as a subform, and sometimes if it falls over it can revert to showing with the flags. It's working great other than strange behaviour in the event of an unhandled error in my application.

Is there a way to check for this happening and protect against it? Some simple command that can switch it back on? Or do I need to close and re-open the form as seems to be the case at present?

Thanks in advance,

Adam

 


Comment by: Jan Karel Pieterse (8/9/2016 9:23:17 AM)

Hi Adam,

My guess is that the treeview reverts to the default settings when your VBA code runs into a runtime error, but I can't really tell :-) I'm surprised it even keeps showing as I'd expect a code reset to remove the tree altogether?

 


Comment by: Peter Thornton (8/12/2016 3:02:05 PM)

Hi Adam,
Can you repro' when "it falls over". Add something to flag or debug when the subform's open and close events fire.

In some scenarios the subform seems to want to close and immediately reopen, eg print preview and report view. If that happens the userform that contains the treeview will have gone which might explain what you describe.

If this sounds familiar to any Access folk and in particular how to handle this scenario please contact me off-line!

 


Comment by: Tony Matyas (8/23/2016 4:23:25 PM)

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?

Gratefully yours

 


Comment by: Peter Thornton (8/24/2016 11:45:26 AM)

Hi Tony,

That's certainly possible: [icon] / [checkbox] / [tag-label] / node-caption. If applicable tag-labels could be included individually only for nodes only which have a tag caption.

It would be too much to give full details here but to point you in the right direction look at how the respective node controls are added, and adapt for your tag labels. Although it's relatively straightforward there are some small details to include, if this is for a professional project it might be cost effective if we implement it for you.

 


Comment by: Scott Cordwell (10/2/2016 11:36:56 PM)

Hi,
Great work guys. I've been using the tree view in Access 2007 using the runtime for a while now. I just installed onto a Windows 10 PC that has Access 2013 retail on it as well. I'm getting a "System Error &H8007007E (-21470224770)" when opening the form with the treeview on it in the runtime environment. Then vba generates a "2926 - Because of your security settings and current security policy, this control has been disabled...." error. The front end works fine if opened using Access 2013 Retail? I've checked trusted locations etc, with no result. Any tips would be appreciated. I'll be getting the commercial version when released.
Thanks.
Scott

 


Comment by: Jan Karel Pieterse (10/3/2016 7:04:31 AM)

Hi Scott,

Are you saying you have two Access versions installed side by side? In my experience this often leads to problems with the references. I'd advise to try whether it works on a single-install of Access 2013.

 


Comment by: Scott Cordwell (10/3/2016 7:37:01 AM)

Hi Jan,
Yes, I use Sagekey Software's Access Runtime install builder to install the front end application and runtime on all PCs regardless of whether they have Access Retail on them or not. Works really well (for years) and has never adversely affected any existing Access installs. I suspect it's either a permissions issue or an FM20.dll version problem on this particular PC. I'll have to do some more digging.
Thanks
Scott

 


Comment by: Jan Karel Pieterse (10/3/2016 4:45:20 PM)

Hi Scott,

OK, please let us know if you keep having problems or if you find the root cause.

 


Comment by: Kieran Shirey (10/12/2016 2:16:04 AM)

Hi,
My license has expired. Is it possible to move from Access 2013 to Office 365 with a database that has a Treeview Control in it?

 


Comment by: Jan Karel Pieterse (10/12/2016 6:53:49 AM)

Hi Kieran,

you should be able to provided you install the 32 bit version of Office 365 (default). To be future-proof, perhaps now is the time to bite the bullet and implement our treeview ?

 


Comment by: Bill Cawley (10/17/2016 11:40:31 AM)

THANK YOU!

I have programmed - on and off - for over 30 years, yet I still cannot find how to install a Treeview addon to my Excel, even when following the rules conscientiously. Nor do Microsoft provide any examples that I could emulate.

Your example download works fine. You have saved me a great deal of time.

 


Comment by: Gestec Video (11/2/2016 6:55:31 PM)

I'm using your treeview control with Access 2016, but it doesn't work properly.
The problem is that the captión of some nodes are hiden when I expand or collapse a node, but when I click on the caption of the hidden node, it appear.

The icon to expand and collapse doesn't respond when I click over it.
I have to chose de option 'expander icon'.

The problem also happen with your demo.

 


Comment by: Jan Karel Pieterse (11/2/2016 7:41:10 PM)

Hi Gestec,

This is a known (temporary) problem with Office 365. Microsoft is working on a fix which should be published to the people which are in the fast track update schedule within the next couple of days.

 


Comment by: Bruno MARCHAL (11/6/2016 6:35:31 AM)

Hi,

First of All Thanks for all the Job Done !

Questions
It seems it doesn't work with an accdb file
I've tried to include the reference of Form 2 which is located in another folder but it can't compile the code due to problem of Uftreeview definition that is defined in user32.dll I assume.

Any tips?

I intend to Donate cause i am also interested in Drag & Drop Features. How does it work?

Thanks

 


Comment by: Peter Thornton (11/7/2016 1:14:22 PM)

Hi Bruno, thanks for your comments!

I'm not sure why it's not working as an accdb for you. I can simply convert the demo mdb file to an accdb with SaveAs. Or, create a new accdb and drag the modules from the mdb. To add the Userform see the CheckUF function. Also get the v26 class modules in the Excel download if you haven't already. If stuck contact me off-line and I'll return an accdb (my address is in the headers).

We've only implemented Drag & Drop in the 'pro' version (main feature is performance with much larger treeviews). This is currently available as beta in Excel & Word, and soon for Access.

 


Comment by: Kerry (11/9/2016 4:14:17 AM)

First I wanted to say thanks a lot for creating the treeview control. It is a very useful functionality, and I wish MS had the control control available by default for 64 bit. I was wondering if you have code that can be used to populate the treeview control you created recursively. Thanks again.

 


Comment by: Peter Thornton (11/9/2016 11:43:13 AM)

Hi Kerry, thank you for your comments!

Without knowing how your source data is arranged and the hierarchical relationships are defined, it’s difficult to suggest a generic recursive approach to populate the treeview.

If you can’t find an example to adapt maybe we can sort out something privately for you.

 


Comment by: Rob Sloan (11/17/2016 2:04:04 AM)

This looks truly amazing. Hoping to likewise tackle the recursive issue to populate the tree view. Have a somewhat simple table: PK_Id, Name, Parent_Id (with Self Join to PK_Id) which can go X number of levels deep. I will take a look for some examples. But if you have any ideas, that would be great!

 


Comment by: Peter Thornton (11/17/2016 2:57:04 PM)

Hi Rob,
Even with your description afraid I can't visualise how your data is organised, or even if you actually need a recursive function.

If you're new to recursive functions perhaps the key thing to consider is when variables should be be passed ByRef or ByVal. Typically with hierarchical structures the 'Level' counter should be ByVal as it increments, so it can reset after a given branch has completed.

There's a recursive function in the demo for reading the treeview. If stuck you can send me an example of what you've got off-line and I'll take a look.

 


Comment by: Rob Sloan (11/22/2016 4:53:04 PM)

I was able to use a recursive CTE in SQL (the backend for this database) and populate the treeview. The last couple things I am stuck on are 1) creating new properties for the clsNode that would only show the checkbox at the root node and 2) if possible, making certain root nodes read only (cannot unselect check box after marked as checked on a "for each cnode in mctree.nodes"). This is going to work wonders!

 


Comment by: Peter Thornton (11/30/2016 1:17:50 PM)

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

 


Comment by: Damon (12/12/2016 10:23:21 PM)

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

errDone:
    FullPath = s
End Function



Thanks again so much!
Damon

 


Comment by: Jan Karel Pieterse (12/13/2016 6:50:13 AM)

Hi Damon,

The best thing to do is open the example Access file and look at the "manual" which can be reached from the "How do I...?" button on the startup form (or open the frmHelp form).

 


Comment by: Jan Karel Pieterse (12/13/2016 6:53:15 AM)

Hi Damon,

As to your other questions:

2. I guess it doesn't make much of a difference. Though using just the filename risks duplicates.

3. This is precisely why our treeview is so flexible: you can add any functionality you like :-)

 


Comment by: (12/19/2016 4:39:27 PM)

Sorry - the "Contact Us" link isn't working 'cause I don't have an email client registered at this location. And I thought there may be value in sharing this.

I'm interested in the beta tests - but.

I'm an Access developer and rarely work with Excel.
Is the listgrid control something that would be usefull to a Access only person like myself?

As for the treeview - friggin brilliant so far.

BC

 


Comment by: Jan Karel Pieterse (12/19/2016 4:55:31 PM)

Hi BC,

I'll add you to the beta program for sure. Just send an email to i n f o AT j k p - a d s . c o m.
I have no idea whether the listgrid would be useful to you. I would guess Access forms would do the trick nicely for displaying records.

 


Comment by: Peter Nguyen (1/5/2017 8:03:41 PM)

Hi Rob,
Would you post your code that populate the treeview from SQL backend?

Thanks

 


Comment by: Christian (1/6/2017 11:09:44 PM)

Hello Jan,

I have implemented your treeview successfully in my access database. After adding a header and footer to my form it doesn't work any more. Run Error '-2147221494(8004000a)':
Refresh: ' TreeControl' frame is not referenced
After deleting the header and footer the treeview works again. What should I do?

Regards
Christian

 


Comment by: Peter Thornton (1/7/2017 12:50:58 PM)

Hi Christian,
I can't reproduce your error after adding a header or footer, not sure why it fails for you. What should happen is this:

In the subform's load event an MSForms Frame is created and a reference retained mfrTreeControl. When the Treeview is created the next line should pass this reference to the treeview, in the demo/subform see Property pTreeview.

I suspect what's happening for you is some action is causing your mfrTreeControl reference to lose scope, after the subForms' load event and before this line:
Set mTreeview.TreeControl = mfrTreeControl

Anything that causes your project to recompile while the form is running would result in variables losing scope.

 


Comment by: Christian (1/9/2017 1:38:47 PM)

Hi Jan,

I have solved the problem with the header and footer. Now I try to adjust the icons for my application. At first I select the image on the frame frImage. Then I chose properties. Under picture I try to change the picture, but it doesn't work. All other fields, like Name, can be edit.
Is this the wrong way to adjust the images/icons?

 


Comment by: Peter Thornton (1/9/2017 1:56:09 PM)

Hi Christian,
For some reason it can be difficult or impossible to apply the picture to the MSForms image controls in Access. The workaround is create your images in a temporary userform in Excel or Word, and copy them into the Frame in Access. Be sure to name image controls as required and with unique names.

 


Comment by: Julian Rodriguez (1/13/2017 9:49:50 PM)

Hi Peter:
First of al allow to join to all the congratulations you've recieved so far for this brilliant piece of work. It is elegant, functional and I am sure that for many of us a relief after years of fighting with the problems of office and windowa versions compatibiliy of their treeview included ui the Microsoft web commons dll (mcotl).
I have implemenyed your treeview in my appplications and so far goes so nicely. However, I noticed that when the nodes caption is wider than the subform treeview let say when expanding the tree, the lef-roght scrll bat shows up which is correct but, when I collapse back the tree and the caption of the nodes are within the limits of the subform, the scroll bar does not dissapear and stays on. Curiously this does not happen witn the up and down scroll bars. Is this behaviour normal? Is it a bug?
Than you very much for your support and of course your code and for allowing us to use it

 


Comment by: Peter Thornton (1/14/2017 2:30:23 PM)

Hi Julian, thanks for your comments

It’s not a bug but I agree not ideal. For the vertical scrollbar it’s relatively simple to size the scrollheight according to number of visible nodes, or turn on/off if the total height of visible nodes is more or less than the height of the treeview.

There’s much more to consider with the horizontal scrollbar. As written it displays and sizes to fit the widest node displayed in the lifetime of the treeview. That might be as a result of expanding nodes, or scrolling down and finding a wider node, or editing a node.

In the scenario you described you might reasonably expect the scrollbar to adjust according to the node expanded in front of you, but what about wider nodes further down tree. Also, the widest node might not be in the highest expanded level, a level-1 node with a long caption might be wider than a level-3 node with a short caption. The scrollbar could be adjusted in real time as the tree is scrolled vertically to fit the current view, but would give an unsightly appearance while scrolling vertically.

The best solution might be to size the scrollbar to the widest node in the current overall expanded configuration of the treeview, including out of site nodes. Indeed that’s possible, but more code than you might imagine and in a large treeview might compromise performance.

 


Comment by: Julián Rodríguez (1/15/2017 7:52:01 PM)

Hi Peter and Christian:

1. Peter I understand what you explained and I think we can live with it. But what felt strange to me was that the horizontal scroll bars stay in a totally colapsed tree with all the nodes within the treview control in number and size. Anyway, as I told you not big deal and I will perfectly will live with it. It is a wonderful control that you have gifted to all of us.

2. Christian: I think, if I have not missunderstood you, that I have run with the same problema many times. The way I get around it is as follows: A. I get into de editor VBA tool window, look for the forms in the left side tree, and remove the ufTreeview object without exporting it. B. I close the aplication and open it again pressing the shift key so it goes to the Access window without executing macros and without openning forms. C. Then I run the startup macro which ask me if I want to create a new userform required for the correct functioning of the treeview. I say yes and it creates it. D. Then I close again the application and I am asked if I want to save the changes in the UF ufTreeview. I say yes and get out. E. I open normally the application and when open the demo form I close it, open the subform subTreeview which shows me the frame control with the my image controls and then it lets mi to change the image again by looking for it whenever I have the iamge I want to link to the image control.
I do not know why it does this acces. It happened to me whenever I got a crush in my application and it is like something gets corrupted in the userform or so. I am not an expert in programming and I don't understand very well this behaviour. The important thing is that I can get around it! :)

 


Comment by: Christian M (1/31/2017 8:07:30 PM)

Jan/Peter

Well done, what a wonderful control. In the process of updating the code to add data columns to the right of the tree I noticed what I think is a typo. Shouldn't msngTopLabel be .Width instead?


'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?

 


Comment by: Peter Thornton (2/1/2017 1:29:07 AM)

Hi Christian M,
Thank you for your comments!

Typo is a nice way of putting it but looking afresh not sure what the 'If msngTopLabel < mcFullWidth' was supposed to accomplish.

If the treeview is not resizable during runtime, could change the Const mcFullWidth = 600 to a module level variable and assign its value as the treeview width (TreeControl) if the FullWidth option is set.

It depends what you mean by "updates" but you're welcome to send them to me off-line. Others have also added columns.

 


Comment by: James (2/1/2017 11:51:01 AM)

Hi,
Is there any way to increase the Treeview line thickness?
Thanks,
James

 


Comment by: Jan Karel Pieterse (2/1/2017 1:37:21 PM)

Hi James,

You could play with the width and height of the label controls we've used for the lines perhaps. Those are controlled by this line in clsTree:


                    With cRoot.HLine
                        .Top = msngTopHV + mlVisCount * msngNodeHeight
                        .Left = mcLineLeft
                        .Caption = ""
                        .BorderStyle = fmBorderStyleSingle
                        .BorderColor = LineColor    'vbScrollBars
                        .Width = msngIndent
                        .Height = mcPtPxl * [yourscalefactorgoeshere]
'.....................
                    With moRootHolder.VLine
                        .ZOrder 1
                        .Width = mcPtPxl * [yourscalefactorgoeshere]


. Note that making this wider will cause the lines to becone rectangles so you might want to change the label backcolor property too.

 


Comment by: Scott Braley (2/2/2017 6:11:49 AM)

I just want to say that this tool is amazing. I do have one concern though before I try implementing this in my project. I've always understood that MS Access limits you to a certain number of controls that can be added/deleted over the lifetime of a form. Is that not the case? Thanks for your time.

 


Comment by: Jan Karel Pieterse (2/2/2017 10:50:33 AM)

Hi Scott,

So far we haven't had any complaints in that direction so I think we can safely say it works just fine.

Should you run into a problem anyway, we do have a pro version which uses less controls to display the tree. You can register for the beta program to try it.

 


Comment by: Marcos Ximenes Carvalho (2/2/2017 10:23:31 PM)

Mr. Pieterse

I live in Brazil.
What i need to do to change the icons?

Thank you, very much

 


Comment by: Jan Karel Pieterse (2/3/2017 11:51:58 AM)

Hi Marcos,

You mean how you should add your own icons or how to use different ones of the icons we already provided?

To use different icons, simply remove the ones we provided from the icon frame and insert new image controls into the frame and make sure their content is the way you like it. You can also of course replace the image sin the existing image controls. Make sure you name the picture controls properly as that is what the class uses to select the right icon.

 


Comment by: Marcos Ximenes Carvalho (2/5/2017 7:39:45 PM)

Dear Mr
Thank you very much for the material sent, but I could not put my icons in trieeview.
Can you send an example?

Thanks
Marcos

 


Comment by: Peter Thornton (2/6/2017 3:40:50 PM)

Hi Marcos,
If you are using Access it can be difficult to add or edit the image controls. One way is to add and edit the image controls in Excel (on a userform), apply the 'picture' and simply copy them into the Frame in Access. You can copy/paste multiple image controls in one go.

Also see this comment above
Julián Rodríguez (1/15/2017 7:52:01 PM)
I haven't tried Julián's suggestion but it looks interesting.

In the 'pro' (currently beta) images can be stored as 'blobs' in a table.

 


Comment by: Arran Spencer (2/7/2017 7:47:30 PM)

Hi MVPs,

You mention that it is important to make sure that all instances of the classes are destroyed. I would like to use the classcounts code you have included in the demo to check that all instances are in fact destroyed in my implementation (I am using the queryclose event as per the demo). So I have some questions...

How do I set debugmode to 1 to use classcounts?
What happens if all instances are not destroyed?

PS. The tree view tool is amazing, far better than I expected, and easy to implement (and although some of the code is hard to understand for a learner like me, I have started to improve my own VBA skills by studying your code so thanks for that too!)

Thanks
Arran

 


Comment by: Jan Karel Pieterse (2/8/2017 10:56:23 AM)

Hi Arran,

You can enable the class termination checks by adding a conditional compile argument to the project properties (tools, VBA project properties, add DebugMode = 1 to the last textbox of the dialog)

 


Comment by: Alexandre (3/7/2017 11:46:48 AM)

Hi,

    I am using your ClsTreeView and ClsNode classes to have a treeview on my form. However, when I close the form and it gets unloaded, I get an "automation error" or "Object required error". I saw that some sheet objects, which are normally not empty, get emptied. If I remove the line calling this form, my code works. Have you ever seen this issue? Could you please help?
    I can send you screenshots via email.

Regards,

Alexandre

 


Comment by: Jan Karel Pieterse (3/8/2017 9:50:46 AM)

Hi Alexandre,

Sure, send your screenshots (perhaps your file as well, so we can trouble-shoot?).

 


Comment by: Kaman (3/17/2017 4:12:04 PM)

Hey guys, I am having a problem trying to get the mcTree_KeyDown, mcTree_Click events to fire, am I doing something glaringly obvious wrong? It's working in your frmDemo in the same database.

Cheers

 


Comment by: Peter Thornton (3/19/2017 1:27:32 PM)

Hi Kaman,
Did you declare a withevents reference to the treeview, eg

Private WithEvents mcTree As clsTreeview

Select mcTree in the top middle dropdown and event stubs in the top right dropdown

 


Comment by: Daniel Dumitru (3/27/2017 3:30:54 PM)

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
Set cNode = mcTree.ActiveNode

 


Comment by: Jan Karel Pieterse (3/27/2017 3:45:36 PM)

Hi Daniel,

I assume you have this in the declaration section of the userform's code module:

Private WithEvents mcTree As clsTreeView


And this somewhere where the form is initialised:

Set mcTree = New clsTreeView

 


Comment by: Daniel Dumitru (3/27/2017 4:35:26 PM)

Hi Jan,

Yes I do. I have the
Private WithEvents mcTree As clsTreeView
, in a form, and in a different module I have a public function:
Function addRoot(frmI As Frame, targetFolder As String)

Set mcTree = New clsTreeView
With mcTree
        'SET TREE = FRAME
        Set .TreeControl = frmI


Is it a problem that the function where the clsTreeView is not in the form?

 


Comment by: Daniel Dumitru (3/27/2017 4:40:02 PM)

It does have to be defined in the Form, now it's working.
Thank you!
Set mcTree = New clsTreeView
- should be defined in the form, not in a separate module.

 


Comment by: Jan Karel Pieterse (3/27/2017 4:50:54 PM)

Hi Daniel,

Indeed in the form, as that is where form code belongs :-)

 


Comment by: Filia (6/15/2017 3:34:32 PM)

Hi, I cannot import Form_subTreeView into my project (Access 2016). Message: The module name 'Form_subTreeView' is invalid". Any idea why?

 


Comment by: Jan Karel Pieterse (6/15/2017 3:57:05 PM)

Hi Filia,

I'm afraid I don't know!

 


Comment by: Martin (6/16/2017 2:58:20 AM)

You mention:

Access 2003
Access 2010 (32 bit)
Access 2010 (64 bit)

What about 2007, 2013 & 2016?

 


Comment by: Jan Karel Pieterse (6/16/2017 3:03:02 PM)

Hi Martin,

It should work on those versions as well, the ones mentioned are the ones we tested on.

 


Comment by: Peter Thornton (6/22/2017 9:38:07 AM)

@ Filia,
I've just tried the following with Access 2016 64 bit:
- Open a newly downloaded treeview demo v025, all worked fine.
- Drag the subForm from the treeview demo to a new project, no problem.

@ Martin,
>What about 2007, 2013 & 2016?
I can confirm working in Access 2007, 2013-32, and 2016-64.
I believe others have tested in 2013-64 and 2016-32.

The current version 025 was made with 2003. We held back from updating to 026 as we can no longer compile in a way that will work for 2003. However as time has gone by we may update the 025 mdb to 026 as an accdb.

 


Comment by: Filia (6/29/2017 4:08:00 PM)

Well, I got it to work through trial and error in Access 2016. The only problem remaining is that when text in the control extends under the vertical scrollbar and I drag the horizontal scrollbar to the right to view it, the vertical scrollbar doesn't refresh so I get 2 vertical scrollbars (original position on load and new position resulting from dragging).
When I populate the control, my last two commands are Refresh and then ExpandToLevel(x). Any help to solve this will be appreciated.

 


Comment by: Peter Thornton (7/1/2017 3:54:01 PM)

Hi Filia,
I don't know how two vertical scrollbars can be displayed at the same time because normally only one can exist.
However moving the horizontal scrollbar will not do anything to help show nodes 'below' the vertical scrollbar.

If you want you can send me what you have off-line and I'll take a look.

 


Comment by: Julian (8/6/2017 1:10:54 PM)

Hi, there. I have implemented your wonderful treeview in my aplication and so far I was running it on Windows 7 or higher + Officce 2007. However I understood that your treview was also ready for access 2003. Well as some users of my aplication still run it on XP and access 2003 I downgraded my aplication to a version for them and I have run into an strange problem. Whenever at the begining when it has to pass the trusted property it fails to do so and tells me it has not found that object (?¿) and it does not create the uftreeview userform. I really do not understand very much about this. I tied tou use your demo compiled by the way in access 2003 and it just crash ass soon as you enter in the mdb file.
Do you think there is something I am doing wrong? It is not compatible?

Thanks

 


Comment by: Peter Thornton (8/7/2017 10:34:07 AM)

Hi Julian,

I have similar problems as you describe with Access 2003. Not sure but possibly because my 2003 is now in Windows 10 with multiple later Office versions. These all 'share' the VBA run-times, these have been updated with various security changes which maybe cause problems in the now unsupported 2003.

If your Access 2003 is in a similar setup could you ask one of your XP/2003 users to download a fresh copy of the Access 025 demo and try it.

 


Comment by: Julian (8/18/2017 7:16:15 PM)

Hi Peter, thanks for the suggestion. I will do that and see what happens.

Thanks so much

 


Comment by: TungSon Nguyen (8/20/2017 4:41:39 PM)

Hi JKP,
I am trying to use your treeview control for my project.
there are two point I would like your help to make me clear:
1. I could not get the tooltip option, especially when my node text is longer then the width of the treeview-container frame.
2. Is this possible to get the FullPath of a node with the node caption instead of the node key?

I am looking forward to receiving your reply.
Thank you in advance and Best regards.

 


Comment by: Jan Karel Pieterse (8/21/2017 8:21:05 AM)

Hi TungSon,

In class clsNode, find the routine called AddChild.
Search for: ".Caption = vCaption"
Below that line, add:
        .ControlTipText = vCaption

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].