Lock filters

[lukasz_rz]

Hi Guys.

Is there a possibility in excel to lock the filters? Meaning that I do set up some filters for certain columns and I want prevent other file users to modify it ? Note - I do not want to lock cells to be edited, just filters.


Or maybe is there a simple clever macro that would show a pop-up window every time someone is trying to change a filter?

Thanks a lot in advance.

 

[Kerryx]

format the row containing the filter and set to locked
For the cells you want to be editable format cells and set protection in cells - untick locked
Protect the worksheet

 

[lukasz_rz]

format the row containing the filter and set to locked
For the cells you want to be editable format cells and set protection in cells - untick locked
Protect the worksheet

That is what I did. I've unlocked all the cells except the ones in the first row (with filter) which I do not want to be edited. I've locked the first row from A to F ( I need column G to be filterable). I've protected the sheet.

And indeed, I can edit all the other rows (that is good), I cannot use the filters in locked cells in row 1 (A to F - that is good too), however a filter i column G does not work.

 

[Kerryx]

1) Before protecting the work sheet, Apply Autofilter in the Range where you want users to user auto filter - column G only
2) Protect work sheet , and tickbox with option to "Allow all users of this worksheet to:" and by selecting option "Use Autofilter"

 

[lukasz_rz]

1) Before protecting the work sheet, Apply Autofilter in the Range where you want users to user auto filter - column G only
2) Protect work sheet , and tickbox with option to "Allow all users of this worksheet to:" and by selecting option "Use Autofilter"

Woudn't it clear all my already filtered data from other columns?

 

[Kerryx]

My bad yes it would , I am sorry but that was about all I could suggest

 

[lukasz_rz]

My bad yes it would , I am sorry but that was about all I could suggest

No worries, big thanks for trying

 

[Jaafar Tribak]

I want prevent other file users to modify it

What do you mean by modifying the filters?

 

[Fluff]

How about

Sub lukasz()
    Dim Rng As Range
    Dim Cnt As Long
    
    With ActiveSheet.AutoFilter
        .Parent.Unprotect Password:="[COLOR=#ff0000]Pword[/COLOR]"
        Set Rng = .Range
        With .Filters
            For Cnt = 1 To .Count
                If Cnt <> 7 Then
                    With .Item(Cnt)
                        If .On Then
                            If .Operator = 0 Then
                                Rng.AutoFilter Cnt, .Criteria1, , , False
                            ElseIf .Operator = 1 Then
                                Rng.AutoFilter Cnt, .Criteria1, .Operator, .Criteria2, , False
                            ElseIf .Operator = 7 Then
                                Rng.AutoFilter Cnt, .Criteria1, .Operator, , False
                            End If
                        Else
                            Rng.AutoFilter Cnt, , , , False
                        End If
                    End With
                End If
            Next Cnt
        End With
        .Parent.Protect Password:="[COLOR=#ff0000]Pword[/COLOR]", Contents:=True, AllowFiltering:=True
    End With
End Sub

Change password in red to suit

 

[Fluff]

If your filter criteria are set & don't change then code can be simplified a lot.