CODEWITHSUNDEEP
vbaexcel
paulinhax
paulinhax

Reputation: 602

excel: advanced filter with protected sheet

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

In addition, I want to protect sheet because I have some formulas on cells that people shouldn't be able to change but I also have cells that the user should come with some information then I've already unlocked these cells as you can see below:

enter image description here

The problem is when I try to run my Advanced Filter when I click my "Filter" button. I get an error message that says:

Advanced Filter can't run in Protected Sheet.

So I associated 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

The LimparAntes sub is the routine that calls Advanced Filter, but I still get the same mistake so I'm in doubt. The code is below:

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

Is that the correct way to do that? I've made a lot of research but I couldn't find anyone with the same problem with Advanced Filter as I so I don't even know if it's possible to achieve what I want.

Upvotes: 6

Views: 1666

Answers (1)

Busse
Busse

Reputation: 863

Is this all of the code?

Just from looking at the code without executing it, the first steps should be to unlock/unprotect ALL sheets by doing:

Dim wks As Worksheet

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

Then upon completion, you should run LimparAntes(). That will then copy/filter the data you need. And then finally you should lock the sheets.

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

Try this and see if it removes the error. If not, I have read that setting the autofilter property to True prior to locking the sheet may be beneficial.

Upvotes: 5

Related Questions