Make filters work while spreadsheet protected????

[corinthia]

Hello there

Does anyone know how to make filters work, while you have a spreadsheet protected?

I need to have it protected, but when I go to click on the arrow that the filter display, it does not work.

Any ideas?

 

[DaleWatson123321]

Corinthia,

I've been waiting to see if anyone knows of a "direct" solution. In case there isn't one, here's an "indirect" solution...

Using (relatively simply) VBA code, extract all the data from your protected sheet to another sheet that is NOT protected. The same code would activate the AutoFilter on the unprotected sheet.

In case you're not yet aware, an "additional" option would be to have VBA code attached to a macro button that would permit the user to extract "selective" data from your protected data to the separate sheet. This can essentially provide a similar result as the AutoFilter, and can actually be preferable - in that the data can be extracted to a separate sheet which is set up with separate headings and formatting for printing purposes. This extraction would involve using the Data - Filter - Advanced Filter option.

Be aware, however, that extracting data to a separate sheet "manually" (via the menu) can cause an ERROR message - which is a BUG in Excel. It says you CANNOT extract data to a "separate" sheet. But this IS possible - by using VBA.

If you want to use the above option(s), and decide you'd like assistance, don't hesitate to involve me. I find it MUCH easier to work with the actual file, so emailing me the file would be preferable. If data sensititity is a concern, perhaps you could reduce the number of records in your data, and replace the data with fictitious data that still reflects the type of data you're working with.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Gavona]

Dale, just for reference:
In Excel 2000 I can manually use advanced filter to extract data to a separate sheet. However, I must initiate the advance filter command from the separate sheet. I think this works with or without rangenames, but certainly with.

Having said that even a one line macro which merely applies the advanced filter command using 'standard' rangenames and can be activated with a key combination is a vast improvement!

 

[corinthia]

Hello there, have a look to this VBA Codes, they actually work, and one can use filters while spredsheet protected.

Great!!

We can solve it via VBA

1. Turn on the Autofilter
2. Insert following procedure in a standard module:
Sub Allow_filter()
Worksheets("Blad1&quot.Activate
ActiveSheet.Protect UserInterfaceOnly:=True
ActiveSheet.EnableAutoFilter = True
End Sub

3. Insert following code in the This Workbook module:
Private Sub Workbook_Open()
Call Allow_Filter
End Sub

4. Turn on the worksheet protection
5. Save & Close the workbook
6. Open it again - Done!