Writing VBA code to use Auto Filter while in a Protected Sheet or Protected Workbook

[passin through]

Is it possible to protect a sheet and/or workbook and assign a macro to a button which will Filter on and off the predetermined rows while the sheet is protected? When the sheet is unprotected, the macro runs exactly as desired.

On the Protect Sheet tab I have the SORT and AutoFilter boxes checked.

Currently using the following code:

ActiveSheet.Range("BCD_SORT_COLUMNS").AutoFilter Field:=2

When the routine is run, it halts and gives the following error message:

Run-time error ‘1004’:
You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet (Review tab, Changes group, Unprotect Sheet button). You may be prompted for a password.


The cells set up to use the FILTER are shown in the Advanced Filter dialog box as follows:
List range: $B$9:$C$301
Criteria range: $B$10:$FD$298
The cells named "BCD_SORT_COLUMNS" are $B$9:$C$301

 

[passin through]

forgot to say I am using Excel 2010

 

[Michael M]

You will need to Unprotect the sheet as one of the first lines in your code, otherwise the code, as you have already found out, will fail !

 

[passin through]

Thanks for your reply. I was keeping my fingers crossed hoping for a better out come.
My understanding was that it is not possible to unprotect and then protect a sheet from VBA code. Is there a way?

 

[antialias401]

You can protect and unprotect sheets via VBA:

If the sheet is NOT password protected:

'Unprotect my sheet
Sheets("Sheet1").Unprotect 

'Reprotect my sheet
Sheets("Sheet1").Protect

Do this if the sheet IS password protected:

'Unprotect my sheet
Sheets("Sheet1").Unprotect Password:="Pass"

'Reprotect my sheet
Sheets("Sheet1").Protect Password:="Pass"

Of course, swap out "Sheet1" for your target sheet.

 

[Michael M]

and swapout "Pass" for whatever password you need ...

 

[passin through]

Aye, and it works like a charm of course. Its a work-around for what I see as Excels limits, but it gets me to where I've been trying to go. Thanks very much to both of you for your help.

 

[Michael M]

you do realise that you can include the Protect / Unprotect code into the existing macro ??
It doesn't have to be run separately !

 

[passin through]

Thanks, the protect sheet works fine within the macro.
When the code executes, all check boxes are left unselected in the "PROTECT SHEET" dialog box except the "SELECT UNLOCKED CELLS". The check boxes are under the "ALLOW ALL USERS OF THIS WORKSHEET TO:".
What code would protect the worksheet and also allow for some options to be selected, such as the "USE AutoFilter"?

 

[antialias401]

The worksheet changes you can allow are listed on the MSDN:
https://msdn.microsoft.com/en-us/library/office/ff840611.aspx

Look for the items you want to allow and place them in a comma delimited pattern in the .Protect code:

'Reprotect my sheet
Sheets("Sheet1").Protect Password:="Pass", AllowSorting, AllowFiltering, Etc...