Solved: Code to unprotect only one worksheet

[Dave T]

Hello All,

I have a workbook with 13 worksheets in it.
I have a macro to protect all worksheets when the workbook is opened, but I would like one of the worksheet to remain unprotected.

Workbook_Open code:


Private Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
For Each Sh In ThisWorkbook.Worksheets
Sh.Select
Sh.Protect userinterfaceonly:=True
Sh.EnableSelection = xlNoRestrictions
Sh.EnableAutoFilter = True
Sh.EnableOutlining = True
On Error Resume Next
Sh.ShowAllData
Err.Clear
Application.ScreenUpdating = True
End Sub


I have been trying to use a Worksheet_Activate macro to unprotect a worksheet called "FINANCIAL YR INSPECT & MONITOR" wihout success:


Private Sub Worksheet_Activate()
On Error Resume Next
ActiveSheet.Unprotect
End Sub


Is there a better way ???

Can someone please give me two explanations along with code suggestions:

Can extra code be added to the Workbook_Open code to exclude a specific worksheet
What am I doing wrong with the Worksheet_Activate macroThanking you in advance.

Regards,
Dave T

[Simon Lloyd]

This fixes your immediate questionPrivate Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
For Each Sh In ThisWorkbook.Worksheets
Sh.Select
If Sh.Name <> "MySheet" Then ' change for sheet name
Sh.Protect userinterfaceonly:=True
End If
Sh.EnableSelection = xlNoRestrictions
Sh.EnableAutoFilter = True
Sh.EnableOutlining = True
On Error Resume Next
Sh.ShowAllData
Err.Clear
Application.ScreenUpdating = True
End Sub
I wouldn't use On error Resume next as it masks all problems, maybe this is a little tidierPrivate Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
For Each Sh In Sheets
With Sh
If .Name <> "MySheet" Then ' change for sheet name
.Protect userinterfaceonly:=True
End If
.EnableSelection = xlNoRestrictions
.EnableAutoFilter = True
.EnableOutlining = True
.ShowAllData
End With
Next Sh
Application.ScreenUpdating = True
End Sub
You don't need to select a sheet in order to manipulate it.

[Ken Puls]

How's this?

Private Sub Workbook_Open()
Dim Sh As Worksheet
With Application
.ScreenUpdating = False
.WindowState = xlMaximized
End With
ActiveWindow.WindowState = xlMaximized
For Each Sh In ThisWorkbook.Worksheets
With Sh
If Not UCase(.Name) = "FINANCIAL YR INSPECT & MONITOR" Then
.Protect userinterfaceonly:=True
.EnableSelection = xlNoRestrictions
.EnableAutoFilter = True
.EnableOutlining = True
On Error Resume Next
.ShowAllData
On Error GoTo 0
End If
End With
Next Sh
Application.ScreenUpdating = True
End Sub

[Ken Puls]

Beat me by a minute there, Simon. Just for reference, if I recall correctly, ShowAllData throws an error if filtering is not active. So you would want to have the On Error in front, but I'd suggest that it be turned off immediately after.

Dave, so you know, setting the On Error Goto 0 clears any reference to the error as well, so you don't need to use err.clear with it.

[Ken Puls]

And hey, Dave, thank you. I learned something (http://www.excelguru.ca/blog/2010/09/01/enabling-outlining-commands-on-a-protected-worksheet/) from this thread. :)

[Simon Lloyd]

Beat me by a minute there, Simon. Just for reference, if I recall correctly, ShowAllData throws an error if filtering is not active. So you would want to have the On Error in front, but I'd suggest that it be turned off immediately after.Good catch, i didn't test it (bad i know :() it was early here and i just typed it up. I'm not a fan of "On Error Resume Next" but in some cases i guess you have to bite the bullet :)

[Dave T]

Thank you Simon and Ken for your replies, they are very much appreciated.

Just out of curiosity...
Could you use code along the lines of both of yours but modified to protect the entire workbook and if desired use Worksheet_Active on a sheet by sheet basis to unprotect a specific sheet.
I realise this would be an inefficient way to do things i.e. protect all worksheets then unprotect one/some.
Would there be any problems/conflict protecting all worksheets with the Workbook_Open code then possibly soon after a Worksheet_Activate code is run that unprotects a worksheet ??
If there were no problems what would the Worksheet_Activate code look like if run in conjunction with the Workbook_open ??

Once again, thanks for your replies.

Regards,
Dave T

[Simon Lloyd]

If you unprotect on Worksheet_Activate then there is no point whatsoever in protecting the worksheet.

Protecting the workbook will protect the structure too and you will NOT be able to make any changes without unprotecting. I don't know what you envisage doing, can you explain better what you want to acheive and why?

[Dave T]

Hello Simon,

I agree the code in the Workbook_Open as suggested by you and Ken is the way I will go.
The workbook I have used the code in will have all the sheets protected.
All I was curious about was that rather than adding extra lines to the Workbook_Open coding for each sheet/s, could Worksheet_Activate be included on a sheet by sheet basis ??
If this did not cause any conflict with the Workbook_Open maybe it could be temporarily added to various worksheets while they are being worked on and then removed later.
Also I suppose I thought it might be easier to add a block of unique, self contained code as required, at bit like where you call another macro from within other code. That way it might be easier for a VBA novice like me to remove it when it is no longer required than trying to work out which parts of the Workbook_Open code can be removed/commented out without affecting the original code.

Not that I am planning of using the Worksheet_Activate code, I was curious if trying to unprotect as worksheet using Worksheet_Activate would cause any conflict if a Workbook_Open macro is used that protects all of the sheets in the workbook upon opening.

Regards,
Dave T

[Simon Lloyd]

It wouldn't cause a conflict (although as i said unprotect on activate means the whole worksheet is ediatble everytime it's brought in to view) as the Workbook_Open code runs first and then is no longer referenced, i haven't tried it so not sure that the worksheet_activate runs on the initial visible sheet right after the workbook is opened.

[Ken Puls]

I would see this more as a case to use Worksheet_Activate to protect each individual worksheet, rather than unprotect it. Here's my take on the Pros/Cons vs just using the workbook_open route:

Pros:
-It would fire each time someone activate the worksheet which means that you could reprotect the worksheet if someone unprotected it and navigated away. (Similar to the Lock on Resume feature of a screensaver.)
-You can easily control which sheets are subject to that feature... just drop the code in the ones you want to protect.
-One place to look to see what is happening.

Cons:
-A new worksheet inserted in the file would not get the code inserted. This could be a bad thing if you want all sheets except the one you specified protected
-Much more maintenance of code if you ever need to update something
-Could really p!ss off a user if they unprotected the sheet to copy something in, went to another sheet and copied, then came back to paste it (the sheet would have been reprotected, right?) -- Actually, depending on how much you dislike your users, this could be a Pro. ;)
-Extra VBA overhead each time you flip sheets (granted not a lot, but some)
-The Undo stack would be cleared every time you switched between worksheets in the workbook. (I don't think this is a good thing.)

Long and short of it is that you need to know your goals. If you just want the sheets protected, go with the workbook_open routine. If you need any of the pros listed above, and can live with the cons, then go with the Worksheet_Activate event.

[Dave T]

Thnks again Simon & Ken,

The answer Ken wrote was what I was after... the Pros and Cons was a very helpfull explanation.
I also appreciated Ken's down to earth type of answer.

Regards,
Dave T