Showing All Data in a Protected Worksheet

**Labros2JD** · 09-29-2010, 04:07 PM

Hi All,

I have a worksheet with filtering options. I would like to have the filters be reset, or to show all data, when the worksheet is closed that way each time it is opened it will not have a filter set up. However, this spreadsheet is also a protected one. I know that the ShowAllData in VBA cannot be used on a protected sheet. Therefore I need assistance with the following:

I need to write a VBA code that will unprotect, show all data, and then protect the worksheet all when the workbook is being closed.

Any suggestions?

Thanks!!

**jaslake** · 09-29-2010, 04:50 PM

Perhaps something like this

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Sheet1
        .Unprotect
        If .AutoFilterMode Then
            .ShowAllData
        End If
        .Protect
    End With
End Sub

John

**Labros2JD** · 09-29-2010, 05:54 PM

That will work great except for that now it prompts me to enter my password. How can I setup the password in the VBA code so that I don't have to enter it each time it protects and unprotects the spreadsheet? Thanks!!!

**jaslake** · 09-29-2010, 06:23 PM

Perhaps this

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Sheet1
        .Unprotect Password:="Secret"
        If .AutoFilterMode Then
            .ShowAllData
        End If
        .Protect Password:="Secret"
    End With
End Sub

**Labros2JD** · 09-30-2010, 12:02 PM

Thank you for all of the help. Unfortunately I have run into a bigger issue I did not forsee previously. I am going to be sharing this workbook and therefore the spreadsheet cannot protect and unprotect in a shared workbook.

Anyone have any suggestions of another way to accomplish the show all data?

Thanks!

Thread: Showing All Data in a Protected Worksheet

Thread Tools

Display

Showing All Data in a Protected Worksheet

Re: Showing All Data in a Protected Worksheet

Re: Showing All Data in a Protected Worksheet

Re: Showing All Data in a Protected Worksheet

Re: Showing All Data in a Protected Worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions