Design help for showing/hiding rows

[Michael Simpson]

At the moment, I'm not after any actual code (though that may well change ). I'm more after ideas on a best way to solve a problem. I'm an average VBA programmer (amazing what you can find by googling), but one thing I've learnt with VBA/Excel is it's your imagination that is often the limiter. Anyway - to my design problem.

You have a bunch of parent rows where each one could contain one or more child rows. Generally, the user will want to review the parents, and then might want to drill down and review the children. I've come up with a couple of designs at the moment.

All the rows on one sheet, children hidden. Some sort of toggle that shows/hides the children. Question - will you need one toggle for EACH parent, or can you have a general toggle that then interprets where the cursor is and toggles accordingly ? (... and how would you solve the toggle button being, say, on row 1 and the row they want to toggle is a couple of pages down ?)

You have all the parents on one sheet, and the parents/children (all) shown on the second sheet. The user places focus on the parent on sheet 1 and presses "something", whereupon the code performs a "hyperlink" to the correct row on the second sheet. Question - how difficult to build, and what solution to "go back" to the correct parent on the first sheet ?

 

[Andrew Poulsom]

You could use a manual outline:

http://office.microsoft.com/en-ca/excel-help/create-an-outline-HP005201641.aspx

 

[Michael Simpson]

My immediate thought when I saw your reply

was "Oooh, nice". Thank you Andrew. That seems a much more elegant solution (on a scale of 1-10 as an Excel user, I'm a 0.5).

I assume you can create an outline using VBA programming ?

 

[Andrew Poulsom]

Re: My immediate thought when I saw your reply

I assume you can create an outline using VBA programming ?

Yes you can. Example:

Sub Test()
    Range("A2:A6").Rows.Group
End Sub

 

[Michael Simpson]

Re: My immediate thought when I saw your reply

Thanks very much Andrew. Great help