Use Double Click in lieu of Filtering

gailb
3StarLounger
Posts: 256
Joined: 09 May 2020, 14:00

Use Double Click in lieu of Filtering

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 79671
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use Double Click in lieu of Filtering

Post by 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...
Best wishes,
Hans

User avatar
SpeakEasy
5StarLounger
Posts: 666
Joined: 27 Jun 2021, 10:46

Re: Use Double Click in lieu of Filtering

Post by 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
3StarLounger
Posts: 256
Joined: 09 May 2020, 14:00

Re: Use Double Click in lieu of Filtering

Post by 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.