Geek Answers Handbook

Excel: Advanced Protected Sheet Filter

I have this sheet where I use Advanced Filter to search for information inside another sheet in my book.

In addition, I want to protect the sheet, because I have some formulas on the cells that people cannot change, but I also have cells in which the user should get some information, then I already unlocked these cells, since you can see below:

enter image description here

The problem is that I try to run my advanced filter when I click the Filter button. An error message appears:

The advanced filter cannot work in a protected sheet.

So, I linked this code to my Filter button:

Private Sub Filtrar_Click()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
    wks.Unprotect "Password"
    Call LimparAntes
    wks.Protect "Password", UserInterfaceOnly:=True
Next

End Sub

LimparAntes - , , , . :

Sub LimparAntes()
'
' LimparAntes Macro
'

'
Dim Lastrow As Long
Lastrow = Sheets("AUX").Range("A" & rows.Count).End(xlUp).Row

    Sheets("AUX").Range("A1:K" & Lastrow).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("CONSULTA").Range("D34:I35"), CopyToRange:=Sheets("CONSULTA").Range("B40:K40"), Unique:= _
        False
    Sheets("CONSULTA").Range("F37").Select

End Sub

? , - Advanced Filter, , , .

+6
1

?

, , / , :

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
    wks.Unprotect "Password"
Next

, , LimparAntes(). / . , , .

Private Sub Filtrar_Click()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
    wks.Unprotect "Password"    'Unprotect all sheets first
Next

Call LimparAntes     'Call filter sub

For Each wks In ActiveWorkbook.Worksheets
    wks.Protect "Password", UserInterfaceOnly:=True     'Re-Protect all sheets
Next

End Sub

, . , , True .

+5

More articles:


All Articles