Excel: how to save and restore sheet protection settings

Is that what your want
http://www.mrexcel.com/forum/excel-questions/228818-showalldata.html

No, they modify the autofilter but it can't modify the autofilter if sheet si protected.

A litte bit confused, did you read this, if please write your thread much clear

http://office.microsoft.com/en-us/excel-help/password-protect-worksheet-or-workbook-elements-HP010078580.aspx

The problem is very easy.

Set an autofilter and filter something.

Then execute this code:

Sub test1()
    With ActiveSheet
        If .AutoFilterMode Then
            If .FilterMode Then
                .ShowAllData
            End If
        Else
            If .FilterMode Then
                .ShowAllData
            End If
        End If
    End With
End Sub

Select allOpen in new window

Results:
if sheet is unprotected -> it removes your filter
if sheet is protected -> it crashes on the ShowAllData line with a 1004 error

I would to avoid the crash (keeping the same logic).

PS: manually removing and reenabling the protection is not an option. I've to use it often and over many files of other users.

When you open the workbook, unprotect the worksheet, then reprotect it with UserInterfaceOnly parameter set to True. You can then run your macro while the worksheet is protected.
Worksheets("Sheet1").Protect Password:="somepassword", UserInterfaceOnly:= True

Wow thanks byundt!  it worked!
If it's not possible (as alternative) to save and restore protection settings I will change the protection on all the files (a lot) with your "UserInterfaceOnly:= True" method.

The UserInterfaceOnly property is not saved with the file, unfortunately. It must be set each time you open the file, as well as each time you apply protection.

The UserIntefaceOnly trick to allow macros to work without removing sheet protection works in many cases, but not all. But when it does work, as in your current situation, it is a very nice trick indeed.

Brad

Wait, the big problem is that I'm in a company where there are many Excel files with different protection settings.
I just want to use the ShowAllData command in a macroes in my PERSONAL.XLSB over all these files without disrupting their protection settings.
Is it really impossible to detect the original settings and restore them at the end of my macroes?

Is it really impossible to detect the original settings and restore them at the end of my macros?

It is quite possible to detect the original settings and restore them at the end of the macro. That's exactly what the code in my last post is doing.

As written, it runs on Excel 2013 and restores all the protection features supported by that version of Excel. Some of those features, however, are not available on earlier versions of Excel. These unavailable features will cause run-time errors when the code is run on those earlier versions.

You will need to determine the oldest version of Excel that will be in use by one of your macro users. You then have a choice of either simplifying the code to support just that oldest version, or testing the version in the code and  executing different statements for each one.

byundt I don't understand because I see a single sub.
What's the part that reads/saves the original settings and what's the part that restores them?

Statements 8 to 12 capture the original settings. Statements 15 to 31 reapply them along with UserInterfaceOnly:=True

ah, great!
now I try it, adapting it to the limited settings of Excel 2010

You may find that recording a macro will expose the settings that are present in your version of Excel for comparison to those in Excel 2013.

impressive byundt!
Your sub works perfectly without modifications on my Excel 2010!
I love you!!!
I would to give you 5000 points!