An MSForms (all VBA) treeview

Pages in this article

Examples

This page displays some examples where our treeview has been used.

RefTreeAnalyser

Our RefTreeAnalyser also implements the treeview control, as you can see here:

Screenshot of RefTreeAnalyser, the ultimate Excel formula auditing tool.

The City Of Amsterdam

The Engineering Desk of the City of Amsterdam has implemented our Treeview in a VBA driven tool which is used by the technical designers of the Engineering desk so their drawings comply with the Dutch CAD standard NLCS. This is what the treeview looks like in their software:

Screenshot of technical drawing tool.

Extended Project Explorer

(by Peter Thornton)

The purpose of this file is to show a few ways how the VBA Treeview can be used in a real life app. The treeview lists projects and modules similar to the VBE's (Visual Basic Editor) Project Explorer, but the branches are extended to list procedures and declarations. These can be double clicked and in turn activated in the VBE.

Other features include routines to search the treeview, return details of the treeview to a sheet to document projects, and more. Please refer to the main demo files for the most recent version of the VBA Treeview.

It looks like this:

Screenshot of Extended Project Explorer.

Access and Word users: please note documentation is included in the main Excel demo.

Download The Extended Project Explorer V1.0 (Updated Dec 04, 2013, downloaded: 6.792times)

Access Archon article

Description: Helen Feddema from Access Watch published an Access Archon article with a sample .accdb database using the Treeview alternative that works in 64-bit Office; also includes a sample .mdb database that uses the native Treeview control.

Access database analyzer utility

This is a screen-shot provided to us by John Bourne of the object relationships tree in his Access project analyzer utility

Screenshot of object relationships tree

XER toolkit

The XER Toolkit is an MS Excel based application that can read, display and analyse data from Oracle's Project Management application, Primavera. The toolkit helps to improve the quality of project schedules by providing visibility without the requirement for (expensive) Primavera licences. As an enterprise system, the data within Primavera is hierarchically arranged and so the Treeview developed by JKP has become an integral and essential part of the toolkit's data access functionality. See the video. One of the screens in the tool:

Treeview data selector

Spanish diseases classification list

From Diego Pereira: This treeview loads the International Classification of Diseases in Spanish. By entering a word, a part of a word or a ICD10 code, one can locate matching nodes, highlight them and expand them. The main purpose of this example is to serve as a simple implementation of the Treeview in an Access style.

Download the sample Access accdb database (05 Mar 2014, downloaded 5.019 times)

Treeview showing Spanish Diseases classifications

Sheet navigation tool

Yves Leboutte sent us these screenshots:

Go To sheet: Allows to jump to worksheet < List of countries
Jump to sheet

Go To Item: To jump to 1 item (rows)
Jump to item

Item Selection: Allows to save options to some items in 1 or all childs sheets.

Filtered tree

Tiago Costa from EWEN Energy shared a treeview with us which the user can filter. The filtering is implemented quite simple by filtering the table containing the source data for the tree and rebuilding the tree after filtering using the filtered table (in a Worksheet). I particularly like the looks of the form, very "modern UI"-like:

Nice form with filtered tree

Template system

FREE open source Access database for IT Asset Management purposes

Florian Rossmark created an IT Assets database, a free open source IT Assets management database project based on Microsoft SQL server as a back end and Microsoft Access as front end. The Wiki module in the database was accomplished by using the TreeView control for easy navigation through structured data controlled by the actual end user.

Peter Denney's showing a popup with more information pertaining to a node

Your implementation

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.

Comments

All comments about this page:

Comment by: Steve (28-2-2014 14:09:10) deeplink to this comment

I am needing to use a treeview control for Access 2013 64 bit on a Windows 8.1 machine. Will this solution work for me? Do you think that MS will continue the MSForms for a while?

Comment by: Jan Karel Pieterse (28-2-2014 14:10:46) deeplink to this comment

Hi Steve,

Yes, this method should work just fine (The MSACCESS demo file should work in 64 bit Access 2010 and 2013).
MSFORMS controls are definitely going to be around for a considerable amount of time.

Comment by: ANmar (12-9-2014 23:28:51) deeplink to this comment

Hi
I am using the Treeview for few days now
Great job and I have to raise my hat

Few things though
1- I was able to add the Double-click on node to expand it since I use that a lot, will send you the details later on
2- I need to know how will I send the NodeClick event to select a certain node in code, and not in mouse

Thanks and great job again

Comment by: Jan Karel Pieterse (13-9-2014 17:13:58) deeplink to this comment

Hi Anmar,

1: Thanks!
2: You can simply set the ActiveNode of the treeview. Suppose your node is called "ThisNode" and the variabole holding the treeview mcTree:

Set mcTree.ActiveNode = ThisNode

Comment by: Tarek Hegazy (10-4-2015 18:28:56) deeplink to this comment

Great Job. Is it possible to get the source code for the Filtered Tree. Many Thanks.
Tarek

Comment by: Peter Thornton (13-4-2015 11:03:33) deeplink to this comment

Hi Tarek,
What do you mean by "filtered tree"?

Comment by: Keven Kemege (14-4-2015 22:33:36) deeplink to this comment

is there an example of a recursive function that would process a parent child hierarchy file like the legacy Tree view Addbranch function?

'======== AddBranch Sub Procedure =========================
' Recursive Procedure to add branches to TreeView Control

thanks for any help that you could provide me on this subject.

Keven Kemege

Comment by: Peter Thornton (16-4-2015 18:32:34) deeplink to this comment

Hi Keven,
What "AddBranch" function are you referring to which treeview are you referring to as the "legacy" treeview, AFAIK there is no such built-in function in the mscomctl.ocx version.
Adding nodes recursively according to the data is normally straightforward but perhaps explain the objective behind your question.

Comment by: Keven Kemege (27-4-2015 23:50:07) deeplink to this comment

Peter Said:
What "AddBranch" function are you referring to which treeview are you referring to as the "legacy" treeview.

Keven Said:
just Google AddBranch and treeview and you'll find a site with a recursive addbranch Visual Basic function written to work with an Access Data base form + table/query dataset and the mscomctl.ocx.
I tried to revise this code with the JKP treeview and have failed to duplicate the treeview for my parentID; text; childID formatted data set.
Can't seem to get the branching levels set recursively...

Comment by: Peter Thornton (28-4-2015 11:57:01) deeplink to this comment

Hi Kevin,
I take it you mean the example in kb/209891. You didn't explain why you are having difficulties adapting it, does the original work for you?
At a glance, I don't have a suitable Access file to test it with, apart form the obvious references it looks like the only thing you need to change is

objTree.Nodes.Add()
to
mcTree.NodeAdd()

Also change tvwChild to tvChild or 4

Although node.AddChild is more efficient we included the treeview.NodeAdd method to simplify porting code. Refer to the documentation.

Comment by: Keven Kemege (28-4-2015 18:01:17) deeplink to this comment

Got this recursive function to work with these changes:

'================= AddBranch Sub Procedure =========================
     '     Recursive Procedure to add branches to TreeView Control

     Dim cParentNode As clsNode ' altered for JKP
     Dim cChildNode As clsNode    ' altered for JKP

     If IsMissing(varReportToID) Then
        strCriteria = parent_ID & " Is Null"
     Else ' Search for records pointing to parent.
        strCriteria = BuildCriteria(parent_ID, rst.Fields(parent_ID).Type, "=" & varReportToID)
'Article ID: 209891 Set nodParent = objTree.Nodes("a" & varReportToID)
                     Set cParentNode = mcTree.Nodes(CStr(varReportToID)) ' altered for JKP
     End If

        If Not IsMissing(varReportToID) Then ' Add new node to the root.
'Article ID: 209891 Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText)
                     Set cChildNode = mcTree.NodeAdd(cParentNode, tvChild, strKey, strText) 'Peter Thornton & altered for JKP

        Else 'add new node to the parent
'Article ID: 209891 Set nodCurrent = objTree.Nodes.Add(, , strKey, strText)
                     Set cChildNode = mcTree.NodeAdd(, , strKey, strText) 'Peter Thornton & altered for JKP

        End If

thanks to Peter Thornton & Jan...

Comment by: Jan Karel Pieterse (29-4-2015 07:41:21) deeplink to this comment

Hi Keven,

Thank you!

Comment by: Salim (18-11-2015 17:44:34) deeplink to this comment

Hi!

Please if possible can you explain briefly how to achieve parent child relationship of Class modules as with the case of your TreeView Control.

Thank you very much.

Comment by: Jan Karel Pieterse (19-11-2015 09:15:20) deeplink to this comment

Hi Salim,

What it takes is a variable (property) of type collection in the parent class to which all child classes are added and an object variable (property) in the child class that gets an instance of the parent class.

In a class called clsParent:

Option Explicit

Private mcolChildren As Collection

Private Sub Class_Terminate()
    Set mcolChildren = Nothing
End Sub

Public Property Get Children() As Collection
    Set Children = mcolChildren
End Property

Public Property Set Children(colChildren As Collection)
    Set mcolChildren = colChildren
End Property

Public Sub AddChild(cChild As clsChild)
    Set cChild.Parent = Me
    If mcolChildren Is Nothing Then Set mcolChildren = New Collection
    mcolChildren.Add cChild
End Sub

In a class called clsChild:

Option Explicit

Private moParent As clsParent

Private msName As String

Private Sub Class_Terminate()
    Set moParent = Nothing
End Sub

Public Property Get Parent() As clsParent
    Set Parent = moParent
End Property

Public Property Set Parent(oParent As clsParent)
    Set moParent = oParent
End Property

Public Property Get Name() As String
    Name = msName
End Property

Public Property Let Name(ByVal sName As String)
    msName = sName
End Property

ANd in a normal module:

Option Explicit

Sub Demo()
    Dim cParent As clsParent
    Dim cChild As clsChild
    Set cParent = New clsParent
    Set cChild = New clsChild
    cChild.Name = "John"
    cParent.AddChild cChild
    Set cChild = New clsChild
    cChild.Name = "Peter"
    cParent.AddChild cChild
    For Each cChild In cParent.Children
        MsgBox cChild.Name
    Next
End Sub

Comment by: Bennett Stewart (16-2-2016 14:01:52) deeplink to this comment

Is there a way to hide checkbox for root node but show checkboxes for child nodes?

Comment by: Jan Karel Pieterse (16-2-2016 17:48:49) deeplink to this comment

Hi Bennett,

I'm afraid as we implemented it it's an all-or-nothing setting for the entire tree.

Comment by: Åsa (16-3-2016 17:13:14) deeplink to this comment

Hi,

I downloaded your treeview control today because I needed to make an application work on 64-bit Word. I have managed to make most of my application work as it does on 32-bit with the Common controls treeview. Only one detail remains.

When the userform loads, all subfolders of a specific folder are listed in the treeview control. As the nodes are added I also check if the nodename matches the latest selected folder (saved from the previous visit of the user). I there is a match I save the index number of the node and when all nodes have been added I would like to select the node with the saved index.

This is the code used with the common control treeview

trvFolders.Nodes.Item(intIndex).Selected = True

Is there a way to achieve the same result in your treeview?

Best regards,
Åsa

Comment by: Jan Karel Pieterse (16-3-2016 17:31:16) deeplink to this comment

Hi Åsa,

That works slightly different from the common controls treeview indeed, by setting the activenode:

Set trvFolders.ActiveNode = trvFolders.Nodes(intIndex)

Comment by: Daniel (19-4-2016 14:08:29) deeplink to this comment

Hi, how can i make, please, for enabled my "context menu" when i clic in the text of node ? Only when i clic in then "blank" of frame permits to see my menu.

Thank's

Best regards
Daniel

Comment by: Jan Karel Pieterse (20-4-2016 06:25:30) deeplink to this comment

Hi Daniel,

It is as simple as adding this to your userform:

Private Sub mcTree_Click(cNode As clsNode)
Application.CommandBars("Cell").ShowPopup
End Sub

Of course you need to replace "Cell" with the name of your commandbar.

Comment by: Daniel (21-4-2016 11:04:31) deeplink to this comment

