#Excel treeview code

For that I sincerelyĮnough to make the necessary adjustments to incorporate the treeview inĮnsured the treeview also works on MAC Office 2011, Thanks Ron! Copyright and LicensingĪll code in the treeview is (c) JKP Application Development ServicesĪnd Peter Thornton (the Authors).

Lots of functionality would not have been available now. However, without the help of my friend and fellow (former) Excel MVP Peter Thornton, The basic plumbing and code structure of this treeview control was devisedīy me. Screenshot of treeview on Mac Excel 2011 Acknowledgements In some earlier versions though you would need to adapt the demos made for Our Treeview works in all versions of Excel, Access and Word from OfficeĢ007 to 2019 and 365, for Windows and Mac, 32bit and 64bit.

#Excel treeview how to

How to put it to use in your own VBA project. In the following pages I'll show what our treeview can do and explain Two treeviews, left: VBA tree, right: Common controls tree One (right) side-by-side in their simplest display mode (read on, thereĪre even prettier screenshots further down the page): The screenshot below shows both our new Treeview (left) and the Windows A couple of weeksĪfter I started building this I tricked Peter Treeview 'control', that only uses the native Office forms controls.

#Excel treeview 64 Bit

The standard Treeview control, like all non built-in ActiveX controls,Ĭannot be used in 64 bit versions of Office.Įspecially the second point convinced me to develop a custom-made.

Replacing both, but at worst the user's Excel can crash when trying Atīest it could entail removing both the control and the reference and

#Excel treeview 32 bit

People using 32 bit Windows will almost certainly have problems. If you distribute a file that was saved in 64 bit Windows,Ĭontaining one of the 'Microsoft Windows Common Controls 6.0' (The TreeviewĬontrol is one of them) and with the reference set to 'mscomctl.ocx',

Compile errors due to a difference in how the control librariesĪre registered in 32 bits Windows' System32 and 64 bit Windows' SysWOW64įolders.

There are a couple of problems with this Treeview control: Section, then you know what a versatile control this is to show hierarchically If you have ever used the Treeview control from the 'Additional controls' So I used this just to make the screen repaint itself through code (couldn't find an Application/Sheet/Workbook property to repaint the Sheet).An MSForms (all VBA) treeview for Access, Excel And Word Content Not to mention when you add a item to a node it will expand that node on the rebuild to show the new data.ĭon't mind the following code in the ThisWorkbook module:Īpplication.DisplayScrollBars = True When I opened the workbook (before this code) the treeView had HUGE font sizes in it for some odd reason.but if I scrolled down it would be fine. Remove it if you like, but I feel it's more user-friendly like this.people save workbooks knowing they won't close it for a while, so I'm sure they'd like to keep seeing what they were looking at before they saved it.

I've also added a procedure that stores the expanded nodes in a new worksheet 'Tracking' and when you save it will reload the tree to keep them from all expanding and then run a procedure called 'SetNodes' to keep the data looking like it was when you saved / closed the workbook. That's really strange to me, what could be causing the workbook to change the Saved property back to False? (I checked the event in real-time after setting the property to True, and it displayed False) He forgot Me.Saved = True at the end of the BeforeSave procedure (maybe because of the 'LoadTree' call)'.but even with ThisWorkbook.Saved = True when I pressed close, it still asked me if I wanted to save and if I pressed save it will repeatedly ask that. When I try closing the workbook, it asks me if I want to save even after I pressed save.