Advanced Filter Protected Sheet

[martipe1]

I run advanced filter

ProtectSheets
... code
rngA.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"WorkBalance"), CopyToRange:=Worksheets("Work").Range("R1"), Unique:=False

All sheets are protected with this procedure

Sub ProtectSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect , userinterfaceonly:=True
Next ws
End Sub

And when I try to run my code receive the following error message:

"You cannot use this command on a protected sheet"

Why if the sheets are protected with userinterfaceonly:= True, my code gives me above mentioned error?

Thanks.

 

[DanteAmor]

Worksheets("Work").Range("R1")

The cells of R1 and below, where you write the filter result must be in unlock format to be able to write inside them.
Or unprotect the sheet, filter and then protect the sheet.
Try:

With Worksheets("Work")
  .Unprotect
  rngA.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("WorkBalance"), _
    CopyToRange:=.Range("R1"), Unique:=False
  .Protect , userinterfaceonly:=True
End Sub

 

[martipe1]

Thank you very much for your answer.

That will do the trick, but still wondering why I need to unprotect a sheet if UserInterfaceOnly is supposed to protect the user interface but not for macros

 

[DanteAmor]

Try this:

.Protect userinterfaceonly:=True

 

[martipe1]

Thank you for your reply.

At the beginning of my code I start protecting the sheets by calling the procedure ProtectSheets

     ProtectSheets
             ... code
             rngA.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
             "WorkBalance"), CopyToRange:=Worksheets("Work").Range("R1"), Unique:=False

within that procedure, I protect sheets using UserInterfaceOnly

Sub ProtectSheets()
      Dim ws As Worksheet
             For Each ws In ActiveWorkbook.Worksheets
                    ws.Protect , userinterfaceonly:=True
             Next ws
End Sub

I would assume with my code will be able to make changes to any protected sheet without the need to go through the process to unprotect/protect the sheet, but looks like I'm wrong and would like to know why.

Thanks

 

[DanteAmor]

I did some tests and with the advanced filter it does not work userinterfaceonly

 

[martipe1]

Thank you!!

 

[Steveghi]

I did some tests and with the advanced filter it does not work userinterfaceonly

Hello,

I'm new to this forum but noticed a similar problem with the UserInterfaceOnly

If the worksheet is protected by a password that the user is not permitted to know, then there appears to be no way for a macro to insert anything into a protected cell without storing the password in the VBA code.

Has that been your experience?

 

[martipe1]

Yes, that was my experience