Hide/Unhide with auto-changing object

**BRISBANEBOB** · 03-05-2009, 02:25 AM

I'm working my way through module Programming in VBA 1.01 so this is way beyond me:-

I have an Excel schedule with subtotals and between 1 and 50 rows under each subtotals. The subtotals contain totals of the rows below them. Is it possible to link code to an object (say a 'down arrow' next to each subtotal) and when it is hit, it unhides the rows beneath the subtotals? At the same time the 'down arrow' changes to an 'up arrow' and when that it hit it re-hides the rows.

There is also the possibility that the user will unhide rows and move down the spreadsheet unhiding/rehiding other areas before coming back and wanting to re-hide the first area he revealed. When the spreadsheet is closed, all the arrows should revert to 'down arrows' and all the non-subtotals' rows should be hidden.

Does this make any sense?

**Leith Ross** · 03-05-2009, 03:12 AM

Hello BrisbaneBob,

Is this a project you were given for a class? It can be done, but it is quite complex. Do you have a sample workbook you can post?

**BRISBANEBOB** · 03-05-2009, 03:33 AM

Hi Leith

I wish it was a project - I'd take a fail quite happily.

I have 500 line financial report with subtotals (as per attached sample). I didn't think it would be simple when I started (yesterday) trying to do it but I had no idea how complicated it was going to be - well past my level of knowledge. But it sounds so easy in principle....!

If you have any ideas I'd be grateful.

Thanks and regards

**Leith Ross** · 03-06-2009, 12:35 AM

Hello BrisbaneBob,

Turn out to be easier than I had first thought. Each arrow has a macro assigned to it. Each macro call another macro that does the work. The macro assigned to arrow just passes the rows you want to hide. The macro below are already added to the attached workbook.

Please Login or Register  to view this content.

**BRISBANEBOB** · 03-06-2009, 12:42 AM

Leith

Brilliant! BRILLAINT!

Where in the code does the arrow change direction? I can't work that bit out.

**BRISBANEBOB** · 03-06-2009, 12:45 AM

OK, OK - just opened my eyes and saw 'rotation'

Thanks very much - a great solution.

**Leith Ross** · 03-06-2009, 12:49 AM

Hello BrisbaneBob,

The Arrow is flipped by the If Then Else statement in the HideRows macro. Status is the negation of the of the hidden property. When the rows are visible, Status will be false and the arrow is not rotated (pointing up), if Status is True then the arrow is flipped 180 degrees (pointing down).