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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

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 > How To Use

An MSForms (all VBA) treeview; How to use

This page outlines the minimum steps needed to add this treeview control to your own Excel or Word VBA project. For Access the instructions are different, those can be found in the Access download.

The container control

Open the designer window of your userform and add a frame. This is where the treeview will be built. We recommend these properties (which are of course optional):

 Recommended properties for the container frame
Recommended properties for the container frame

Your userform would look like this:

 The container frame on your userform
The container frame on your userform

If you want images in your treeview, you'll have to add another frame (we called it frmImageBox) and then add Image controls within that frame. Set the Visible property of the frame to false to avoid it showing up on your userform. Like so:

 The images frame on your userform
The images frame on your userform

If you want to add images, make sure you name the new image controls properly, as it is the name of the imagecontrol you need to pass to the cNode class to get that image displayed. An easy way to get the images is to copy the entire frame with images from our userform to yours.

Class modules

Copy the class modules clsNode and clsTreeView to your project (you can simply drag them to your project). Your project (if you started with an empty workbook) would look like this:

 Project with class modules in place

The project with class modules in place

So far, things have been simple. The next parts are a bit more compex, but nothing too hard!

Code behind your userform

For the treeview to work, your userform needs some code, in particular these elements are necessary:

That's it!

Variable declaration

Add this code to the declaration section of your userform:

'Add this to your form's declaration section
Private WithEvents mcTree As clsTreeView

That's it! No more variables needed!

Of course the name of the variable mcTree is totally up to you.

If you need another (independent) treeview on your form, simply add another frame to hold it and an additional variable in the forms declaration section (of course you name it differently than the other one) like the one shown here.

Initialisation

In the intialisation routine of your form, you need code that adds nodes to the tree and when you're done adding nodes, you need to set some properties of the treeview. Then you'll want the treeview to be displayed.

Adding an instance of the treeview to your form

Adding the instance is easy:

    'Instantiate a new instance of the treeview class and set a module level variable to hold it:
    Set mcTree = New clsTreeView

Now tell the tree class instance which frame is its container:

    With mcTree
        'The VBA treeview needs a container Frame control on the userform.
        'Just draw a frame on your form where the treeview should appear
        'Make sure the frame has no border and no caption
        'Set it to show both scroll bars. (Keepscrollbarsvisible to both)
        'Set it to the special effect "Sunken"
        'Note that node label formats such as back/fore colors and font adopt
        'the frame container's properties, so change if/as required.
        '(you can copy the frame named frmDemo from this userform )
        
        'Then pass this frame to the TreeControl of the treeview class

        Set .TreeControl = Me.frmDemo
        'Title for message boxes:
        .AppName = Me.AppName

Note that most of the code listed below is within the With mcTree ... End With structure.
 

Setting initial look

You'll want control over the look and feel of your treeview, here is some example code (this code comes immediately below the code sample show above):

        'Set some properties
        .CheckBoxes = True
        .RootButton = True
        .LabelEdit = 0 'default is 0 can be edited (like LabelEditConstants tvwAutomatic/tvwManual)
        .Indentation = 20 * 0.75 'defaults to 11.25
        .NodeHeight = 16 * 0.75 'defaults to 12
        .ShowLines = True
        'If your form has icons in an iconframe (called frmImageBox),
        'you could use icons for the expand and collapse buttons:

        Call .ExpanderImage(Me.frmImageBox.Controls("Win7Minus").Picture, Me.frmImageBox.Controls("Win7Plus1").Picture)

If your treeview needs to show images, add a frame control with Image controls inside. Lets call it frmImageBox. This is how you tell the class where the images are:

        Set .Images = Me.frmImageBox

That is just about all the plumbing you need to get started.

Adding nodes

First of all, a couple of variables are needed to add nodes:

'The root of the tree
Dim cRoot As clsNode
'A node
Dim cNode As clsNode
'An extra variable should you need to remember a certain node
Dim cExtraNode As clsNode

Next we'll start by building the rootnode:

        ' add a Root node with main and expanded icons and make it bold
        Set cRoot = .AddRoot("Root", "Root Node", "FolderClosed", "FolderOpen")
        cRoot.Bold = True

Note that the tree can have more than one rootnode, there is a special RootNodes collection to which you automatically add new roots by calling the AddRoot method.

As you can see, we assume there are two icons in the image frame called FolderClosed and FolderOpen respectively.

Now we want to add children to the root. This is the code from our demo form:

        'Add branches with child nodes to the root:
        'Keys are optional but if using them they must be unique,
        'attempting to add a node with a duplicate key will cause a runtime error.
        '(below we will include unique keys with all the nodes)
        Set cNode = cRoot.AddChild("1", "1 A", "FLGNETH")
        cNode.Bold = True
        
        'Add a 2nd branch to the root:
        Set cNode = cRoot.AddChild("2", "2 B", "FLGSWED")
        cNode.Bold = True
        'If you want to add more child branches to a branch later on, use a variable to store the branch.
        Set cExtraNode = cNode.AddChild("2.1", "2.1  level 2", "NOTE03", "NOTE04")  ' include an expanded icon
        cExtraNode.Expanded = False   ' this node will initially be collapsed,
                                      ' its child node controls will be created when expanded


        'To add a branches to a branch, make sure you set a variable to its 'main' or parent branch when created
        'Then use the Branch's AddChild method, here to create multiple levels

        Set cNode = cNode.AddChild("2.2", "2.2  level 2", "NOTE03", "NOTE04")    ' include an expanded icon
        Set cNode = cNode.AddChild("2.2.1", "2.2.1  level 3", "OpenBook")
        Set cNode = cNode.AddChild("2.2.1.1", "2.2.1.1  level 4", "Scroll")
        Set cNode = cNode.AddChild("2.2.1.1.1 ", "2.2.1.1.1   level 5", "GreenTick")

        'Now add another branch to the branch we stored earlier
        cExtraNode.AddChild "2.1.1", "2.1.1  level 3", "OpenBook"

        'Add a 3rd branch to the root, with a child node
        Set cNode = cRoot.AddChild("3", "3 C", "FLGUK")
        cNode.Bold = True
        cNode.AddChild "3.1", "3.1  level 2", "Scroll"

        ' and add a 4th branch to the root
        Set cNode = cRoot.AddChild("4", "4 D", "FLGUSA02")
        cNode.Bold = True
        cNode.Caption = "4 D  +" & mlCntChildren

        ' add a bunch of child nodes to the 4th branch
        For i = 1 To mlCntChildren  ' 15
            Set cExtraNode = cNode.AddChild("4." & i, "  4.1 " & Right$("000" & i, 4), "Scroll")
            '  add some alternate row colour formats
            If i Mod 2 Then
                cExtraNode.BackColor = RGB(255, 255, 220) ' pale yellow
                cExtraNode.ForeColor = RGB(180, 0, 0)     ' dark red font
            End If
        Next

Display the tree

Displaying the tree is as simple as calling one method:

    'Fill the tree
    .Refresh

Termination

When the form goes out of scope (i.e. out of memory) you need to remove the treeview from memory:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Make sure all objects are destroyed
    If Not mcTree Is Nothing Then
        mcTree.TerminateTree
    End If
End Sub

Feedback

We've worked hard to create a reliable and performant treeview. If you encounter bugs, please let us know so we can work on them. Better yet: if you have fixed a bug you found, send us your updated code so we can add the fixes you made.

In any case, comments (and compliments) are welcome!

 


 


Comments

All comments about this page:


Comment by: Cyril (2/23/2013 6:04:03 PM)

Thank you very much for your amazing work.

I'm actually working on a project with lot of xml datastorage files, I will improve your code on monday and send you feedback if necessary.

See you
Cyril

 


Comment by: Mike (5/2/2013 8:13:30 AM)

Thank you for your work!

In a Access-Database the ScrollBars are visible but doesn't work :-(

Thank you very much for your support.

Bye, Mike

 


Comment by: Jan Karel Pieterse (5/2/2013 9:42:53 AM)

Hi Mike,

You're welcome!

We're working on a new build for Access with loads of fixes, so please be patient with us :-)

 


Comment by: Joost Halenbeek (6/2/2013 8:13:09 PM)

Hello Jan Karel,
(Hallo, ben je net als ik Nederlands?)
Absolutely loving the first impression of your TreeView, great work.
I've been struggling with mscomctl TreeViews for some time now.
I can get them working, but they always have new surprises, for instance try placing them on a multipage control...argh
Your TreeView has passed the test so far, happy.
My question: I'm trying for a "flat" layout of my userforms (yes, a follower or fashion...) I managed to flatten the frame that the treeview is associated with, by setting its properties via the .TreeControl property, just before doing a .Refresh. (Is that a sound method?) Is it also possible to make the scrollbars flat? I mean like the Windows 8 style scrollbars?

Best Regards, Vriendelijke Groet,
Joost

 


Comment by: Jan Karel Pieterse (6/2/2013 8:33:40 PM)

Hi Joost,

Yes, I'm Dutch (as you can probably tell from my home page here :-) ).

I don't think you can change the appearance of the scrollbars, as those are just the built-in ones the frame control shows.

But then again, I've seen people do stuff with userforms I'd never have held possible, so who knows...

 


Comment by: Billy Hamilton (6/4/2013 9:10:17 PM)

The download page mentions build 024, but when I take the link, I get a zip file with build 023.

 


Comment by: Raymond Rooks (7/18/2013 2:25:20 AM)

This looks like a really great tool. I am quite impressed with it.

I do have a question about the tri-state checkboxes, though. If you set them programatically, parent checkboxes aren't properly set grey (instead of black) if only part of the tree is selected. It must have to do with mctlCheckBox not being defined since the user didn't actually click on a box.

Is this working as intended? I am loading saved values onto a form, and it would be great to see in the parent if all values were selected, or just some...up to the root.

 


Comment by: Sami Souki (7/18/2013 1:50:40 PM)

regarding the treeview control... how can i use the doubleClick event on nodes...

 


Comment by: Michael Bleau (7/18/2013 4:00:36 PM)

Jan,

Thank you for this. A question:

I am using Access 2010 and am looking for a frame container control. The closest I have found is something called Unbound Object Frame. It has no scroll bars however. On the other hand, your demo seems to use a subform control. Am I on the right track?

Any help is welcome/

Thanks,

Mike

 


Comment by: D. Spirydzionak (7/31/2013 11:18:03 AM)

Hi guys,

Great work!
TreeView works fine for me (using it in Excel) and helps me a lot.
Thank you very much!

 


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

@Michael:

You should be able to find how how to do this using the MSAccess download on the previous page.

@Sami: I seem to recall I have given an example in one of the comments ione one of these pages. Make sure you click the show all comments link to see them all.

@Raymond: something to look at. I'll put it on the list...

 


Comment by: Daniel (9/4/2013 6:29:20 PM)

Hi Jan

Thanks so much for this treeview... mscomclt make me crazy because my app don't work in others pcs ...

just one question: ¿who i do for select (highligth it) a last node inserted by code?

sorry because my english

Regards
Daniel from Colombia!!!

 


Comment by: Jan Karel Pieterse (9/4/2013 8:12:13 PM)

Hi Daniel,

Your English is just fine :-)
If you still have an object variable pointing to that node, like nNode, then you can use code like this to ensure that that node becomes the active node:

Set mcTree.ActiveNode = cNode

 


Comment by: Daniel (9/4/2013 9:56:22 PM)

sorry a have a mistake

forget the last comment

thanks for you help!!

Daniel!!!

 


Comment by: Antonio (9/17/2013 2:20:55 PM)

Hi.
Nice control and code. Many thanks for your work.

I want to apologize if my English is not very correct. It's not my mother languange.

I've changed a little, enhancing the Double-Click of a Node and adding a new property "Children", to get te children nodes count.

If the node doesn't allow editions, then a Double-Click performs as Expander Click


Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' PT a node label has been double-clicked, enter edit-mode if manual editing is enabled
    Dim bDummy As Boolean

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

End Sub



Public Property Get Children() As Integer
Children = 0
If Not Me.ChildNodes Is Nothing Then _
    Children = mcolChildNodes.Count
End Property


Best regards.

Antonio.
Madrid (Spain)

 


Comment by: Jan Karel Pieterse (9/17/2013 4:00:21 PM)

Hi Antonio,

Thank you for your comments!

Using your Children property is simpler than using ChildNodes.Count, because the latter needs an extra check to see whether the ChildNodes collection isn't nothing.

HOwever, this can be prevented by changing this (in clsNode):

Public Property Get ChildNodes() As Collection
    Set ChildNodes = mcolChildNodes
End Property


to:

Public Property Get ChildNodes() As Collection
    If mcolChildNodes Is Nothing Then
        Set mcolChildNodes = New Collection
    End If
    Set ChildNodes = mcolChildNodes
End Property

 


Comment by: Graham (10/9/2013 6:06:29 PM)

Just wanted to say thank you for the development of this, it's a brilliant bit of kit.

I was tearing my hair out trying to get a common control treeview working on all company pcs with very little success, so this has been a life-saver.

 


Comment by: Jan Karel Pieterse (10/10/2013 10:14:50 AM)

Hi Graham,

Thanks for letting us know. Always nice to hear people appreciate our hard work.

We've put a lot of effort in making it look good, versatile and easy to implement.

 


Comment by: Ray Longmoor (10/16/2013 9:41:09 PM)

I have started to use your treeview but I have run in to a problem

I have embedded the tree view in a tab control, the tab control has the tree view in a sub form

I use access 2010 adp project, The tree view works fine every time when I find records and it populates the tree from the records returned, the problem comes when I don't find records.

e.g. Server filter by form and set the criteria to Find no Records and return a blank form.

Now set Server filter by form again and modify the criteria so that the form finds records, now switch to the tab with the treevew control and all I see is the flags and check boxes.

Any help would be appreciated

Thanks

Ray

 


Comment by: Ben (10/17/2013 2:11:11 PM)

Ray -

It sounds like the treeview was not completely torn down and rebuilt.

Have you looked at the demo and observed how it tears down and rebuild when changing between different demo?

The other possibility is that you need to ensure that events responsible for doing this is actually firing.

If you are still not sure how to fix this or need further help, consider contacting accessexperts.com.

Best of luck!

 


Comment by: Ray Longmoor (10/17/2013 9:24:35 PM)

Ben thanks for the reply

I have taken a look at your response but I am still not able to get the treeview to repopulate after no records have been found, the error I receive is
-2147221494 Refresh: 'TreeControl' frame is not referenced

For the life of me I can no see what is wrong

Any help would be appreciated

Thanks

Regards Ray

 


Comment by: Paulo (11/6/2013 12:44:25 PM)

Hi JKP,

I am very grateful for you have made available this control. He is excellent and will help me a lot!

I'm getting a compile error with the message: "Compile error. Constant expression required"..

The build is broken, but when running the form I have no problem.

The error occurs on line #438 of module clsNode. The statement is:

If ndOrder <> ndAscending ndOrder = -1 Then 'descending

I'm using build 025, MS Excel 2010 32-bit on Windows 7 Pro 64-bit.

Many thanks and please excuse me for my bad english.

 


Comment by: Jan Karel Pieterse (11/6/2013 1:22:04 PM)

Hi Paulo,

Odd error. I doubt this is caused by the treeview itself.

Look in your VBAProject's Tools, References, are any marked as missing?

 


Comment by: Paulo (11/6/2013 3:30:53 PM)

Hi Jan,

No reference missing.

This error occurs in the Sort function. I'm not using this function, but I would not remove it from the module clsNode, what I did was add the parameter "Optional ByVal ndAscending = 1" in this way:


Public Function Sort (Optional ByVal ndOrder The ndSortOrder ndAscending =, _
                     Optional ByVal ndCompare The ndCompareMethod ndTextCompare =, _
                     Optional ByVal ndAscending = 1) As Boolean


And now everything is good. :)

Many thanks!

 


Comment by: Jan Karel Pieterse (11/6/2013 5:44:36 PM)

Hi Paulo,

Odd, because that ndAscending constant (in fact an enum) should be declared at the top of the class (it is in the Excel download as far as I can see):

Public Enum ndSortOrder
    ndAscending = 1
    ndDescending = 2
End Enum

 


Comment by: Paulo (11/6/2013 6:15:58 PM)

Yes Jan,

And in my code is declared. But did some testing and I think I found the answer: initially this form I'm working on had the standard TreeView, I removed this and created a new object based on your TreeView, but I had not removed the reference (Microsoft Windows Common Controls 6.0 (SP6)).

I undid my change in Sort function, removed the reference, compiled again and received no error message.

Insistently, I enabled again the reference, compiled again ... but this time did not get any error message!

Finally, have disabled the reference. :)

I can only believe it was a conflict with reference to the MS TreeView.

Again, thank you for your help, patience and excellent resource available to us.

Paulo S. Quateli
São Paulo - BR

 


Comment by: Jan Karel Pieterse (11/6/2013 7:17:50 PM)

hi Paulo,

Excellent glad you could solve the issue.

 


Comment by: Sijpie (11/7/2013 9:22:58 PM)

Jan Karel,
My form containing the treeView has other controls (text & list box) where the user can update details of items shown in the tree. After the update I want to activate the node that has been updated, so I use
Set mcTree.ActiveNode cMyNode

This puts a thin grey border around the node, but not the full select colour. Somewhere I recall to have seen that the full colour only appears if the container control for the tree has focus, but I cvan't find that comment anymore. But if i add
frTreeControl.SetFocus

that doesn't make any difference.

By the way I like the results of the treeview, looks great. Once I have finished this part of the project some beer money will be winging its way...

 


Comment by: Jan Karel Pieterse (11/9/2013 11:01:21 AM)

Hi Sijpie,

The correct syntax for setting the activenode is:

Set mcTree.ActiveNode = cMyNode '(you omitted the equal sign)


In my implementation it suffices to use setfocus to achieve just what you describe, so I wonder whether something isn't quite right in yours?

 


Comment by: Sijpie (11/11/2013 9:38:58 AM)

Thank Jan Karel,
I had confused myself here: the changes are made by the user entering data or clicking buttons in another part of the form, and so the other textbox has the focus, and I end up with a grey border as is correct. so yes the functions work as they should, just my brain doesn't...

cheers, Jaap

 


Comment by: Jeremiah (11/13/2013 5:08:18 PM)

Ok, maybe I missed it in the coding. Is there a way to make the nodes click to open a form. Like Parent Node steps into a child node. The child node has 7 forms referenced. You click that and it would open the form. I know there is a way to build a dblclick function in tree view, but cannot find any goof references.

 


Comment by: Jan Karel Pieterse (11/14/2013 6:41:31 AM)

Hi Jeremiah,

If you go into the clsNode class you can see how we implemented the click event. Specifically, the routine in question contains a call to a method in clsTree:

moTree.NodeClick Control, Me


In clsTree this method is structured like this:
Friend Sub NodeClick(ByRef oCtl As MSForms.Control, ByRef cNode As clsNode)


Within that method we call:

RaiseEvent Click(cNode)


Which has been declared at the top of that class:

Event Click(cNode As clsNode)     'Node clcick event


After adding your own new event with these steps you can then find your new event in the form's event dropdown by first selecting mcTree in the left dropdown at the top of your code window and then use the right-hand dropdown.

 


Comment by: Lord Vorp (11/28/2013 4:04:47 AM)

I'm working with your TreeView to replace mscomctl.ocx (w0000t!!). The existing mscomctl code accepts a double-click on the root node caption/label, to expand/collapse the node (I'd like to duplicate this if I could, but it's not that big of a deal since the arrows work, too!). But it ALSO intercepts a double-click on a non-root node to equate to "choose THIS one, click SELECT and close the form"

Unfortunately, I'm having a devil of a time capturing the DblClick() event on the form.

Looking into the code for clsNode, I found mctlControl_DblClick() has behavior that IF EnableLabelEdit is true then switch into edit mode.

My form does NOT allow Editing the nodes, and this seems to be the correct location to intercept or, preferably, raise the event to the frame/form, but my knowledge of VBA events is insufficient, so far, to implement this.

If I have the correct event handler, how should I modify mctlControl_DblClick() to raise DblClick to the frame or form? and if it's the wrong spot, where should I go?

Thanks in advance!

 


Comment by: Jan Karel Pieterse (11/28/2013 7:41:48 AM)

Hi Lord Vop,

OK, here are the steps.

1. In clsNode, replace the existing mctlControl_DblClick with:

Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim bCancel As Boolean
    bCancel = moTree.RaiseDoubleClick(Me)
    Cancel = bCancel
End Sub


in clsTreeView, at the top, add:

Event DblClick(cNode As clsNode, Cancel As Boolean)


And somewhere between the other routines, add:

Friend Function RaiseDoubleClick(cNode As clsNode) As Boolean
    Dim Cancel As Boolean
    RaiseEvent DblClick(cNode, Cancel)
    RaiseDoubleClick = Cancel
End Function


Now you can add the dblclick event to your userform:

Private Sub mcTree_DblClick(cNode As clsNode, Cancel As Boolean)
    MsgBox cNode.Caption
End Sub




 


Comment by: Maik (11/28/2013 3:08:47 PM)

Hi JKP,

I have two questions.
Is it possible to create the treeview without root node?

