All-VBA treeview

[Jan Karel Pieterse]

Hi All,

As mentioned here, I've been working on replacing the common controls treeview with an all-VBA alternative.

Along with Peter Thornton this project has now reached an important milestone: the beta release.

Have a look at http://www.jkp-ads.com/articles/treeview.asp" onclick="window.open(this.href);return false;

[HansV]

Hi Jan Karel,

I just looked at it briefly, but I'm already impressed! I'll test it more thoroughly and let you know how it goes.

[HansV]

I created a userform with your treeview and populated it with data from an Access database. It worked very well, and setting it up was actually easier than with the ActiveX treeview control (after a bit of experimentation)!
Responsiveness is excellent.
This is going to be a very useful control.

[Jan Karel Pieterse]

Thanks Hans! Can you post a picture of your tree?

[HansV]

I'll do that later today - the database and workbook are on another computer than the one I'm using now.

[HansV]

The example is created from a rather simple demo database I already had, with two tables:

x1444.png

x1445.png

The tables are related like this:

x1446.png

Code to populate the treeview (it requires a reference to the Microsoft Office 14.0 Access database engine Object Library, or to DAO if you use a .mdb instead of a .accdb):

Code: Select all

Private Sub UserForm_Initialize()
    ' The root of the tree
    Dim cRoot As clsNode
    ' Three levels of nodes
    Dim cNode1 As clsNode
    Dim cNode2 As clsNode
    Dim cNode3 As clsNode
    ' Database variables
    Dim dbs As DAO.Database
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim rst3 As DAO.Recordset
    'Instantiate a new instance of the treeview class and set a module level variable to hold it:
    Set mcTree = New clsTreeView
    With mcTree
        ' Pass frame to the TreeControl of the treeview class
        Set .TreeControl = Me.frmDemo
        ' Title for message boxes:
        .AppName = "TreeView Demo"
        ' Set some properties
        .CheckBoxes = False
        .RootButton = True
        .LabelEdit = 0
        .Indentation = 20 * 0.75
        .NodeHeight = 16 * 0.75
        .ShowLines = True
        ' Add a Root node with main and expanded icons and make it bold
        Set cRoot = .AddRoot("Root", "Classes", "FolderClosed", "FolderOpen")
        cRoot.Bold = True
        ' Open the database
        Set dbs = DBEngine.OpenDatabase("C:\AccessSamples\Treeview.accdb")
        ' Open recordset that returns the unique dates from tblClasses
        Set rst1 = dbs.OpenRecordset("SELECT DISTINCT StartDate FROM tblClasses", dbOpenForwardOnly)
        ' Loop through the dates
        Do While Not rst1.EOF
            ' Add node
            Set cNode1 = cRoot.AddChild(sKey:="D" & rst1!StartDate, sCaption:="Date " & rst1!StartDate)
            ' Open recordset that returns the classes for each date
            Set rst2 = dbs.OpenRecordset("SELECT ClassID, ClassNumber FROM tblClasses WHERE StartDate=#" & _
                Format(rst1!StartDate, "mm\/dd\/yyyy") & "# ORDER BY ClassID", dbOpenForwardOnly)
            ' Loop through the classes
            Do While Not rst2.EOF
                ' Add node
                Set cNode2 = cNode1.AddChild(sKey:="C" & rst2!ClassID, sCaption:="Class " & rst2!ClassNumber)
                ' Open recordset that returns the students for each class
                Set rst3 = dbs.OpenRecordset("SELECT StudentID, StudentFullName FROM tblStudents WHERE StudentClassID=" & _
                    rst2!ClassID & " ORDER BY StudentFullName", dbOpenForwardOnly)
                ' Loop through the students
                Do While Not rst3.EOF
                    ' Add node
                    Set cNode3 = cNode2.AddChild(sKey:="M" & rst3!StudentID, sCaption:=rst3!StudentFullName)
                    rst3.MoveNext
                Loop
                rst3.Close
                rst2.MoveNext
            Loop
            rst2.Close
            rst1.MoveNext
        Loop
        Set rst3 = Nothing
        Set rst2 = Nothing
        rst1.Close
        Set rst1 = Nothing
        dbs.Close
        Set dbs = Nothing
        ' Create the node controls and display the tree
        .PopulateTree
    End With
End Sub

This is the resulting treeview in Excel:

x1447.png

[Jan Karel Pieterse]

Hi Hans,

Very nice, thanks. Would it be possible to include your example file with our download?

[HansV]

The database contains other things; I will create a separate database with those two tables later today, zip it together with the workbook and attach it to a reply. You can use it as you like.

[Jan Karel Pieterse]

Thanks Hans, much appreciated.

[HansV]

The example originally was a .xlsm workbook that connected to a .accdb database.
To make it possible for more users to view it, I have changed it to a .xls workbook that connects to a .mdb database.
The attached zip file contains the workbook and the database; both should be unzipped to the same folder.
Macros in the workbook must be enabled for the userform to work, obviously.

TreeView_Access.zip

[Jan Karel Pieterse]

Thanks Hans, I'll have a look!