+ Reply to Thread
Results 1 to 6 of 6

How to expand and collapse (unhide and hide) rows in a tree like structure

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    Perth, Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    14

    How to expand and collapse (unhide and hide) rows in a tree like structure

    Dear forum

    Please see my attached spreadsheet. I have a tree like structure with three tiers of branches that I want end users to be able to collapse and expand by clicking on cells marked “-“ and “+”. I have in mind the code that would work, but I lack the knowledge to draft it. I thought a solution would be to run a SelectionChange procedure that would, if the collapse symbol “-“ is clicked, hide all rows starting from the next row to the row in which I would put a text string, eg “stop” in same column below the collapse ”-“ symbol. That way I could add new rows of data and still have the procedure work. At the moment I have coded a very inflexible series of macros linked to static cells. I am aware of Excel's built in tree structure functionality, but I dont like the look of it, and it isnt quite suited to what I need.

    As always any assistance would be greatly appreciated.

    Scott N
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    02-11-2013
    Location
    Perth, Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: How to expand and collapse (unhide and hide) rows in a tree like structure

    rendi-tsgsample.xlsI have looked around for some code. The closest I could find was in the attached workbook, which looked for blank cells in a particular column, and then hid the row in which the blank cell was found. Unfortunately, the code is static - it requires the input of the starting row and the end row to work. What I am looking for is something that is dynamic, a procedure that can identify where the last row is to be hid by either me putting in a text string "Stop", or it finds some other text in the cell, such as the next item in the same tier of the tree.

    Any help would be greatly appreciated.

    Regards

    Scott N

  3. #3
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: How to expand and collapse (unhide and hide) rows in a tree like structure

    This might get you started. Im sure there will be hiccups, I dont think this is a good idea but if this is what you want.

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  4. #4
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: How to expand and collapse (unhide and hide) rows in a tree like structure

    The rules are simple for this spreadsheet. Everything in the tree must have a + or - sign. The branches must be 2 columns apart also.

    tree.xlsm

  5. #5
    Registered User
    Join Date
    02-11-2013
    Location
    Perth, Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: How to expand and collapse (unhide and hide) rows in a tree like structure

    Thanks Mike and Yraen.

    Your code was greatly appreciated. I love the tree.xlsm and have used it in my project = problem solved.

    Thank you so much to both of you for your help.

    Regards

    Scott

  6. #6
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: How to expand and collapse (unhide and hide) rows in a tree like structure

    You're welcome. It was fun to create it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1