Thank's more Jan for your response but my problem is another. My problem comes of the event "frTreeControl_DblClick" : when i double-click on the text of the node, nothing happens, on the other hand when i double-click right off the text of node the events occurs.
I do not manage to understand.
I apologize for my english

Thank you very much beforehand.

Best regards
daniel

Comment by: Jan Karel Pieterse (22-4-2016 16:39:18) deeplink to this comment

Hi Daniel,

The double-click event of a node is currently reserved for going into label edit mode. You can find the event in clsNode:

Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

To add a double-click event of the treeview to your own project you need a couple of steps, which I have described in a comment on this page:

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

Look for this comment:

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

Comment by: Life (14-6-2016 09:39:06) deeplink to this comment

Hi there

thank you so much for sharing your code and effort
with the world.

I am devlepoing a Ms Access in a Right to Left enviorment,
Thererfore I am wondering whether there is a simple way to define the TreeView as right to left display?

Comment by: Jan Karel Pieterse (14-6-2016 10:31:10) deeplink to this comment

Hi Life,

Wow, that isn't easy in our current implementation. The entire control is indeed designed to be left-to-right.
You'd have to redo all assignments in clsTreeView that contain ".Left = ......." and make them direction sensitive.
Maybe something I can ask you to do? :-P

Comment by: Peter Thornton (14-6-2016 14:09:07) deeplink to this comment

Hi Life,

Indeed not easy, that said nothing is impossible. I suspect the biggest challenge would be catering for variable widths when the scrollbars are implemented for a left-right environment, or do they also automatically switch in right-left?

Otherwise if the entire 'insidewidth' is fixed at design it might be easier, still a fair amount of work though!

Comment by: David Bickerton (22-7-2016 17:22:26) deeplink to this comment

Hi,

I am using MSForms (All VBA) Treeview - it is great so far - thanks for all the help.

I am having issues with SETTING new PARENT NODES within the code ? Example : How can I do this when I have 10 NODES all at Level 0. Then I want one of these Nodes to move to the sibling of one of the others that already exist ? I am assuming I do something like

Set mcTree.Nodes(strKey).ParentNode.Key = "117"

But I am having problems.

Thanks,

David

Comment by: Peter Thornton (1-8-2016 15:39:01) deeplink to this comment

Hi David,

The treeview includes a 'move' method, eg

mcListMove cSource, cDest

..where cSource is the node you want to move and cDest is the new parent.

See "Move" in the documentation in the Excel demo (clsTreeview) if you want to place before or after some node.

Comment by: Steven Wade (28-7-2017 15:54:18) deeplink to this comment

Do you have any code examples that show how to synchronize the data record displayed on an MS Access form based on the value selected in the tree control (subform) on that same page? (similar to how linking master and child fields allows form/subform synchronization)

Comment by: Peter Thornton (31-7-2017 08:12:58) deeplink to this comment

Hi Steven,

To trap the treeview's click event and whatever Access form event traps the selected data record and the events select or activate the reciprical item, something like this:

Private Sub mcTree_Click(cNode As clsNode)
    ' activate associated data record
End Sub

'In the Access form event
k = the KeyOrIndex associated with selected data item

    Set mcTree.ActiveNode = mcTree.Nodes(k)
    mcTree.ScrollToView mcTree.ActiveNode

Comment by: Giancarlo (3-7-2018 12:43:38) deeplink to this comment

Changes required for Excel 2016 64 bit Windows 10
-------------------------------------------------------

''#If Win64 Then
    Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" ( _
                                            ByVal hwnd As LongPtr, _
                                            ByVal nIndex As Long, _
                                            ByVal dwNewLong As LongPtr) As Long 'Ptr - Modify
-------------------------------------------------------
#If VBA7 Then

Private Declare PtrSafe Function SetParent Lib "user32" ( _
                                            ByVal hWndChild As LongPtr, _
                                            ByVal hWndNewParent As LongPtr) As Long 'Ptr - Modify

Comment by: Fungencio (15-11-2018 18:59:30) deeplink to this comment

Hello there,

I have been looking for a Treeview like yours, it's such an excellent work!
I wonder if it would be possible to link the Excel VB Treeview data that we add (input) to a Data Validation list cell, by different levels
For example:
The macro is run and then we start adding nodes as:

