I need to protect all worksheets except one while I need to expand/collapse in all the protected sheets

Anonymous
2018-10-18T09:30:43+00:00

Hi,

I have a file with several sheets. I need to protect all sheets except one (in which I have few pivot tables that users should be able to refresh) and in addition, the users should be able to expand/collapse columns and rows in the protected sheets.

This code works to protect all workbook while expanding/collapsing. The problem is that I do know know how to unlock the sheet "Pivots" to let the users refresh some pivot tables.

Private Sub Workbook_Open()

 Dim objHoja As Worksheet

 For Each objHoja In ThisWorkbook.Sheets

 With objHoja

 .Protect Password:="1234", userinterfaceonly:=True, _

 AllowFiltering:=True

 .EnableOutlining = True

 End With

 Next

 End Sub

Any help will be more than welcome!

Marisol

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Lz365 38,211 Reputation points Volunteer Moderator
2018-10-18T10:28:53+00:00

Hi

Add the following line of code just before End Sub:

Sheets("Pivots").Unprotect Password:="1234"

Was this answer helpful?

3 people found this answer helpful.
0 comments
Answer accepted by question author
Lz365 38,211 Reputation points Volunteer Moderator
2018-10-18T11:20:52+00:00

You're welcome. Feel free to Mark as Answer so this can be re-used sooner or later...

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-10-18T10:42:16+00:00

    Hi Lz.

    This is exactly what I needed! Thank you so much!!!

    Was this answer helpful?

    0 comments