Auto Filter on Protected Sheet

**Ivor** · 08-03-2010, 04:38 AM

Good morning All

Hope you are well.

I was wondering if you might be able to assist me with an AutoFiltering issue. I have a spreadsheet which is shared but also has an important column protected. The sheet is used by many users who use 'AutoFilter' to view certain data;Whilst I have been able to allow users to use the filters (by selecting the option whilst protecting the column), the users are complaing that they cannot use the 'Show All' function from the 'Data' menu as it is greyed out. Is there a simple way for me to allow users to utilise the 'Show All' function, or will it only work is the sheet is un-shared/un-protected?

Thanks guys

**dominicb** · 08-03-2010, 04:55 AM

Good morning Ivor

This is quite easy as you are using Excel 2003. Unprotect the spreadsheet, then got to Tools > Protection > Protect Worksheet.
Scroll down through the list box, find Use Autofilter, tick the box and click OK. You will then be able to use your autofilter on a protected sheet.

Just for completeness, if you were using a version of Excel prior to 2002 (XP) then Debra Dalgleish has a solution here.

HTH

DominicB

**royUK** · 08-03-2010, 05:11 AM

If I remember correctly, I think you have to make sure the AutoFilter is on before protecting

**dominicb** · 08-03-2010, 05:44 AM

Originally Posted by royUK

If I remember correctly, I think you have to make sure the AutoFilter is on before protecting

Good point. Should have mentioned that.

DominicB

**Ivor** · 08-03-2010, 06:14 AM

Originally Posted by dominicb

Good morning Ivor

This is quite easy as you are using Excel 2003. Unprotect the spreadsheet, then got to Tools > Protection > Protect Worksheet.
Scroll down through the list box, find Use Autofilter, tick the box and click OK. You will then be able to use your autofilter on a protected sheet.

Just for completeness, if you were using a version of Excel prior to 2002 (XP) then Debra Dalgleish has a solution here.

HTH

DominicB

Hello Dominic

Thank you for your quick reply. I have alreadyfollowed the process as you mentioned which allows users to utilize filters on protected columns, however if the users apply filters and then wish to use the 'Show All' function from the 'Data' menu bar, then all functions are greyed out. This means that rather than just being able to show everything in one click, they will have to memorise which columns they had applied a filter to and individually select 'All' from there. I was thinking that if I gave the users more rights within the protected sheet, the function may become visible again (just incase it's under DATA, FILTER, then, SHOW ALL).

Thanks again sir

Ivor

**Ivor** · 08-03-2010, 06:15 AM

Hello Roy sir, thanks for your reply.

I have attached below the reply I sent back to Dominic.

Hello Dominic

Thank you for your quick reply. I have already followed the process as you mentioned which allows users to utilize filters on protected columns, however if the users apply filters and then wish to use the 'Show All' function from the 'Data' menu bar, then all functions are greyed out. This means that rather than just being able to show everything in one click, they will have to memorise which columns they had applied a filter to and individually select 'All' from there. I was thinking that if I gave the users more rights within the protected sheet, the function may become visible again (just incase it's under DATA, FILTER, then, SHOW ALL).

Thanks again sir

Ivor

**dominicb** · 08-03-2010, 06:31 AM

Hi Ivor

There is no available functionality to do what you require, however we can use macros to get around this.

Put this macro into an empty module and assign it to a command button (use the Forms toolbar for the command button).

Please Login or Register  to view this content.

It will unprotect the sheet, remove all the autofilters and reprotect it again using password as the password (change above as required).

To remove all filters, your users just click the buton.

HTH

DominicB

**Ivor** · 08-03-2010, 07:10 AM

Thank you Dominic sir.

I have tried your code but I was receiving an error saying 'ShowAllData' method of worksheet class failed. However this is irrelavent anyway, because the reason I am protecting the column is because some users keep changing dates, which have a knock effect on extracts for other teams (hence queries coming back all day from random areas complaining at me!!!). So if I allow the users to temporarily 'unlock' the column in question, then there would be no point in protecting it in the first place (since the very people I am protecting it against will be able to uprotect it at a click of a button).

However I am massively grateful for you assistance here and for your time, and will just have to think of a manual solution until Microsoft add in the functionality one day (hopefully long after I am gone from this job!!! ha haha)

Thanks again sir

Cheers
Ivor

**teylyn** · 08-03-2010, 07:19 AM

Ivor

I have attached below the reply I sent back to Dominic.

This is a forum. The posts can be read by anyone. You don't need to repeat them to make them accessible to members other than the one you replied to.

Also, please don't quote whole posts. It's just clutter. Forget the "Quote" button. Use the Quick Reply box or the "Post Reply" button to reply to a post.

thanks

**royUK** · 08-03-2010, 07:26 AM

Dominic's code only unprotects the sheet temporarily. It adds protection back immediately it has shown the data.

To use the code in a shared workbook you must first remove sharing, add the code then apply sharing again

**dominicb** · 08-03-2010, 07:29 AM

Hi Ivor

Originally Posted by Ivor

So if I allow the users to temporarily 'unlock' the column in question, then there would be no point in protecting it in the first place (since the very people I am protecting it against will be able to uprotect it at a click of a button).

The worksheet is "temporarily uinlocked" for a fraction of a second - and then locked back up again. Certainly not long enough for a user to start altering values.

And the macro works just fine. Take a look at the example workbook attached.

HTH

DominicB