Clearing Auto-Filter with Password Protection on

[Ben77]

Hello All,

I relly need your help. I have been trying all morning to sort this out myself and I am failing.

I have prepared a series of Guidance Notes, which when I send must be password protected.

However I want the user to be able to Use autofilter, which I have set up. However the, because of the password protection, they can not auto clear the filters, which is causing some silly mistakes to be made.

Essientially I have tried to write/form a piece of VBA code which does the following via a button:
Unprotects Sheet
Clears filters (manually, as different keyboard shortcuts, etc in different versions of excel)
Re-password protects the sheet.

Surely somebody must have written a piece of code to get rounf this. I would be really greatful for any help.

Thanks:banghead:

[Aflatoon]

Which versions do you have to support? You should be able to clear a filter (but not remove the autofilter itself) manually on a protected sheet if you have allowed autofilter use.

[Ben77]

These notes have to support excel 2003, 2007 and 2010. All of which you can not use 'reset' the filters in the password protection is on, you have to select the filter on each column and clear that way.

I have selected the option to allow auto filter, but this still leaves the problem outlined above.

I have recorded a macro, which unprotects (password), clears each filter, then re protects (with password), and assigned this macro to an object (a square in this case)

However, it does not password protect, it only protects, which is not really good enough.

Help.:dunno

[Aflatoon]

What was the macro? Protecting with password should not be a problem, though you could also try protecting the sheets on workbook open using the userinterfaceonly:=true argument and then the code should not need to change protection in order to clear the filter - for instance:
activesheet.protect password:="test", userinterfaceonly:=true, allowfiltering:=true

on workbook open will then allow your code to simply be
activesheet.showalldata
to clear the filter

[Ben77]

thanks - I shall give this a go.:thumb