reset filter when double click same cell twice

[hajiali]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngCol As Range
    Dim Nextnum As Long
  
    Set rngCol = Me.Columns("A:A")
    If Not Intersect(rngCol, Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
        Case Is <> "": Target.ClearContents
        Case Else
        Nextnum = WorksheetFunction.Max(rngCol) + 1
        Target.Value = Nextnum
        End Select
    End If
    If Not Intersect(Target, Range("C2")) Is Nothing Then
    Cancel = True
      ActiveSheet.Unprotect Password:="password"
      ActiveSheet.Range("$A$3:$Q$603").AutoFilter Field:=3, Criteria1:="OFF"
      ActiveSheet.Protect Password:="password", AllowFiltering:=True
End If
End Sub

using the above code im able to double click C2 and it Filters the Field with "OFF." I want to add the function to reset ("Select All") in the column filter when double click again.

Thanks

 

[onlyadrafter]

Hello,

does this work as expected?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngCol As Range
    Dim Nextnum As Long
  
    Set rngCol = Me.Columns("A:A")
    If Not Intersect(rngCol, Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
        Case Is <> "": Target.ClearContents
        Case Else
        Nextnum = WorksheetFunction.Max(rngCol) + 1
        Target.Value = Nextnum
        End Select
    End If
    If Not Intersect(Target, Range("C2")) Is Nothing Then
    Cancel = True
    ActiveSheet.Unprotect Password:="password"
    If ActiveSheet.AutoFilterMode = False Then
        ActiveSheet.Range("$A$3:$Q$603").AutoFilter FIELD:=3, Criteria1:="OFF"
      Else
        ActiveSheet.Range("$A$3:$Q$603").AutoFilter
      End If
      ActiveSheet.Protect Password:="password", AllowFiltering:=True
End If
End Sub

 

[onlyadrafter]

Hello,

if you want to keep filter drop downs try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngCol As Range
    Dim Nextnum As Long
  
    Set rngCol = Me.Columns("A:A")
    If Not Intersect(rngCol, Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
        Case Is <> "": Target.ClearContents
        Case Else
        Nextnum = WorksheetFunction.Max(rngCol) + 1
        Target.Value = Nextnum
        End Select
    End If
    If Not Intersect(Target, Range("C2")) Is Nothing Then
        Cancel = True
        ActiveSheet.Unprotect Password:="password"
        With ActiveSheet
            If .AutoFilterMode And .FilterMode Then
                .ShowAllData
                .Cells.ClearOutline
            Else
                ActiveSheet.Range("$A$3:$Q$603").AutoFilter field:=3, Criteria1:="OFF"
            End If
      End With
    End If
    ActiveSheet.Protect Password:="password"
End Sub

 

[hajiali]

That works great onlyadrafter

 

[hajiali]

So i added more columns by copying that code and changing

If Not Intersect(Target, Range("C2")) Is Nothing Then

to "E2", "G2", "I2","K2", "M2", "O2"

and changing
field:=3 to its column numbers.

it will not let me filter multiple columns simultaneously when I double click say G2 it filters by OFF then if i click on C2 it resets all filters on all rows. I would like to have the option to do this simultaneously with other columns

 

[Fluff]

How about

   If Not Intersect(Target, Range("C2")) Is Nothing Then
      Cancel = True
      Me.Unprotect Password:="password"
      With ActiveSheet
         If .AutoFilterMode Then
            If .AutoFilter.Filters.Item(3).On Then
               .Range("A3:Q3").AutoFilter 3
            Else
               .Range("$A$3:$Q$603").AutoFilter field:=3, Criteria1:="OFF"
            End If
         Else
            .Range("$A$3:$Q$603").AutoFilter field:=3, Criteria1:="OFF"
         End If
      End With
   End If
   Me.Protect Password:="password"
End Sub

 

[hajiali]

I will give this a try. To add same functionality to other columns I will just copy the code for each column and change the range (“c2”) to cell and change all the 3’s to the column number for each column is that correct?

 

[Fluff]

That's right, or you can use this

   If Not Intersect(Target, Range("C2")) Is Nothing Then
      Cancel = True
      Me.Unprotect Password:="password"
      With ActiveSheet
         If .AutoFilterMode Then
            If .AutoFilter.Filters.Item(Target.Column).On Then
               .Range("A3:Q3").AutoFilter Target.Column
            Else
               .Range("$A$3:$Q$603").AutoFilter field:=Target.Column, Criteria1:="OFF"
            End If
         Else
            .Range("$A$3:$Q$603").AutoFilter field:=Target.Column, Criteria1:="OFF"
         End If
      End With
   End If

where you don't need to change the column refs.

 

[hajiali]

Just what I needed thanks Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.