Use Double Click in lieu of Filtering

[gailb]

In this sheet, I would like to double click in column A and hide or unhide the rows. Much like you would do if I simply filter by column A.

I'll have the sheet start with just the 1's. So only visible rows would be, 2 and 15. If the user double clicks on the 1 in A2, I would like for everything to unhide except for A2, A3, and A12 would be visible. Then if the user wants to drill down on A3 with a double click, A4, A7, and A13 would now unhide.

Then of course the reverse. If the user double clicks on A3, all the rows with a 3 (A4, A7, and A13) would hide.

Is this possible? I've seen a few things that I can make hide, but I don't understand how to reverse with the unhide.

test workbook.xlsm

[HansV]

Once you have hidden all rows with 1, they are by definition not visible, so it is not possible to double-click a 1 to unhide it...

[SpeakEasy]

Assuming I have understood your question properly, try the following vba code in Sheet1:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Static MaxDepth  As Long
    
    If Not Intersect(Target, Range("Table1")) Is Nothing Then
        If MaxDepth <= Target Then
            MaxDepth = Target + 1
        Else
            MaxDepth = Target
        End If
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="<=" & MaxDepth, Operator:=xlAnd
   End If
   
   Cancel = True
   
End Sub

[gailb]

Thanks Hans and SpeakEasy.

@SpeakEasy, I really like what this DoubleClick event does, but what's the possibility of refining it to the WBS?

So, in the case of A2. I everything is collapsed (filtered) to just show level 1's (A2 and A17), but when the user double clicks on A2 to expand the filter, can we have the filter only filter the WBS structure that begins with a 1. In that case, only A2, A3, and A12 would be visible. Everything that is a WBS structure 2 would still be hidden.