Home » Forums » Open Source Learning Center » Learning requests

Tree View in excel 2007

Submitted by deepakjipaulson on 23 July, 2011 - 13:43

Hi !!!

I need to populate a tree view in excel 2007
without using pivot / group option

Here is my requirement :

I need to present - stock ageing report...
the hirearchy goes this way...
few franchise_Vehicle Type_Model_Variant_Age of the stock in days_ ... few more...

I think the only solution will be a tree view ...similar to the one in access..

Hope .this time . I have placed my question right..

is this possible without a pivot table or grouping option ..?????

Thanks
Dj

‹ Fixed Annual/Semi-Annual Bills Better Coding ›
»

Joint with there

Submitted by alwi abdurrahman on 16 September, 2014 - 02:51.

I like this forum, for increasing my idea, in developt system which more better.
Thanks you

»
Vishesh's picture

Working with Treeview

Submitted by Vishesh on 23 July, 2011 - 13:50.

Working with Treeview involves VBA. Don't know if that's possible without VBA.

»

Tree view

Submitted by deepakjipaulson on 23 July, 2011 - 13:57.

No matter ... using VBA is welcome...
But i am not familier with the coding...
could u pls. guide

»
Vishesh's picture

Sample Treeview Code

Submitted by Vishesh on 23 July, 2011 - 14:43.
Here is the sample treeview code. Try to understand the code and build on it. Insert a treeview on you excel sheet. The name of the treeview control here in the code is Treeview1; change the name in the code accordingly. Paste this code in any general module 
Sub SampleTreeViewCode()
    Dim tvw         As TreeView
    Dim ndParent    As Node
    Dim ndChild     As Node
 
    Set tvw = Sheet1.TreeView1
 
    tvw.Nodes.Clear
    Set ndParent = tvw.Nodes.Add(, , , "Level1")
    Set ndChild = tvw.Nodes.Add(ndParent, tvwChild, , "Level2")
    ndParent.Expanded = True
 
    Set tvw = Nothing
    Set ndParent = Nothing
    Set ndChild = Nothing
End Sub
This is the event to be tracked. In the design mode double click on the treeview control to and write the code as below. 
Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
    MsgBox Node.Text
End Sub
»

Tree view

Submitted by deepakjipaulson on 24 July, 2011 - 06:04.

Hi Vishesh,

Awesome.. the code works fine...
I am able to create Level 2, 3... so on...
Now the problem is that.. it doesnot expand/collapse

when the file is closed and opened .. the designed treeview disappears (the sample node opens)
which becomes ok.. after running the macro and saving the file twice or thrice...

thnks

»
Vishesh's picture

Treeview Unresolved

Submitted by Vishesh on 24 July, 2011 - 08:54.

Just tried this on my system. m using Excel 2010. I have added a treeview control on the sheet, saved it and closed. When I reopend, I could actually see my treeview but in a different location. If you open and close a new workbook it will show it fine. So this resolved here. Remember DO NOT save.

But another issue is...if you do save and close the workbook; then if you open it Excel would not recognise it as a treeview control. Try this...in the design view right click to see the properties of Treeview; it won't show up.

Really tricky this issue...

»

Treeview unresolved

Submitted by Anonymous on 24 July, 2011 - 10:46.

Hi Vishesh,

Is there an other option in excel to have the tree view, similar to the one
" right click to see the properties of Treeview" in the categories option.

Can u reply regarding the expand/collapse option too..

Thanks

»

expand and collapse in treeview

Submitted by deepakjipaulson on 24 July, 2011 - 10:58.

Tree view root lines to be selected in the treeview properties.... to get the expand and collapse option

treeview properties.. linestyle_rootlines

»

Directry TreeView for MSExcel2007

Submitted by Anonymous on 8 May, 2012 - 19:25.

Could you pls fill in the codes for a Directry TreeView for MS Excel 2007 in a .xlsx file and mail it across to me @ edwincastellino@gmail.com

»