What is the best way to add a new record to the treeview and show the new node highlited?
There are two scenarios:
1) New node level 2 of en existing node level 1
2) New node level 2 with a new node level 1

With MSCOMCTL.OCX I delete the treeview and build it again.
I could do this also with your treeview, but how could I find the last record? On form IDs for level 1 and level 2 are available.

Kind regards

 


Comment by: Jan Karel Pieterse (11/28/2013 3:43:22 PM)

Hi Maik,

If you look at the project in the Exceldownload, the demo form has an AddChild button which adds a childnode to the currently selected node in the tree.
I expect that should give you enough information to work out to do what you asked.

 


Comment by: Lord Vorp (12/3/2013 3:50:25 AM)

That was just what I needed to proceed! Now I have a handler for mcTree_DblClick that fires on double-click.

However, I updated mctlControl_DblClick() in clsNode.cls to preserves the existing behavior

if edit is enabled
start editmode
else
if the node has children
    toggle expanded
else
    raise the event to the userform.
end if
end if


Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' PT a node label has been double-clicked, enter edit-mode if manual editing is enabled
    Dim bDummy As Boolean

    If moTree.EnableLabelEdit(bDummy) Then

        moTree.EditMode(Me) = True
        EditBox bEnterEdit:=True

    Else

        ' IF there are child nodes, toggle expanded state
        If Not mcolChildNodes Is Nothing Then
            If mcolChildNodes.Count > 0 Then _
             mctlExpander_Click
        Else

            'Raise DblClick() to the form, and let the user decide!
            Cancel = moTree.RaiseDoubleClick(Me)

        End If

    End If

End Sub


 


Comment by: robert (12/28/2013 3:27:22 PM)

Thank you very much for implementing and providing this helpful replacement of the treeview.

Here are my experiences / modifications:
- clsNode.Tag: modified setter / getter to take values of type object too (needed to store own node informations)
- propagation of the MouseDown event of clsNode to clsTreeview and fireing the new created event MouseDown at the clsTreeview (needed for usage of context menus)

 


Comment by: Abdul Rauf Alipuddin (12/31/2013 2:44:43 AM)

Hi,

I have downloaded your treeview demo and try it as part of my application. It is son very good tool! congratulation!. However, when I edit label beyond the frame - the text is not visible until I hit the enter and scroll right. Is it possible, while I am typing the text, when I reach the right border of the frame - it will be automatically go to the next line, not the next sibling or node.

Please advise.

Regard,
ARA

 


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

Hi Abdul,

Thanks for your feedback, we'll add it to the list!

 


Comment by: Jan Karel Pieterse (1/2/2014 10:45:57 AM)

Build 026 contains the changes needed to allow editing of long texts while the textbox remains in view.

 


Comment by: Tony Matyas (1/10/2014 8:01:09 PM)

Thank you for the brilliant piece of work!
My question: I would like to know how to show the UserForm in a non modal way (vbModeless), in order to allow editing or movements in the Excel sheets application at the same time as the active UserForm is shown.

 


Comment by: Jan Karel Pieterse (1/10/2014 8:14:02 PM)

Hi Tony,

Since you already seem to know what argument to use (vbModeless) I am wondering what your problem is exactly?

This shows a userform modeless:

Dim frmForm As Userform1
Set frmForm = New Userform1
frmForm.Show vbModeless

 


Comment by: Drew Perkins (1/16/2014 7:35:54 AM)

Excellent work! and thank you!

It would be nice to add a saving feature that would append a new tree to a table.

 


Comment by: Jan Karel Pieterse (1/16/2014 9:05:42 AM)

Hi Drew,

Thanks for the suggestion.

I do think however that that does go a bit beyond what our aim was: replacing the common controls treeview with an all-VBA one. The common controls treeview has no such option.

 


Comment by: Geoffrey Grinton (1/22/2014 5:07:16 AM)

Thanks for your work on this. It looks like a good way to go. However, a key use of the Treeview control in my applications relies on the use of drag-and-drop functionality. From my initial testing this does not appear to be implemented. Is that the case, or am I missing something?

 


Comment by: Jan Karel Pieterse (1/22/2014 8:41:40 AM)

Hi Geoffrey,

You're right, drag and drop isn't in the demo. However, Peter Thornton has been working on an implementation of D&D.

I'll forward your question to him.

 


Comment by: Jarn (2/11/2014 6:42:55 PM)

Really is great work! Works just as described. Haven't found any bugs. Still working through the features (Thank you!)

 


Comment by: Jan Karel Pieterse (2/12/2014 10:51:03 AM)

Hi Jarn,

Thank you!

 


Comment by: Diego F. Pereira-Perdomo (3/4/2014 8:23:39 AM)

This was something desperately needed for Access.
I'm just starting to implement it but I can say it's a beautiful work.
Many many thanks.
Diego

 


Comment by: Jan Karel Pieterse (3/4/2014 12:41:38 PM)

Hi Diego,

You're welcome!
If your solution is ready, perhaps you can send us a screenshot showing your treeview implementation? So we can add it to the page with examples:
http://www.jkp-ads.com/Articles/treeview02.asp

 


Comment by: Diego F. Pereira-Perdomo (3/5/2014 3:44:10 AM)

Thank you Jan!
I sent an example and a screenshot to your email.
Regards,
Diego

 


Comment by: Jan Karel Pieterse (3/5/2014 10:39:01 AM)

Hi Diego,

Thanks. The sample has been posted.

 


Comment by: Diego F. Pereira-Perdomo (3/15/2014 4:10:20 PM)

Hi Jan,

I'm using MsAccess 2013 x64.

Everything was working properly but now when trying to load the subTreeView form I receive an error:

Run-time error '-2147221005(800401f3)': Invalid class string
If I debug the code this line is highlighted

Set mfrTreeControl = mUF.Controls.Add("Forms.Frame.1", "frTreeControl", True)


The reason I'm opening directly the subTreeView form is for isolating the error, since in the form I use the treeview other errors raised and could be missleading.

I tried removing the subTreeView and the ufTreeView but the error persists.

What can be happening and how can I solve it?

Thanks,

Diego

 


Comment by: Mark Drayton (5/12/2014 2:31:28 PM)

Hi,

I have to say this has been extremely useful - thanks for the effort!

I have a question about how to dynamically create the tree view from a worksheet containing parent-child format, for example: Col A - Parent | Col B - Child?

If I figure it out, i'll post up and share.

Thanks,
Mark

 


Comment by: Jan Karel Pieterse (5/12/2014 5:34:02 PM)

Hi Mark,

It should not be very hard, something like:


Dim oCell As Range
Dim cNode As clsNode
Dim cChildNode As clsNode
Dim vPrev As Variant
vPrev = Range("A1").Value
With mcTree
    Set cRoot = .AddRoot("Root", "Root Node", "", "")
    For Each oCell In Range("A1:A100")
        If vPrev <> oCell.Value Then
            Set cChildNode = cNode.AddChild(oCell.Value, oCell.Value, "")
        Else
            Set cNode = cRoot.AddChild(oCell.Value, oCell.Value, "")
        End If
    Next
End With

 


Comment by: Mark Drayton (5/12/2014 10:30:19 PM)

Hi Jan,

Thanks for you speedy response, the table I'm trying to convert is in this format (split by a pipe):
PARENT|CHILD
ACCOUNT|TOTAL_ACCOUNTS
TOTAL_ACCOUNTS|ALL_ACCOUNTS
TOTAL_ACCOUNTS|NO_ACCOUNTS
ALL_ACCOUNTS|COST
ALL_ACCOUNTS|REVENUE
COST|FIXED
COST|VARIABLE
REVENUE|SALES
REVENUE|NON SALES

I'm struggling with the logic of how to capture the parent (sorry I'm new to this sort of thing in relation to treeviews).

Thanks,
Mark

 


Comment by: Jan Karel Pieterse (5/13/2014 9:17:22 AM)

Hi Mark,

This seems to do the trick:

Private Sub CommandButton1_Click()
    Dim cNode As clsNode
    Dim cParent As clsNode
    Dim cRoot As clsNode
    Dim cChild As clsNode
    Dim vData As Variant
    Dim lCt As Long
    Dim bFoundParent As Boolean
    vData = ActiveSheet.UsedRange.Value
    Set mcTree = New clsTreeView
    With mcTree
        Set .TreeControl = Me.frTreeControl
        Call .NodesClear
        .AppName = Me.AppName
        Set cRoot = .AddRoot("Root", "Root")
        For lCt = 2 To UBound(vData, 1)
            bFoundParent = False
            'Check if parent node exists
            For Each cNode In .Nodes
                If cNode.Key = CStr(vData(lCt, 1)) Then
                    bFoundParent = True
                    Set cParent = cNode
                    Exit For
                End If
            Next
            If bFoundParent Then
                Set cChild = cParent.AddChild(CStr(vData(lCt, 2)), CStr(vData(lCt, 2)))
            Else
                Set cNode = .RootNodes(1).AddChild(CStr(vData(lCt, 1)), CStr(vData(lCt, 1)))
                Set cNode = cNode.AddChild(CStr(vData(lCt, 2)), CStr(vData(lCt, 2)))
            End If
        Next
        .Refresh
    End With
End Sub

 


Comment by: Mark Drayton (5/13/2014 11:18:11 AM)

Awesome - Thank you very much!

 


Comment by: Jesus (5/17/2014 5:02:07 AM)

Hello Thank you very much for your effort and to share.
As many i'm new using VBA. I'm try to follow your instructions to match my work with yours, but I don't be capable to follow you.
would you please explain little more for my.
Thank you again.

 


Comment by: Jan Karel Pieterse (5/18/2014 8:52:31 PM)

Hi Jesus,

There is a lot of explanation in the example file.

Note that this is not something I would recommend for a beginner to start with :-)

 


Comment by: Navdeep (6/18/2014 3:53:00 PM)

Hi Mark,

Can you please share the sample excel for above code as i am trying to do this its not working


Regards
Navdeep

 


Comment by: Yves (7/4/2014 1:43:10 PM)

Hi,
First of all, congratulations for this wonderful code to replace MS lacks on 64bits.

Do you have a way to easily display 1 node with all parents expanded (like 'ensurevisible' in MS treeview)?

This is great when initializing 1 tree.

Thx,

Yves.

 


Comment by: Jan Karel Pieterse (7/4/2014 4:09:07 PM)

Hi Yves,

Thanks!

The cNode.Expanded property of a clsNode class instance (a node) determines -well, that it gets expanded if it has childnodes.

The cTree.ExpandNode method (of the clsTreeView class) expands a node when it is already created.

 


Comment by: Yves (7/4/2014 4:27:05 PM)

Thanks Jan,
I saw this but I am wondering if possibility exist to have tree not expanded and to expand all parents of one node so he is visible.

-> If you load tree with 222 as default value, have the tree closed but 222 visible and selected (& centered with this value will be a must) as below:

100 (Having childs)
200
! 210 (Having childs)
! 220
! ! 221
! ! 222 <- selected
! ! 223
! 230 (Having childs)
! ...
300 ...

This is what EnsureVisible does in MS object...

 


Comment by: Jan Karel Pieterse (7/4/2014 5:17:37 PM)

Hi Yves,

I expect that should happen automatically if you set the ActiveNode porerty to the node you want to have visible.

So lets assume you use similar variable names to the Example:


'Code here that sets cNode
'mcTree holds the instance of the tree control
Set mcTree.ActiveNode = cNode

 


Comment by: Yves (7/4/2014 8:23:06 PM)

This is working!

Thanks for your help.

Yves

 


Comment by: Sujoy (7/15/2014 5:35:54 AM)

Congrats !!! It is a good tool.

The properties of node, such as bold, italic... are not available.

I had to create properties for each of them...


Kindly make the changes to enable those properties of node element.

Sujoy

 


Comment by: Jan Karel Pieterse (7/16/2014 4:31:35 PM)

Hi Sujoy,

Why don't you email your current version so we can include this?

 


Comment by: Abdul Rauf Alipuddin (9/22/2014 4:31:44 AM)

Hi Jan,

For you infor, my last comment was "Comment by: Abdul Rauf Alipuddin (12/31/2013 2:44:43 AM)". Your kindly response to my requests have inspired me to continue and expand my project.

Now I would like to request your help on my additional requirment (addition or modified codes) as follow.

For example, lets refer to your code "Set cNode = cNode.AddChild("2.2.1", "2.2.1 level 3", "OpenBook")"

I believe the node caption "2.2.1 level 3" and Icon of "OpenBook" can be string varibles. So Is it possible to change the Icon if its node caption or its parent node caption (perhaps get from a cell) changes. If Yes, please advise the right additional or modificationcode.

Regards,
Abdul Rauf Alipuddin

 


Comment by: Jan Karel Pieterse (9/22/2014 1:40:16 PM)

Hi Abdul,

Currently, updating a node's image is not supported easily. You can change the ImageMain property of a node, but there is no code that actually changes the image accordingly.

If you urgently need this, let me know and I can quote a price for adding this for you.

 


Comment by: Kurt Bergman (11/6/2014 9:02:36 PM)

Excellent Design - Thank you!

I have a lot of data to display. How do I collapse all nodes once the tree is displayed.

 


Comment by: Sen (11/7/2014 10:42:51 PM)

Hi. I am working on your code to see if applicable for my situation. Thanks for the nice work. It is easy to move around the tree.

When the text inside a node is too long, is there an easy way to "wordwrap"? Where to look at clsNode and clsTreeView classes?

Thank you.
Sincerely.

 


Comment by: Jan Karel Pieterse (11/10/2014 11:30:43 AM)

Hi Sen,

Well, you could change the associated property of the label, but I fear you would have to update quite some code in the treeview because the positioning of other node controls will be affected if a node needs multiple lines.
Since the control is written entirely in VBA this is certainly possible, but a bit of a hassle.

 


Comment by: Jan Karel Pieterse (11/10/2014 11:33:37 AM)

Hi Kurt,

One way is to set the Expanded property of the nodes to False when you define them.

 


Comment by: H Rathod (12/11/2014 8:32:34 PM)

How to Edit a Node in a VBA MS Access TreeView ?

I have added all nodes and root to a treeview, but, now, I want to edit the 9th Node content in the treeview, without a need to repaint entire treeview.

Can this be done in MS Access VBA Coding ? How ? Please guide.

Thanks,

 


Comment by: Peter Thornton (12/18/2014 1:02:52 PM)

Hello H Rathod,
Sorry for late reply, only just noticed your question.
You can add additional nodes to a currently loaded treeview with .AddChild or .NodeAdd methods. See the code behind the buttons on the demo form "Add Sibling" and "Add Child"

 


Comment by: Roy (12/28/2014 11:56:45 PM)

Hi, I'm using your treeview to develop a graphical way of creating SAP org object and relationship files and it's working great.
But some companies can have people that work for 2 or more bosses (i.e. SAP matrix organisations).
So my question is, Is it possible to assign more than one parent object to a child object using your treeview and see it graphically (with lines if possible)? I know I'm asking a lot:)

Happy new year,
Roy

 


Comment by: Jan Karel Pieterse (12/29/2014 11:48:56 AM)

Hi Roy,

That is something that I have given some thought, but it is outside the scope of the treeview I'm afraid.

An n to n relationship diagram is quite complicated to draw as the lines may go in any direction, which would (theoretically) become difficult to read very quickly.

 


Comment by: pradeep (1/10/2015 1:10:26 PM)

request:
currently there is option to dump the data.
Is it possible to create tree structure for the dump data or data which is in similar format ??

 


Comment by: Alexander Bollmann (1/11/2015 8:32:15 PM)

Hi, i used the activex treeview to build a navigation. We got issues with the activex-version and now I try to get your version to work.

I added everything of the demo in my program and replaced the activex-treeview in my form.

The only Problem is, the nodeclick event does not start on my form. On the demoform it works fine, but on my form it just does nothing. (I added the code to the form with "This code fires up when node clicked").

Have you any idea why the events don't start on my form? Did I forget something?

Thanks for the grat code and your help!

 


Comment by: Jan Karel Pieterse (1/12/2015 11:02:58 AM)

Hi Alex,

Have you declared an object variable with the word "WithEvents" at the top of the userform module:

[code]Private WithEvents mcTree As clsTreeView[/code]

 


Comment by: Jan Karel Pieterse (1/12/2015 11:05:33 AM)

Hi pradeep,

I am not sure what you mean. The dump data option already mimicks the tree structure by shifting a column to the right for each child?

 


Comment by: Alexander Bollmann (1/13/2015 2:52:31 PM)

Yes, I did declare the object variable. The Problem was that I outsourced the part of the treeview initialisation in an extra module. Now I wrote that part directly in the code of the form, and now it works. Thanks for your quick reply!

 


Comment by: Jan Karel Pieterse (1/13/2015 4:12:25 PM)

Hi Alexander,

Great. It is better to have the tree code in the form itself rather than on another module (unless you plan to use class modules to handle multiple tree controls of course).

 


Comment by: Andriy (2/19/2015 8:05:00 AM)

Hi, I started adoption of your Treeview on one of my projects. All is going well, however I am stuck with icons/images collection.
Is there a way to dynamically read files similar to example below:

        
ImageList1.ListImages.Add , "TestPictureName", LoadPicture("C:\Text.bmp")


Thanks

 


Comment by: Jan Karel Pieterse (2/19/2015 9:09:18 AM)

Hi Andriy,

Sure you can do that.

Along these lines (somewhere during initialisation of the form, assuming you have a frame called frmImageBox which holds the images:

    Dim oCtl As msforms.Image
    Set oCtl = Me.frmImageBox.Controls.Add("Forms.Image.1")
    oCtl.Name = "imgTest"
    oCtl.Picture = LoadPicture("c:\text.bmp")

 


Comment by: Andriy (2/19/2015 11:03:23 AM)

Thanks for your prompt reply.

Are there any picture type/size limitations?
I use 16x16 bmp files. After form initiation I am trying to create a node with new pic e.g.

Set RootNod = mcTree.AddRoot("_Global", "Global", "Global")


but an error is returned (Error in BuildRoot: Invalid procedure call or argument).
Note, there is Global.bmp file that was dynamically added as discussed above. If I use one one of the embedded pictures such as "Scroll" then there are no errors.
What do I do wrong?

 


Comment by: Andriy (2/19/2015 11:40:23 AM)

Hi Jan,

Please ignore my previous email. All works. Perfect.

Thanks again.

 


Comment by: Jan Karel Pieterse (2/19/2015 1:18:53 PM)

Hi Andriy,

There are limitations when your treeview must work on a Mac. Don't have the needed detail at hand however.

 


Comment by: Peter Thornton (2/24/2015 4:58:02 PM)

@Andriy
Not sure how you got things working but you can load icons in two ways, by passing a Frame containing Image controls or passing a Collection of images (eg populated with LoadPicture, created with APIs or any method to add StdPicture handles)


Dim col as Collection
Set col = New Collection
col.Add LoadPicture("C\Text.bmp"), "myIcon1"
' add more images
Set mcTree.Images = col


Use the unique keys such as "myIcon1" to refer to icons when adding nodes

 


Comment by: Johann Stroh (3/6/2015 1:11:49 AM)

Hi,

Does anyone have any examples of how to remove a node from the treeview?

I also am looking for some examples on moving nodes

Any help/ideas/suggestions would be greatly appreciated

 


Comment by: Jan Karel Pieterse (3/6/2015 11:59:46 AM)

Hi Johann,

For removal of nodes we provide the following method in the clsTreeView class:

Public Sub NodeRemove(cNode As clsNode)
' PT Remove a Node, its children and grandchildrem
'    remove all associated controls and tear down class objects
'    Call Refresh() when done removing nodes

 


Comment by: Peter Thornton (3/6/2015 12:30:56 PM)

Hi Johann.
All the examples you ask for are included in the demo. Select a node and press Del to delete a node (and it's child nodes). Select a node, press Ctrl-X or Ctrl-Y and note the highlight colour change to beige (move) or green (copy), select the destination node and press Ctrl-V.

Apart from the example code in the demo see the following methods in the documentation (in the Excel file), NodeRemove, Move and Copy in the clsTreeView sheet. You can implement these methods as you wish, eg from a right click context menu.

 


Comment by: Janin Lord (4/16/2015 12:36:09 AM)

VERY GREAT TREEVIEW TOOL FOR ACCESS !! YOU'VE JUST MADE MY DAY !!

Question though : how come textboxes, option buttons and listboxes doesn't work (as if they were disabled) ? Looks like they're out of focus. Thought ALL buttons works as in your demo form... My form is no use without all of them... Please !!!

 


Comment by: Jan Karel Pieterse (4/16/2015 7:42:57 AM)

Hi Janin,

You're welcome.

I don't know why some things aren't working on your implementation.

Perhaps you might try to contact Ben Clothier as he did the work on the Access changes. A link to his LinkedIn profile is on this page under Acknowledgements:

http://www.jkp-ads.com/Articles/treeview.asp

 


Comment by: Peter Thornton (4/16/2015 6:30:03 PM)

Hi Janin,
Long shot, I wonder if what you describeD might be related to the Dec-2014 update that caused problems with MSForms controls. The fix that normally works is to delete or if you prefer rename all *.exd files in various system folders, they will get re-created as needed.

 


Comment by: Keven Kemege (5/8/2015 9:59:51 PM)

is Me.mcTree.Nodes the same as TreeNodeCollection?

should "t" be "clsTreeview"?

Call WordRecursion(Me.mcTree.Nodes, oWord)
    End Sub
    
' Public Sub WordRecursion(ByVal t As TreeNodeCollection, ByVal oWord As Object)

Public Sub WordRecursion(ByVal t As clsTreeview, ByVal oWord As Object)

 


Comment by: Peter Thornton (5/9/2015 6:02:40 PM)

Hi Kevin,
Your question is very cryptic. In a light search "TreeNodeCollection" appears to be a collection of nodes in various types of Treeviews, though not the MSComCtl.ocx version which our treeview replaces for VBA. Indeed clsTreeview.Nodes does return the nodes collection though do not assume the respective methods are directly comparable with other versions. Note also clsNode.ChildNodes which typically may be useful in recursive functions

There are recursive examples in the Excel and Word demos, look behind the "Dump Data" button

 


Comment by: Yves (5/11/2015 1:34:15 PM)

Hi !
Thx a lot for that marvellous tool.
However one silly question : I dont want the form to strech each time the form resizes ... it goes to weird results in my own tests !! Can you help me through the class ?
Thx !
Yves

 


Comment by: Peter Thornton (5/12/2015 2:17:59 PM)

Hi Yves,
The demo forms shouldn't stretch, nothing the treeview does should change any dimensions except perhaps the internal scroll dimensions of its container frame. Which form are you talking about?

 


Comment by: Geoff Harper (5/17/2015 1:07:48 PM)

What a fantastic tool!

I am able to do everything I want, except for one ankle-biter. I have followed instructions but I cannot get rid of the border around the TreeView container. I must be doing something wrong, but I don't know what.

Thanks!
-Geoff

 


Comment by: Jan Karel Pieterse (5/18/2015 6:20:10 AM)

Hi Jeff,

That is a property of the parent frame. For me it (rather odd) works if I change the borderstyle to 1 and then back to 0.

 


Comment by: Aleem (5/28/2015 9:30:05 AM)

Hi,

Thank you so much, this is just so wonderful, i would like to save the text once i update example if i use this sample to enter the org strcuture and to keep it save, how i will save these text.

thanks
aleem

 


Comment by: Jan Karel Pieterse (5/28/2015 2:58:18 PM)

Hi Aleem,

The sample workbook has a button to dump the current tree content to a worksheet if I recall correctly, so you should be able to use that and modify it.

 


Comment by: Brian (9/22/2015 10:51:40 PM)

How do I initialize the tree with all nodes collapsed? I am using access 2013 and am populating the tree from a table. It all works great except the tree appears with all nodes expanded. I notice your demo behaves the same way.

I tried to useing tv.ExpandToLevel 0, but got some strange behavior. Do I have to iterate through all nodes and set expanded property to false?

Thanks for a great control.

 


Comment by: Jan Karel Pieterse (9/23/2015 5:49:36 PM)

Hi Brian,

You should be able to set the expanded property when you add the nodes to the tree.

 


Comment by: Luis G (10/26/2015 9:58:56 PM)

Great job.Thanks for sharing.

Any way to include multiple columns?

 


Comment by: Peter Thornton (10/28/2015 10:07:52 AM)

Hello Luis

What do you mean by "multiple columns" ?

 


Comment by: Alan WIlson (12/10/2015 10:03:29 PM)

Very nice piece of work. It helps me a lot and is the core display for a fairly complex data set.

I'm running the Access version and loading a recursive data structure to the tree. It works very well after I added a couple of fields to the nodes to handle linking the table record to display.

Question - I'm working with the copy node function - I let your code add the tree node and then I add the linked table record. But I need to find the node the tree added so I can update the linked fields I've added to the tree. I don't see a find type of operation so I'm assuming I need to write one. In general terms, what would you suggest that would be a consistent approach for stepping through the tree keys?

Thanks,
Alan

 


Comment by: Peter Thornton (12/11/2015 4:12:42 PM)

Hi Alan,
If you don't specify Before or After arguments in the Copy function (see the documentation), your newly copied node will be the last node in destination's childnodes collection, eg

mcTree.Copy cSource, cDest
Set cCopiedNode = cDest.ChildNodes(cDest.ChildNodes.Count)


If using Before/After (only if you know the given before/after exists) replace the .count with index = Before or index = After+1

If calling the Sort function set a ref to the copied node first.

 


Comment by: Luke M (12/18/2015 8:27:03 PM)

Thanks for making this available. It is very impressive.

Like Luis, I'm also interested in a multi-columned tree. It would work just like the Locals/Watches windows in the VBA Editor. Here is a screenshot that should help describe what I'm after: http://i.stack.imgur.com/A43d6.png. Is this possible?

 


Comment by: Peter Thornton (12/21/2015 12:20:17 PM)

Hi Luke,
The simplest approach would be to add a Listbox for the columns. Trap the treeview's node-expand event (not included but easy to add) and update the multi-column list to line up with nodes as expanded. Tip, set a low NodeHeight and let it autosize to the minimum required to match the listbox row height, assuming similar fonts.

If you have dug inside the treeview and worked out how all the controls are added and updated, it's fairly straightforward to add additional columns similar to how the node controls are created and updated, but with their Lefts aligned in columns. Just that might be enough for your needs but for general use would need a lot more work to make it all look and handle nicely.

 


Comment by: Alexander Finck (1/7/2016 11:20:24 AM)

Hi Alan,

did you already implement a "Find" function ?
I also need this function for my own, and if it already exists i don't have to invent the reel again :-)

 


Comment by: Peter Thornton (1/11/2016 2:26:10 PM)

Hi Alexander,
If what I suggested as an answer for Alan, how to 'find' a newly copied node, is not what you are looking for describe what you want a "Find" function to find.

 


Comment by: Alexander Finck (1/13/2016 8:59:37 AM)

Hi Peter,

i need to put some additional information inside a node, maybe using the .tag field.
And to get access to that information i need to find the node first :-)

Like in the demo: There's a node for Jennifer Aniston, but how can i access the information ? Either i have to step through all the nodes manually, or use a "find" function :-)

Regards
Alex

 


Comment by: Peter Thornton (1/13/2016 3:52:37 PM)

Hi Alex,
This is very different objective to the "Find" Alan needed. Without knowing more details you will probably need to loop nodes comparing Caption or Tag properties. If there might be similar or duplicates you may need to continue searching. However if you already know the parent node you could limit to looping looking at its Childnodes collection. Get used to looping recursively to look down a branch.

I don't know if relevant for your purposes but use of Keys might help, no need to loop and search for those. However care is needed to avoid duplicate Keys.

If you want to offer a Find feature for users see the "Project Explorer" demo on the Examples page.

 


Comment by: Joseph Volence (1/21/2016 8:24:36 PM)

I am getting a runtime error 2455 "you entered an expression that has an invalid reference to the property Form/Report"

on the line

Set mcTree = subTreeView.Form.pTreeview


I am using Access 2010, have the subform imported from your file, and the same name.

Thanks!

 


Comment by: Joseph Volence (1/22/2016 3:11:12 PM)

Nevermind, it was because I was trying to load the tree before the subform was loaded, from the form.load event.

I worked around it another way.

 


Comment by: Ed Leibrick (1/27/2016 1:09:24 AM)

I would also like to thank you.
Your code has simplified a tricky issue for me and has impressed all who view it.
Cheers

 


Comment by: Jan Karel Pieterse (1/27/2016 10:29:24 AM)

Hi Ed,

Thank you!

 


Comment by: Ian Wilson (3/1/2016 12:10:28 PM)

Hi,

Thank you for the fantastic code! It is helping me remove the very problematic OCX treeview my access 2010 application!

It is possible to have event handlers for each individual Treeview? I have 4 forms with Treeviews on, all working fine but each form has a specific function so I could do with writing individual onClick events, currently each one calls the TreeControl_Click routine in clsTreeview.

Thank you.

 


Comment by: Jan Karel Pieterse (3/1/2016 9:21:57 PM)

Hi Ian,

If you declare a separate object variable for each tree using WithEvents you'll have each of them in the top-left dropdown to pick from so you can add their events.

 


Comment by: Ian Wilson (3/2/2016 8:28:58 AM)

Hi,

Thanks, all working as I need now.

Your Treeview is such a big improvement on the Common Control one, well done!

 


Comment by: Rob L (3/18/2016 4:37:12 PM)

Thanks for sharing this excelent treeview control as replacement for the MSCOMCTL .
I am using it in an Access project, and it works very well !
The only thing is that the images in the frImages frame cannot be changed from within Access2007....it refuses to apply changes (just wanted to replace your flag images with my images, but no way). The other method you mention to make a collection needs the function GetIcons . Can I get it somewhere ?

 


Comment by: Jan Karel Pieterse (3/18/2016 4:48:44 PM)

Hi Rob,

GetIcons is a function you can find in the Excel demo of the treeview. What it comes down to is that you need to pass a collection of Picture objects to the treeview (using their names as key values). Whether that collection is created by reading the picture properties from the images in a frame or by loading Picture objects from somewhere else makes no difference to the treeview.

 


Comment by: Jan Karel Pieterse (3/18/2016 7:25:08 PM)

Hi Rob,

Images can't be edited in the Frame in Access for some odd reason. From memory one way is prepare the images with pictures on a Frame in Excel (or just on a userfrom), select and copy them. Then in Access put the msForms Frame in the subform into design mode (should see the Toolbox) and simply paste the image controls. Can do the lot in one go.

A different approach waiting for a rainy day is to store the Images as "blobs" (effectively the image file as a text or probably better as Hex or Base64) into a DB Table, then convert as a byte-array to an StdPicture object. A bit of work but pretty confident this would work.

 


Comment by: Antonio (4/18/2016 10:01:39 AM)

Hi.

I've been implementing the treeview succesfully on Access 2010 64 bits.

But I have some issues:

I need to move nodes in the same branch by VBA Code.

I use the following to move up:

Private Sub cmdTabUp_Click()
    Dim oAct As clsNode
    
    Set oAct = New mcTree.ActiveNode
    If oAct Is Nothing Then Exit Sub
    If Not oAct Is oAct.FirstSibling Then
        Set mcTree.MoveCopyNode(True) = oAct
        mcTree.Move oAct, oAct.ParentNode, oAct.Previous.Key, bShowError:=True
    End If
End Sub

But i get an error. How can I move anode one position up or down in the branch?

Many thanks.

Regards. Antonio.

 


Comment by: Jan Karel Pieterse (4/18/2016 12:20:17 PM)

Hi Antonio,

Forgive my ignorence, but shouldn't this line:

Set oAct = New mcTree.ActiveNode


be modified to:

Set oAct = mcTree.ActiveNode

 


Comment by: Antonio (4/18/2016 2:45:35 PM)

Hi Jan.

Sorry, I have a mistake on the code posted.

Correct code is here.

Private Sub cmdTabUp_Click()
    Dim oAct As clsNode
    
    Set oAct = mcTree.ActiveNode
    If oAct Is Nothing Then Exit Sub
    If Not oAct Is oAct.FirstSibling Then
        Set mcTree.MoveCopyNode(True) = oAct
        mcTree.Move oAct, oAct.ParentNode, oAct.Previous.Key, bShowError:=True
    End If
End Sub


Anyway, How can I move a node up or down in the same branch by using VBA code?

Thanks and best regards.

Antonio.

 


Comment by: Antonio (4/18/2016 7:22:00 PM)

Hi Jan.

After modify the code, I receive the error code 9 at the Move procedure located in the clsTreeview class module.

Thanks in advance.

Regards.

Antonio.

 


Comment by: Peter Thornton (4/18/2016 8:47:40 PM)

Hi Antonio, try these

Dim cGrandParent As clsNode
Dim cFirstSibling As clsNode

    Set oAct = mcTree.ActiveNode
    If oAct Is Nothing Then Exit Sub

'up
    Set cGrandParent = oAct.ParentNode.ParentNode
    mcTree.Move cSource:=oAct, cDest:=cGrandParent
    mcTree.Refresh

'or down
    Set cFirstSibling = oAct.FirstSibling
    If Not oAct Is cFirstSibling Then
        mcTree.Move cSource:=oAct, cDest:=cFirstSibling
        mcTree.Refresh
    End If


I didn't include the before or after argument as not sure what position in you want to place the moved node, as written will be placed as the last childnode.

Use of PreviousNode doesn't make sense here as that refers to a current sibling, you might want want to refer to first or last node in the what will be the new parent's collection of childnodes.

You don't need to set MoveCopyNode for these actions

 


Comment by: Antonio (4/19/2016 4:52:52 PM)

Thanks Peter.

I'll try it later. The positions are one before o one after. So I used the firstSibling and lastSibling to avoid running code in that positions (up or down).

Best regards.

Antonio.

 


Comment by: Peter Thornton (4/20/2016 10:59:46 AM)

Antonio,
Perhaps I misunderstood your objective, is it to move the node up/down in the the same collection its parentnode's childnodes?
If so start by getting the index of the node's index in the parent's childnodes collection, simply loop its childnodes collection until you find your node.

Check the node is not its First/LastSibling (depending on direction to move) and

mcTree.Move cSource:=oAct, cDest:=oAct.ParentNode, vBefore:=idx - 1
' or
                        vAfter:=idx+1


and call .Refresh

 


Comment by: Antonio (4/20/2016 11:41:51 PM)

Hi Peter.

You are right, I wanted to move the active node one position up or down in the current branch.

As you can see in my code (move up), first I check it's not the first sibling.

<code>

If Not oAct Is oAct.FirstSibling Then

</code>
        
And later I move the node

<code>
        mcTree.Move oAct, oAct.ParentNode, oAct.Previous.Key, bShowError:=True

</code>

But with this code I always get an error code 9.

I'll check your modification, using the index.

Thanks for all.

Best regards.

Antonio.

 


Comment by: Donald LaDue (5/17/2016 4:37:55 PM)

Hi Team,

great work!

I have two suggestions:

1. Add an "If Len() >0" to the property below.
Reason: If you read the Images from a table and the ImageExpanded field is blank (Nullstring), you otherwise receive an error.

Public Property Let ImageExpanded(vImageExpanded)
' PT string name or numeric index for an expanded icon key
    On Error GoTo errExit
    If Not IsMissing(vImageExpanded) Then
        If Not IsEmpty(vImageExpanded) Then
'DGL: ADDED THIS LINE:
            If Len(vImageExpanded) > 0 Then
                If Len(mvIconMainKey) = 0 Then
                    mvIconMainKey = vImageExpanded
                End If
                mvIconExpandedKey = vImageExpanded
                mlIconCnt = 2
            End If
        End If
    End If
errExit:
End Property


2. I feel that the labels (label text) of the nodes stick too close to the Images.
It would be great to have a parameter where you can determine the gap between text and Image.
But maybe it is there already and I overlooked it?
BTW: Setting .FullWidth = True widens the gap nicely, but you probably want control over the gap independently of this setting.

Regards,
Donald

 


Comment by: Jan Karel Pieterse (5/18/2016 6:59:15 AM)

Hi Donald,

This constant in clsTree controls the padding of the icon:
Private Const mcIconPad As Single = 14.25

As you can see there is a whole bunch of constants there determining the looks :-)

 


