Allow filtering on protected sheet

[hasan mougharbel]

Hello everyone, I hope you're all doing well. I have a vba code that locks a sheet and unlocks specific columns.

My only enquiry is how to apply a filter to the headers, or in other words, enable the 'Use Auto Filter' feature on that sheet.

I've tried experimenting with this, but haven't been successful.

Thanks a lot.

My code

Sub LockSpecificColumns()
    Dim ws As Worksheet
    Dim rngLocked As Range
    Dim rngUnlocked As Range

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Data")
    
    ' Set the ranges to be locked and unlocked
    Set rngLocked = ws.UsedRange
    Set rngUnlocked = Union(ws.Columns("B"), ws.Columns("C"), ws.Columns("D"), ws.Columns("E"), ws.Columns("H"), ws.Columns("F"), ws.Columns("G"))

    ' Lock the entire sheet
    ws.Cells.Locked = True

    ' Unlock specific columns
    rngUnlocked.Locked = False
    
    With Worksheets("Data")
    .Protect AllowFiltering:=True
    End With
        
    ' Protect the sheet
    ws.Protect
End Sub

[deadlyduck]

Removing the last line (ws.protect) seems to be the answer- you've already protected the worksheet in the preceding few lines so it looks like the last line overrides the settings.