Enable Macros on Protected Sheets

[Tashat]

Hi There

I have a workbook with a number of tabs which I need to lock-down to prevent anyone from changing the data in the cells. They need to be able to interrogate the data using filters etc, but not change anything. Therefore I have password protected the cells using Review>Protect Sheet and ticked the relevant check boxes. Many people who use the spreadsheet leave filters applied all over the sheet, which is not helpful. Therefore, I have applied a macro to remove all filters on opening the spreadsheet (code below from another website). As I expected, this code works perfectly when the worksheets are unprotected, but doesn't work when they are. Can anyone advise what I can do so that I can lock all the cells in the worksheet AND have the macro work on open? Many thanks in advance.

Sub auto_open()
'Updated by Extendoffice 20210625
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True

 

[6StringJazzer]

Your code can unprotect and reprotect the sheets. If you are using a password, then add the password as a text string parameter after each of these calls.

    For Each xWs In Application.Worksheets
        xWs.Unprotect
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
       xWs.Protect
    Next

 

[Tashat]

Your code can unprotect and reprotect the sheets. If you are using a password, then add the password as a text string parameter after each of these calls.

    For Each xWs In Application.Worksheets
        xWs.Unprotect
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
       xWs.Protect
    Next

Hi - thank you so much for taking the time to reply. I'm not very knowledgeable with macros - I've tried to add in a new module and pasted in your code above, but it doesn't like the first line and I don't know how to change it. Are you able to advise further? Do I need to do a new macro for each worksheet? If so, how would do this and edit the code for each worksheet?

 

[Tashat]

Hi - thank you so much for taking the time to reply. I'm not very knowledgeable with macros - I've tried to add in a new module and pasted in your code above, but it doesn't like the first line and I don't know how to change it. Are you able to advise further? Do I need to do a new macro for each worksheet? If so, how would do this and edit the code for each worksheet?

Your code can unprotect and reprotect the sheets. If you are using a password, then add the password as a text string parameter after each of these calls.

    For Each xWs In Application.Worksheets
        xWs.Unprotect
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
       xWs.Protect
    Next

Oh I think I've got it working by pasting it into my code. However, it seems it hasn't retained the options I set when I protect the worksheet - I need the user to be able to use autofilters, Use PivotTable and PivotChart, format columns and format rows. Is there a way I can add this into the code?

 

[6StringJazzer]

My example simply added the two lines of red code to your original code. No new module, no new code. Just two new lines. Looks like you have that sorted out.

To re-protect with the desired options, add these arguments:

xWs.Protect AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

 

[Tashat]

My example simply added the two lines of red code to your original code. No new module, no new code. Just two new lines. Looks like you have that sorted out.

To re-protect with the desired options, add these arguments:

xWs.Protect AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

That's perfect! thank you so much for your help.

 

[6StringJazzer]

You're welcome! Glad to help.