Comment by: Donald LaDue (5/20/2016 5:45:23 PM)

Hi Jan,

Thanks for pointing out the padding possibilities via mcIconPad.

At first I was a bit puzzled as that did not show the effect I wanted.
I found out that this is because I'm looking at the gap between .Picture and .Caption within the same(!) Label-Object.
For Illustration:
With MyLabel
.Picture = SomePicture
.Caption = SomeCaption
End With

There probably is no way of getting more room between .Picture and .Caption other than puttung a Space before the .Caption, is there?

 


Comment by: Peter Thornton (5/23/2016 11:19:38 AM)

Hi Donald,
To change the space between the icon and caption change the constant mcIconPad to suit as Jan Karel suggested. First, if necessary, adapt the constant mcIconSize to the size in points of your largest icon (in "normal fonts" Windows 12 points = 16 pixels). To give a 3pt space between 12pt(16pxl) icon and caption

    Private Const mcIconSize As Long = 12
    Private Const mcIconPad As Single = mcIconSize + 3


In hindsight perhaps we should have declared these as variables and given default values in the initialize event.

A similar approach and constants are used for checkboxes.

If distributing to Mac keep in mind 1 point = 1 pixel and adapt the Mac constants similarly.

 


Comment by: David Reid (6/24/2016 1:39:48 AM)

Could you please help me to understand how to set the activenode of the treeview from an unbound subform in Access:

Following is my code:

' on main form:
Public Property Get propTree() As clsTreeview
    Set propTree = mcTree
End Property

'main form: 00_frmOpenMe
'treeview on main form: mcTree
'unbound subform: ctrTree

' on unbound subform:
Private Sub Form_Click()
Dim cTree As clsTreeview
Dim cNode As clsNode
    Set cTree = Me.Parent.propTree
    If Not cTree Is Nothing Then
     With cTree
        Set cNode = cTree.Nodes(Key)
                        
        ' both of these lines work
         Forms![00_frmOpenMe].ctrTree.SetFocus
                         'or
         Forms![00_frmOpenMe].Controls("ctrTree").SetFocus
                
        ' but I still get an error 91: obj/with var not set
         Set cTree.Activenode = cNode
     End With
    End If
End Sub

'Refer to Form and Subform properties and controls
'http://access.mvps.org/access/forms/frm0031.htm

Got to be as simple mistake I'm making. Thank you for
your help.

-David

P.S. Love your treeview control.:)

 


Comment by: Jan Karel Pieterse (6/24/2016 11:17:20 AM)

Hi David,

The Nodes collection is by index, not by Key so you need to run through the nodes collection to get the right one:

    With mcTree
        For Each cNode In .Nodes
            If cNode.Key = Key Then
                Set .ActiveNode = cNode
                Exit For
            End If
        Next
    End With

 


Comment by: René (6/29/2016 12:58:22 AM)

Hello!
I don't have any special comment or question but I just wanted to say thank you for generosity!
Your treeview is great and way better than what Microsoft has decided to take away. I'm still a bit puzzled how they could do that!
Cheers Rene

 


Comment by: Jan Karel Pieterse (6/29/2016 9:31:37 AM)

Hi Rene,

Thank you!

 


Comment by: tz3poo (7/1/2016 12:37:17 PM)

I don't usually do this - but thanks... Your treeview is better then MS's and better than the one i've made myself. Having said that ( :))), how can i use the mousewheel to scroll through the nodes. As it is now, the wheel doesn't work - only on your control - it works on anyother control in ms access. Thnx again

 


Comment by: Jan Karel Pieterse (7/2/2016 7:36:50 PM)

Hi tz3poo,

Thanks! For enabling wheel scroll you would need the (paid) pro version of the treeview, which is in beta. If you would like to aprticipate in the beta program, please let me know.

 


Comment by: Trandafir Marius (7/3/2016 10:50:00 AM)

Hey Jan. I'd love to enter the beta program. How much would i have to pay for the pro version.
P.S. Also, if you use textboxes for the branches of the tree, why not use richtext on them - so we could use html tags for format?
Thnx a bunch - and by the by - your tree is awesome and easy to implement.

 


Comment by: Brandon B (7/7/2016 2:07:27 AM)

I am having some trouble getting the expander +/- to show. I did set the mcTree.ShowExpanders = True but I am not actually seeing the expanders. Is there something else that I need to do... or is there something that could be blocking them somehow?

Below is part of the initialization sub


With mcTree
        Set .TreeControl = Me.frTreeControl
        .AppName = "test name"
        .CheckBoxes = False
        .RootButton = False
        .EnableLabelEdit = False
        .Indentation = 20 * 0.75
        .NodeHeight = 16 * 0.75
        .ShowExpanders = True
        .ShowLines = True
        
        Set .Images = Me.frmImageBox
    
        For Each animalID In ws2.Range("B2:B" & ws2.Range("B" & ws2.Rows.Count).End(xlUp).Row)

            If mcTree.RootNodes.Count = 0 Then 'initialize the tree
                Set cRoot = .AddRoot(CStr(animalID.Offset(0, 1).Value), CStr(animalID.Offset(0, 1).Value), "tenrecImg2")
                Set cNode = cRoot.AddChild(CStr(animalID.Value), CStr(animalID.Value), "greyCkImg2")
                GoTo NextIteration
            End If
            
            For Each cExtraNode In .RootNodes
                If CStr(animalID.Offset(0, 1).Value) = CStr(cExtraNode.Caption) Then
                    Set cNode = cExtraNode.AddChild(CStr(animalID.Value), CStr(animalID.Value), "greyCkImg2")
                    GoTo NextIteration
                End If
            
            Next cExtraNode
            
            Set cRoot = .AddRoot(CStr(animalID.Offset(0, 1).Value), CStr(animalID.Offset(0, 1).Value), "tenrecImg2")
            Set cNode = cRoot.AddChild(CStr(animalID.Value), CStr(animalID.Value), "greyCkImg2")
            
NextIteration:
        Next animalID
End With

 


Comment by: Jan Karel Pieterse (7/7/2016 10:29:14 AM)

