Allow filtering on a protected sheet where the column names are not in Row 1

[intsoccersuperstar]

I have a protected file that has its column names in B6:K6 and data below. It is protected with the password "secret password".

When the user hits a button, I have code that unlocks the sheet like so...

Worksheets("MyOutput").Unprotect "secret password"

My code then does its business, and then to re-protect the sheet I have

Worksheets("MyOutput").Protect "secret password"

Users are allowed to select the locked cells, but they would also like to be able to filter. Remember, the column titles are in cells B6:K6, not the first row.

How can I go about allowing them to filter? I've googled but all suggestions seem to be dependent on the column names being in the first row.

Thanks in advance!

 

[Fluff]

As long as you set the autofilter before protecting the sheet, you should be ok

 

[intsoccersuperstar]

Thanks, Fluff. So what I tried is this: I unprotected the sheet, selected cells B6:K6, went to the Data tab and hit Filter (is this "AutoFilter"? I've always just called it "Filter"), then re-protected the sheet.

Unfortunately, it still does not allow filtering after the VBA code re-protects it. The cells B6:K6 can be selected, and the filter drop down arrows are present, but cannot be clicked on. It acts as if they're not there.

Apologies if I'm missing something obvious.

 

[Fluff]

When you reprotected the sheet, did you check the box that says "Use AutoFilter"?

 

[intsoccersuperstar]

I did not. After trying it, it lets me use the filters with the sheet protected before I run my VBA code. But after I run it, I still lose the ability to use the filters after the VBA code re-protects it.

 

[Fluff]

You will need to record a macro of you protecting the sheet, that way it will show you the code to allow filtering which you can then add to your existing code.

 

[intsoccersuperstar]

So that ALMOST works...it doesn't seem to like PASSWORD protection. When I record the macro, I get the following:

Sub ProtectionMacro()
'
' ProtectionMacro Macro
'


'
ActiveSheet.Unprotect
Range("B6:K6").Select
Selection.AutoFilter
Range("B6").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End Sub

Notice that my "secret password" is not part of the recording, even though I put it in to unprotect it and re-protect it.

Using the following code works:

Range("B6:K6").Select
Selection.AutoFilter


ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True

But if I try to put my "secret password" anywhere into that ActiveSheet.Protect statement I get a "Compile errror: Expected: end of statement" error.


This will work fine enough for our purposes, as the users of this will not be trying to unprotect it with or without a password. But in a perfect world, I'd like to be able to assign my "secret password" to it if possible.

 

[Fluff]

Yeah, not sure why the recorder no longer records the password, but you can simply add this to the end of the protect line of code

Password:="secret password"

 

[intsoccersuperstar]

That did it. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback