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.
Use Double Click in lieu of Filtering
-
- 3StarLounger
- Posts: 256
- Joined: 09 May 2020, 14:00
Use Double Click in lieu of Filtering
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 79671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Use Double Click in lieu of Filtering
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
Hans
-
- 5StarLounger
- Posts: 666
- Joined: 27 Jun 2021, 10:46
Re: Use Double Click in lieu of Filtering
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
-
- 3StarLounger
- Posts: 256
- Joined: 09 May 2020, 14:00
Re: Use Double Click in lieu of Filtering
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.
@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.