[RESOLVED] Block user from turning filter on manually - Dynamically Set AutoFilter to UsedRange.

This code works well for turning on the Auto Filter
for a dynamically determined used range if the filter happens to accidentally be turned off.

My reason for it is because the Row Headers are on row 15, but there is also data on rows 1 thru 14

* I would like to disable the ability for the user to turn the filter on manually.

The method will need to work in both Excel 2003 and 2007

Thanks in advance for any ideas.

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If Filter is off, this code works well to turn it on to a semi-dynamically determined UsedRange

Dim rng As Range
Dim wks As Object
Dim LastRow As Long

Set wks = ActiveSheet

With wks
  LastRow = .[B65536].End(xlUp).Row
  Set rng = .Range(.Cells(15, 1), .Cells(LastRow, 23))'Range= Row(15) to Col(23) to Last data Row
 
  If Not .AutoFilterMode Then
      rng.AutoFilter
  End If
   
      .EnableAutoFilter = True
End With

End Sub

---

I have determined that always releasing and resetting the filter solves any issues,
it just seems more efficient-> to improve the code to Exit If Row(15) is the AutoFilters header row.

Code:

---

' Of course this first line of code is not the correct command
If AutoFilter.HeaderRow = 15  Then

Exit Sub

Else

  'release the AuotFilter so the routine will reset the Filter and Header Row to 15
    ActiveSheet.AutoFilterMode = False

Dim rng As Range
Dim wks As Object
Dim LastRow As Long

Set wks = ActiveSheet

With wks
  LastRow = .[B65536].End(xlUp).Row
  Set rng = .Range(.Cells(15, 1), .Cells(LastRow, 23))'Range= Row(15) to Col(23) to Last data Row
 
      rng.AutoFilter
 
      .EnableAutoFilter = True
End With

End If

---

...

you could try
if .AutoFilter.Range.Row = 15

Try this:

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Long

    If Me.AutoFilterMode = True Then
        If Me.AutoFilter.Range.Row = 15 Then Exit Sub
        Me.AutoFilterMode = False
    End If
    r = Me.UsedRange.Rows.Count + Me.UsedRange.Row - 1 '-- last row of UsedRange
    If r >= 15 Then Me.[A15:W15].Resize(r - 14).AutoFilter
End Sub

---

You can also use this to disable the Filter menu:

Code:

---

    Application.CommandBars(1).Controls(7).CommandBar.Controls(2).Enabled = False

---

But that will affect all other workbooks.

Thanks westconn1, that works fine

Also thank you anhn, I see you came up with the same command as westconn1, plus your code to answer fully my original specification. I have not tried your code yet, but I will later. ->It turns out that I did not need to prevent the user from being able to turn on the autoFilter... The ActiveSheet.AutoFilter.Range.Row = 15 command that both you and westconn1 gave me seems to put me in good shape for now.

Thanks so much for your time anhn ! (I promise to post back in a couple days when I am less busy, to let you know how well your code works for my situation, as it looks very interesting, as well as avoids the need for using On Error Resume Next as I am for now.)

Code:

---

' Routine runs if row 15 is not the header row for the autofilter.
' It also runs if the Filter is off, because of the On Error Resume Next, which is what I need.

On Error Resume Next ' avoids error if AutoFilter is off
If Not ActiveSheet.AutoFilter.Range.Row = 15 Then
On Error GoTo 0

    'release the AuotFilter so the routine will reset the Filter and Header Row to 15
    ActiveSheet.AutoFilterMode = False

Dim rng As Range
Dim wks As Object
Dim LastRow As Long

Set wks = ActiveSheet

With wks
  LastRow = .[B65536].End(xlUp).Row
  Set rng = .Range(.Cells(15, 1), .Cells(LastRow, 23))'Range= Row(15) to Col(23) to Last data Row
 
      rng.AutoFilter
 
      .EnableAutoFilter = True
End With

End If

---