Hi Brandon,

I don't see anything obviously wrong with your code apart from a missing Refresh statement to refresh the treeview after setting up the nodes. Best place it before the End With:

    .Refresh
End With

 


Comment by: Brandon B (7/9/2016 7:29:21 AM)

Hey Jan,
Thanks for having a look at it for me. I accidently skipped the .refresh when copy/pasting the code over and the actual issue ended up being something super simple... I had .rootbutton = false /face_to_palm.

I wanted to let you know what a life saver your tree is. I spent weeks working on a form that would streamline data collection and animal care at our lab. The Microsoft version of treeview was a central piece to the whole project... but after creating the whole thing I realized that it would not work on the lab's 64bit excel and Macs. I am baffled by Microsoft's decision to exclude such immensely useful controls from those versions of VBA. But at the same time I am a little glad that they did. Your treeview was much easier to use and more flexible!
So..

Dim i As Integer
i = 1

Do While i <> 1
msgbox "THANK YOU!"
Loop

 


Comment by: Brandon B (7/9/2016 7:45:36 AM)

GAH i ment
Do While i = 1

*feel free to just edit my previous comment...and we will pretend that this never happened haha

 


Comment by: Jack M (7/9/2016 1:04:56 PM)

I'm new to Excel VBA and am working on a simplified double-entry accounts project for to help my son with his business accounts.
I saw your brilliant treeview and wanted to use it for my 'Chart of Accounts' with the level 1 nodes being account types e.g Assets/Liabilities etc and lower levels being accounts eg Bank account/Savings account etc.
It all works perfectly from nodes I build into the coding and the I have an 'Add Account' button which adds a node perfectly.
The problem I have is that I can't find what I need to do to RETAIN THE NODE ADDED VIA THE BUTTON, ie. I can Add it,expand it, close and re-expand it etc. but, when I close the InputForm, the newly added node is lost!
Thanks in anticipation.

 


Comment by: Peter Thornton (8/1/2016 3:41:48 PM)

Hi Jack,
Sorry only just noticed your question on this page, if you haven't already figured it contact me off-line as I don't quite understand what you're asking.

 


Comment by: Karthikeyan.s (8/9/2016 1:07:21 PM)

hi,
I cant able to print check box and tree structure once i press the Dump data,what i have to do to print it in exact tree structure as you shown in example.

 


Comment by: Jan Karel Pieterse (8/9/2016 1:54:39 PM)

Hi Karthikeyan,

If you double-click the button on the dem userform, there is an "Exit Sub" command in the click event of the button. Remove that to make it work.

 


Comment by: BUM SUK KO (9/6/2016 2:57:13 PM)

Hi, This is Bumsuk Ko from South Korea.

I found your treeview three days ago in a website.
I was really really astonished from your treeview result.
As you know, MS common control sometimes bothers people. So I have tried to avoid using it. At this moment, your treeview gives me a chance to use treeview in my program.
Thanks a lot.

And I also found listgrid/listview. If it is not too late, can I join as a beta tester? Or Can I purchase your listgrid/listview?

I'm so excited to wait for your response about listgrid/listview.

Best Regards,
Bumsuk Ko

 


Comment by: Peter Thornton (9/6/2016 9:20:39 PM)

Hi Bumsuk Ko,

It's never too late:) Please contact us via the "Contact us" link just below the ListGrid details on the main treeview page. If you can't find it you can contact me directly, you can find my address in the headers of the treeview demo.

 


Comment by: Steve Wilson (9/30/2016 4:29:48 PM)

What an awesome job you guys did with this code! I spent a couple of days perusing through it trying to figure out what methods did what, and WOW... very impressive.

I have one question though:

I have a function built to test if a node already exists in the tree, but I'm struggling a bit on determining the most resource-efficient way to go about this is. What would be your recommendation?

 


Comment by: Jan Karel Pieterse (9/30/2016 5:16:57 PM)

Hi Steve,

Thanks for the compliments!

What does your current function look like?

 


Comment by: Steve Wilson (9/30/2016 5:55:59 PM)

I've tried a couple of things, but nothing seems to work just yet. My application runs through all the rows in a recordset using the mcTree.NodeAdd method, which works great. However, creating a simple Boolean function to see if the node already exists has proven to be my Achilles' heel this week.

Here's what I've been trying to do:


Private Function NodeExists(intUnitID) As Boolean
    'test to see if node exists in the treeview object: mcTree
    
    NodeExists = True
    
    'test for nothing, then set to false
    If mcTree.Nodes.Item("'" & intUnitID & "'") = Nothing Then
    
        NodeExists = False
        
    End If

End Function

 


Comment by: Jan Karel Pieterse (9/30/2016 7:04:13 PM)

Hi Steve,

I'd do something like:

Function NodeExists(cTree As clsTreeView, sKey As String) As Boolean
    Dim cNode As clsNode
    For Each cNode In cTree.Nodes
        If cNode.Key = sKey Then
            NodeExists = True
            Exit Function
        End If
    Next
End Function

 


Comment by: Mike (10/26/2016 8:52:29 PM)

I get "Compile error in clsTreeView" when using this in Mac Excel version 15.28 (161019).. This was working fine on older Mac Excel and on PC Excels... Any ideas what's up with the newer max Excel?

Any fix available?

 


Comment by: Peter Thornton (11/3/2016 11:57:55 AM)

Mike, could you contact me off-line and tell me exactly where it fails to compile in your Mac. Get my address from the headers.

 


Comment by: John Bingham (1/10/2017 3:48:25 AM)

Hi,

We've incorporated the tree into our product in order to escape from mscomctl. Really what you've done here is absolutely incredible and fantastic.

Our application incorporates a lot of subforms and tabcontrols, and either may be nested inside the other.

When any form containing an mcTree is viewed in design view, the mcTree subform insists on always appearing on top.

For example, a form FF has a tab control TC1. On TC1's second tab, there is a subform SF, containing another tab control, TC2 (TC2 is in a subform to overcome MS Access's inability to render nested tab controls correctly). TC2 contains an mcTree. When I view the form FF in design, I see TC1; By default I do not see SF, because SF is on a tab of TC1 which isn't the default tab - yet I do see the mcTree subform contained in SF.

This behaviour can be quite frustrating, if you need to be able to see the controls existing in the same part of the screen as where the mcTree control is. Can this behaviour be overcome?

I don't know for certain what code is causing the behaviour in the first place, and I'm reluctant to just wade in and make lots of changes to try and fix it, given what I don't know.

Can I get any guidance, re what logic causes this behaviour and if applicable what issues are involved, which this behaviour was or is intended to solve?

Many thanks
John

 


Comment by: Peter Thornton (1/10/2017 4:55:51 PM)

Hi John,

It sounds like you've got a complex arrangement of tabs within subforms within tabs? If I follow the problem is only in design, yet you say "not sure what code is causing the behaviour".

If you could you send me a non-sensitive file that shows the problem I'd be interested to have a look. My address is in the headers.

 


Comment by: Bolat (1/17/2017 5:47:55 AM)

Hi.
It really great job you have done. Many tahnks.
Just wondering is it possible to show treeview in "read only" option.
i mean , just to prepare treeview as i need, show it to a user, and not to allow user to change/click checkboxes.
I could not find any properties of checkboxes like "locked" or something similar.
Is it possible to solve this question ?

 


Comment by: Jan Karel Pieterse (1/17/2017 8:50:00 AM)

Hi Bolat,

If you add this little line of code to the demofile in the routine called "InitializeDemo1", just below "With mcTree":

.TreeControl.Enabled = False


then you effectively disable clicking the tree.

 


Comment by: Bolat (1/18/2017 5:29:11 AM)

Hi, Jan.
Thank you for prompt reply.
But if i add this line [.TreeControl.Enabled = False] - it will disable the whole tree, and i can not expand/collapse, scroll, etc. If "tree" is too long, it is not possible to see all nodes, especially childnodes.
What should i do to allow user to expand/collapse, scroll, but to prohibit change checkbox status ?
Thank you &
Best reagards,

 


Comment by: Jan Karel Pieterse (1/18/2017 8:24:31 AM)

Hi Bolat,

CLicks on the checkbox are handled in the clsNode class. You could simply comment this routine:

Private Sub mctlCheckBox_Click()
'Other code here
End Sub

 


Comment by: Bolat (1/18/2017 10:48:57 AM)

Great,
Thank you so much for your support.
I used public variable to select wether it is allowed to edit(true) or just to view(false).
So easy. Thank you.

One question more.
1) there are some nodes in childnodes collection (for example level=2), which are not to be changed in any case. if parent node checkbox status is 0, and when i click on the checkbox to change status to -1, i do need to predict that there are few childnodes ( not all but part of them) and they should not(!) to be changed .
It means that status of childnodes boxes is going to be changed -1, exept ones i talked above. and their status should remain = 0,
So automatically status of the parent node must be changed to +1 only (!!), since not all childnodes are =-1 ( part of them should remain with status = 0).

What shall i do to get such a rezult ?
Thank you &
Best regards

 


Comment by: Jan Karel Pieterse (1/18/2017 6:18:25 PM)

Hi Bolat,

I would add a property to clsNode which allows you to enable/disable its checkbox for clicking so you can turn that on or off when adding the node. You would also have to include an argument in the NodeAdd and AddRoot subs in clsTree so you can pass that setting to those methods. Then finally set the new clsNode property in NodeAdd and AddRoot according to the new parameter.

 


Comment by: Bolat (1/19/2017 7:53:12 AM)

Hi, Jan.
Thank you for your message.
Frankly speaking, it's hardly understand what shall i do. Sorry for bothering you.
Do you have some similar sample ?
for exapmle tree nodes are, all their checkboxes status are 0:
1. Cars
1.1 Toyotas
1.2 KIA
1.2.1 KIA RIO
1.2.2 *KIA OPTIMA
1.2.3 KIA CEED
if i click on node [1.2 KIA](!) to change status, all checkboxes are to &#1080;&#1091; changed , except [1.2.2 *KIA OPTIMA] - because first char is "*". thus status of checkboxes should be converted as
0 -> 1.1 Toyotas
1 -> 1.2 KIA
-1-> 1.2.1 KIA RIO
0 -> 1.2.2 *KIA OPTIMA
-1-> 1.2.3 KIA CEED
I would apreciate for any help/code sample.
Thank you in advance

 


Comment by: Peter Thornton (1/19/2017 6:23:27 PM)

Hi Bolat,
I don't quite follow what you are trying to achieve but you can validate and check changes in the NodeCheck event and apply as you you want, for example

Private Sub mcTree_NodeCheck(cNode As clsNode)
' prevent user from checking any level-2 nodes, or any nodes with "3" in the caption
    With cNode
        If .Checked Then
            If .Level = 2 Then
                .Checked = False
            ElseIf InStr(1, .Caption, "3") Then
                .Checked = False
            End If
        End If
    End With
End Sub

 


Comment by: Bolat (1/24/2017 6:10:48 PM)

Hi, Peter.
Thank you for your reply.
It's working. But there is one issue.
it's very difficult for me to explain, and I did not find any possibility to attach a picture .
Thus I loaded picture into cloud. this is the link

https://1drv.ms/i/s!Ahfd8-miifhwj6UvhmDlXjOBnnMd2Q

When I click on root node , all nodes status should be changed from [0] to [-1] except ones which caption consists of "reserved"(for example - [1.2.2 (reserved) KIA OPTIMA]). Yes, status of [1.2.2] is [0], but all "below"(1.2.3, 1.3 , etc) nodes status have been changed from [0] to [1], but should be changed to [-1] .
What else code I should add to solve the problem ?
Thank you in advance.

 


Comment by: Peter Thornton (1/25/2017 1:18:59 PM)

Hi Bolat,
With the triState checkboxes option, when you click a checkbox it will change to 0 or -1, then all descendant checkboxes will change to the same value, but parent checkboxes may change to the appropriate value of -1, 0, or if a parent's childnodes have a mixed combination of checkboxes it will change to +1 (grey).

If I follow you want to be able to check a parent, but maybe prevent some child checkboxes from changing to the same value. This defeats the normal way triState checkboxes work. Although possible to adapt the code to meet your objective difficult to suggest without knowing a lot more about your treeview, eg only leaf-nodes might be prevented from changing, or other constraints or requirements. Can you try the conventional way without using the triState option?

 


Comment by: Mike Smith (2/8/2017 8:29:52 PM)

Does the clsTreeView works on the MAC 2016 version of Excel? Seems there might be issues with their move to 64 bits only. Please check this problem that was reported, and the reply given by the Mac MVP... Thanks!

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac/error-message-when-i-open-macro-worksheet-excel/d8edd28a-8efc-48b3-9992-7b42edd26f0a

 


Comment by: Peter Thornton (2/10/2017 12:44:45 PM)

Hi Mike,

We don't have Mac 2016-64 to test, but we've made a small change with use of the #If Mac compile constant and uploaded.

Could you download treeviewExcel.zip again and test. If it fails, open the project, do Debug/Compile and advise where it fails to compile.

 


Comment by: Erlind (2/23/2017 4:20:24 PM)

Hi there,

thank you very much for this great treeview.

I found it yesterday and today I started implementing it into my project where I am currently using MSComctlLib.

I have already modified how the tree is populated based on my tables by there is something that I am missing.
In my example I create four Roots and then add child nodes. Problem is that all the child nodes end up being on the last root that I add.
Is there a way to programmatically activate the desired RootNode prior to adding ChildNodes?

Thanks in advance

 


Comment by: Peter Thornton (2/24/2017 1:52:22 PM)

Hi Erlind,
Several ways, eg to return the 3rd root

a) Give each root a Key when you create it, then later when you need it -
Set cRoot = mcTree.Nodes("myRoot3")
cRoot.AddChild(args)

b) Add the 4 root nodes first and, assuming none will be deleted until you need it -
Set cRoot = mcTree.Nodes(3)

c) an array of roots
Dim cRoots() as clsNode
Redim cRoots(1 to 4)
for i = 1 to 4: Set cRoots(i) = .AddRoot(arg's)
cRoots(3).AddChild(args)

d) get the first root's parent's 3rd childnode
Set cRoot = .Nodes(1).ParentNode.ChildNodes(3)

The root nodes' parentNode is not part of the visible tree or included in the Nodes collection

 


Comment by: Erlind (2/24/2017 1:59:43 PM)

Thank you very much, Peter!

That was very helpful.

Regards


 


Comment by: Erlind (3/2/2017 1:48:09 PM)

Hi there,

is there a way to raise an event when in image on the tree is clicked?
At the moment the event is raised only if the label or the expander are clicked (through mctlControl_Click() )

Thanks in advance

Regards

 


Comment by: Peter Thornton (3/3/2017 12:29:30 PM)

If you want the icon to raise the same events as the label -
in the clsNode declarations, change
Private mctlIcon As MSForms.Image
to
Private WithEvents mctlIcon As MSForms.Image

and have it's events call equivalent label events, eg

Private Sub mctlIcon_Click()
    mctlControl_Click
End Sub

Normally it's best to add event stubs from the top right dropdown, first select mctlControl in the middle dropdown.

 


Comment by: Erlind (3/3/2017 9:40:16 PM)

Hi there Peter,

thank you very much! Your answers are always quick and precise.

Speaking of; is there a way to scroll the tree view using the mouse scroll?

I looked inside clsTree but nothing pointed to a scroll event

Thanks in advance

 


Comment by: Peter Thornton (3/6/2017 12:47:13 PM)

Scroll is not built-in to the treeview, or indeed into any MS VBA, or VB/ocx type controls. If you search the internet you might come across an approach for VBA controls developed by and originally posted by, er, me! Re-posted and adapted many times by others.

However I strongly suggest you don't try integrating this into the treeview unless you really understand how it works, and even then will take a long time to get it working reliably.

Wheel scroll is built-in to the 'pro' treeview (see link above), after a considerable amount of development time!

 


Comment by: Erlind (3/7/2017 11:28:26 AM)

Thanks Peter,

I saw the posts online and integrated a version that handles the wheel scroll.
It seems stable on the 32 version but it crashes on the 64-bit version of Access. Maybe something's wrong with the APIs even though the version I am using has APIs and functions dedicated to 64-bit.

The wheel scroll inside the pro version works correctly with Access 64-bit?

How much is the license and can I include your code in a commercial application?

Thanks in advance
Erlind

 


Comment by: Peter Thornton (3/11/2017 7:34:59 PM)

Hi Erlind, sorry for late reply, I was away last week.

Most of the posts on-line with wheel scroll for MSForms controls in x64, are adaptations of an approach for wheel scroll I posted many years ago for x32. I haven't looked at all of them but I haven't seen any that are likely to work in x64.

The best way for you to find out if wheel scroll works in the x64 version of the 'pro' treeview is to try it!

Your last question, unlike the free the treeview the code is packaged like a 'control' in an xlam or accde type file, and yes you can re-distribute it with a license, all explained in the demo.

 


Comment by: Erlind (3/13/2017 8:58:14 AM)

Thank you for your reply Peter,

I think that's the best option.

The demo will give me time to test the tree in my production environment for stability and compare it with the free version.

Can you please tell me how to get the demo?

Thanks in advance
Erlind

 


Comment by: Peter Thornton (3/13/2017 6:55:50 PM)

Hi Erlind,

There are links for the 'pro' Treview and ListGrid on the main Treeview page, scroll up and see the links on the left. However I've taken the liberty of contacting you directly.

 


Comment by: Zoltan Attila (3/14/2017 3:04:28 PM)

Hello there,

first of all I want to thank you sharing your wunderfull treeview.
I would like to enable the doubleclick and the mouseup events but I'm not able to do it... Could you please help me?

By double click event I want to change the Icons and add some comments to the Nodes

By mouse up event I would like to implement a dynamic popup menu in the treeview depending if I right click on the root or on a node.

Thank you in advance for your help.

 


Comment by: Jan Karel Pieterse (3/14/2017 4:27:57 PM)

Hi Zoltan,

Have a look at this comment, perhaps it helps?

http://www.jkp-ads.com/articles/treeview01.asp?AllComments=True#19932

 


Comment by: Roberto (3/30/2017 11:48:45 PM)

I have a problem to change the picture into Forms.Frame.1 class

I choose picture property for XPMinus, after the program open the folder for select the image and save all, the showing drawing non change.

what can I do ?

many thank's

 


Comment by: Peter Thornton (4/1/2017 10:20:15 AM)

Hi Roberto,
In Access it can be difficult to add or change the 'picture' property in Image controls on the Frame.

Simple way is to Cut the Image control from the Access Frame, and paste it into a frame or even directly onto a userform in Excel; add or change the the 'picture' property in Excel, then copy the Image control back to the Access Frame.

You can create all your Images in Excel, then copy all in one go to the Frame in Access.

 


Comment by: filou07 (5/12/2017 11:15:17 AM)

How can I count all children and sub-sub-sub...children of a node ?

 


Comment by: Ashish (5/12/2017 11:24:48 AM)

Hello everyone,

Is this possible to add multiple columns in tree view.

 


Comment by: Peter Thornton (5/13/2017 5:08:09 PM)

Hi Ashish,
It is possible but means significantly adapting the treeview code. I've seen various attempts but none that appear to work very well. Better to display columns of data in an adjacent listbox/listview, or our ListGrid. Look at Windows Explorer, with a treeview in the left panel and list on the right.

 


Comment by: Peter Thornton (5/13/2017 5:14:38 PM)

@ filour07

Try this recursive routine to count all child and grandchild nodes

Sub recChildNodesCount(cNode As clsNode, count As Long)
Dim cChild As clsNode
    If Not cNode.ChildNodes Is Nothing Then
        For Each cChild In cNode.ChildNodes
            count = count + 1
            recChildNodesCount cChild, count
        Next
    End If
End Sub

Call it like this

Dim count As Long
    recChildNodesCount mcTree.ActiveNode, count
    MsgBox count

 


Comment by: Charles Vanders (8/11/2017 8:14:55 AM)

Thank you for filling the gaping hole left by a crappy microsoft decision to leave treeview users high and dry!

Question:

How do you add a child to a node when you only know its key?

ie how do I search whole collection for a specific node by key?

 


Comment by: Peter Thornton (8/11/2017 4:07:59 PM)

Hi Charles,
Thanks, glad you like it. FWIW the 'pro' Treeview has moved on a long way!

Your last Q first followed by two ways to add a new node


Set cParent = mcTree.Nodes("parentKey")
Set cChild = cParent.AddChild("newKey1", "New node1")
' or
Set cChild = mcTree.NodeAdd("parentKey", tvChild, "newKey2", "New Node2")

 


Comment by: yossi oulu (8/24/2017 6:32:09 PM)

Hello,
Is it possible to place the tree view control on top of the worksheet instead of on a user form?
If yes, do you have an example?
Thank you
Yossi

 


Comment by: Jan Karel Pieterse (8/25/2017 1:14:24 PM)

Hi Yossi,

I've had one sample of someone who tried it. But I think too much will need to be adjusted to make it work reliably.
It isn't very hard to create a userform instead I'd say?

 


Comment by: Yossi (8/25/2017 5:33:27 PM)

Yes, I guess this is OK
Thanks a lot
Yossi

 


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