Excel: collapsing/expanding rows by double clicking cell

[tankthefrank]

hi folks,

I'm pretty retarded when it comes to excel which after 10 yrs of using is pretty poor.

Objective: create a cascading look across a s/s.
I want the user of the s/s to double click on a cell (NOT THE COLLAPSE/EXPAND button in groupings) to expand additional rows, and vice versa collapse by double clicking the same cell. The cell i'm referring to is a few columns in hence why i don't want to use the collapse/expand buttons from groupings.
Big boss wants something funky which i'm not entirely great at delivering so any help with this would be great.
If you know a cool way of getting that cascading look (without merging cells together) please let me know. Much appreciated

 

[GlennUK]

I don't what you mean by "that cascading look", but expanding collapsing groupings is very easy to trigger on a double-click.

Right-click the sheet tab and choose View Code, and paste this code into the code pane:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ActiveSheet.Rows(Target.Row).ShowDetail = Not ActiveSheet.Rows(Target.Row).ShowDetail
End Sub

By the way, if you have any VBA queries I recommend posting in the VBA forum forum707 in the future.

Cheers, Glenn.

Beauty is in the eye of the beerholder.