-Node 1
- Subnode 1.1
- Subnode 1.2
-Node 2
- Subnode 2.1
-Node 3

At the same time we have 2 data validation list cells in which:

-In the first cell its shown the first level (Node 1/Node 2/Node 3)
-In the second cell, depending on what is selected in the first one it will show:
(If Node 1 is selected in the first cell, it will show Subnode 1.1/Subnode 1.2...etc

I hope you understand
Thanks a lot!

Comment by: Jan Karel Pieterse (16-11-2018 08:09:03) deeplink to this comment

Hi Fungencio,

Perhaps this helps: https://www.contextures.com/xlDataVal02.html

Comment by: mohammed (14-2-2019 10:18:58) deeplink to this comment

Hello there,

I have been looking for a Treeview like yours, it's such an excellent work!
I want to create an interface Like your Example "The City Of Amsterdam" But I need To load Data From Sheets
I created the parent and child node but i can't load the child child node form second sheet
I hope that you can help me
Thank's a lot

Comment by: Peter Thornton (15-2-2019 14:53:29) deeplink to this comment

Hi Mohammed,

We don't have the source code of the "City of Amsterdam" example but it's normal Treeview. Without knowing anything about what you are working with it's hard to give any suggestions, but follow how the demos work and refer to the documentation* and hopefully it will all come together.

* if using the Access version get the Excel download for documentation

Comment by: RTaillon (10-9-2019 21:18:00) deeplink to this comment

I'd love to get the POPUP ability...!! Is there a way/place to get the code for this?!

RT

Comment by: Jan Karel Pieterse (11-9-2019 09:32:00) deeplink to this comment

Hi RT,

The way to do that is to enable the MouseUp or -Down event in clsNode and to use that by using the PopUp method of a custom commandbar you create for that purpose.
See comment block in the routine "NodeEventRouter" in clsTreeView to get an idea of what to do.

Comment by: J Jones (31-10-2019 18:36:00) deeplink to this comment

Hi,

Thank you for this, it is very helpful!

Is there a way to output the 'Key' values of all selected nodes(checkboxes) to a given Excel cell?

Thank you

Comment by: Peter Thornton (31-10-2019 20:09:00) deeplink to this comment

Hello J Jones

Adapt the example in the Excel Demo. First remove a couple of stray lines that got left in near the top of the 'Dump Data' button, search Private Sub cmdGetData_Click()

mcTree.ActiveNode.Key = Now < remove
Exit Sub < remove

Edit the GetData1 routine just below the below the cmdGetData_Click routine

Change
    lCt = lCt + 1
    rng(lCt, lLevel) = cParent.Caption
to
If cParent.Checked = -1 Then
        lCt = lCt + 1
        rng(lCt, 1) = cParent.Key
    End If

If you're using Access you will need to automate Excel

Comment by: J Jones (1-11-2019 12:29:00) deeplink to this comment

Thank you so much for your quick response & your generosity!

Comment by: miguel angel Molina (26-12-2019 09:46:00) deeplink to this comment

POPUP IN NODE

Comment by: Lakazet (27-10-2020 21:20:00) deeplink to this comment

Hi guys, my treeview on excel x86 working as well but I want running it on x64, anybody help me for follow codes:

Private Sub CBAddRoot_Click()

    Dim rngData As Range
    Dim lngRow As Long
    Dim objNode As Node
    Dim objRootNode As Node
    Dim obj As Node
    Dim ch As Node
    Dim strPrevName As String
    Dim lngCount As Long


    Set rngData = ActiveSheet.Range("A1:D56")

    strPrevName = ""
    lngCount = 0
    For lngRow = 1 To rngData.Rows.Count
        If rngData.Cells(lngRow, 1) <> strPrevName Then
            lngCount = lngCount + 1
            Set objRootNode = TreeView1.Nodes.Add(, , "Key" & lngCount)

            objRootNode.Text = rngData.Cells(lngRow, 1)

        End If

            lngCount = lngCount + 1

         Set objNode = TreeView1.Nodes.Add(objRootNode, tvwChild, "Key" & lngCount)

        objNode.Text = rngData.Cells(lngRow, 2) & " " & rngData.Cells(lngRow, 11).Text

         lngCount = lngCount + 1

        Set obj = TreeView1.Nodes.Add(objNode, tvwChild, "Key" & lngCount)

        obj.Text = rngData.Cells(lngRow, 3) & " " & rngData.Cells(lngRow, 12).Text

     lngCount = lngCount + 1

     Set ch = TreeView1.Nodes.Add(obj, tvwChild, "Key" & lngCount)

        ch.Text = rngData.Cells(lngRow, 4) & " " & rngData.Cells(lngRow, 13).Text

        strPrevName = rngData.Cells(lngRow, 1)

    Next


End Sub

Comment by: Jan Karel Pieterse (28-10-2020 09:30:00) deeplink to this comment

Hi Lakazet,

There is nothing in your code that I would expect to cause an issue in 64 bit Excel. What error(s) do you get?

Comment by: Lakazet (28-10-2020 13:24:00) deeplink to this comment

Hi jan, I import this code in userform (ufDemo) and add button for that, but when running ufDemo and press button some error occurred for example undefined variables types that I fix it with clsnode instead Node, but not recognized text and as it, I dont know how solve it

Comment by: Jan Karel Pieterse (28-10-2020 13:44:00) deeplink to this comment

Hi Lakazet,

You'll have to study the demo file and use the syntax explained there. If your system does not recognize clsNode then I suspect you haven't copied the class module clsNode?

Comment by: Lakazet (28-10-2020 14:26:00) deeplink to this comment

yes I do it but since I am a beginner, I didn't understanding

Comment by: Jan Karel Pieterse (28-10-2020 15:21:00) deeplink to this comment

Hi Lakazet,

If you're willing to pay for some consulting we might me able to help out?

Comment by: karim (31-3-2022 07:01:00) deeplink to this comment

treeview to access

Comment by: Jan Karel Pieterse (31-3-2022 10:28:00) deeplink to this comment

Hi Karim,

What is your question please?

Comment by: Meindert (25-6-2022 22:34:00) deeplink to this comment

Hi there,

Nice tool!

Is there a place where I can add standard (user defined) colors for checked and unchecked nodes?

Greetings
Meindert

Comment by: Jan Karel Pieterse (26-6-2022 16:05:00) deeplink to this comment

Hi Meindert,

Not at my pc right now, but you could look in the clsTreeview class to see what it does when a node gets checked or unchecked. It should affect the relevant nodes and change their properties. There you can edit the code to set coloring accordingly.

Comment by: Bùi Van S? (23-1-2023 18:06:00) deeplink to this comment

Hi,
I'm from Vietnam. I downloaded the file "Treeview_ExtProjExpl" and used it. However, when extracting and clicking the "Extended Project Explorer" button has an error message like this: "Compile error: Type mismatch." And the VBE window points to the structure

Function SwitchFormParent(sFormCap As String, sWindow As String, bNewForm As Boolean) As Boolean

is highlighted in yellow, the line

lRet = SetParent(mhWndForm, GetDesktopWindow)

is highlighted in blue.
I have read on many forums but there is no solution.
Hope to be of help. Thank you!

P/s: I use Windows 11 and Office 365, 64 bit.
My English is very limited, so I used Google Translate to help. Hope that the shortcoming will be understood and forgiven.

Comment by: Jan Karel Pieterse (24-1-2023 08:59:00) deeplink to this comment

Hi Bùi,

It is because the declaration of the variable lRet is not 64 bit Office compliant. Move its declaration to the conditional compile block immediately below where it is currently declared, like so:

#If VBA7 And Not Mac Then
    Dim lRet As LongPtr
    Dim hWndFGnd As LongPtr
    Dim hWndExcel As LongPtr
    Dim hWndParent As LongPtr
    Dim hWndVBE As LongPtr
#Else
    Dim lRet As Long
    Dim hWndFGnd As Long
    Dim hWndExcel As Long
    Dim hWndParent As Long
    Dim hWndVBE As Long
#End If

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.