Autofilter code not working as desired

[dleister]

My objective is to have a button on the form which:
unprotects the form if it is protected
activates the autofilter for all columns if it is not already active

if it is already unprotected activates the autofilter for all columns if it is not already active

This code unfortunately does not operate the autofilter correctly; if the autofilter is already on it removes it

Here is my code:

Sub FilterInv()
'
' FilterInv Macro for Invoice Log
'
With ActiveSheet


If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect
.AutoFilterMode = False


.Range("A6").AutoFilter


Else
.AutoFilterMode = False


.Range("A6").AutoFilter

End If


End With
End Sub

 

[Ramesh]

Try placing an if condition to check whether a filter is already active on the current worksheet..try using the following code:

    If .AutoFilterMode = True Then
        .AutoFilterMode = False
    Else
        Range("A1").CurrentRegion.AutoFilter
    End If

 

[dleister]

Thank you Ramesh,
I tried this code and it works to filter however if selected the second time it removes the filter. I want it to unprotect and then autofilter no matter how many times it is selected. I hope that makes sense.


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Ramesh]

Request you to help me with the workflow clearly plz.. its little confusing :-(

 

[dleister]

I have a form with a button which the user selects to remove all applied filters.

I want the code to unlock the worksheet and respond "no filter applied" if there aren't any and if there are then I want it to unfilter the data which will allow the user to apply a new filter from any of the columns in the table/worksheet.

Currently the code does give the message if no filters applied; however if there it removes the auto filter arrows on the column. This is very confusing to the user, so I want the code to never remove the arrows on the columns.

 

[Marc L]

Use Range.AutoFilter to remove all active filters without losing arrows …
​

 

[dleister]

Marc, thank you so much!!