Solved: Can "Lock project for viewing" cause issues?

[AirCooledNut]

Here's an issue I can consistently reproduce. It involves 'Lock project for viewing' and the execution of code...or the non-execution of code. Or maybe the non-execution of a Property? :think:

Uncheck the 'Lock project for viewing', save workbook and close. Open workbook and my sheet protection routine works.
...
For Each shtWkSht In ThisWorkbook.Worksheets
'Only lock down certain sheets.
If IsThisSheet2BeProtected(shtWkSht.Name) Then
'Specify sheet protection properties
Application.StatusBar = "Protecting sheet " & shtWkSht.Name & "..."
'IMPORTANT: UserInterfaceOnly:=True allows sheet to be locked but allows VBA to 'work' the sheet w/o having to deal w/password un/setting
shtWkSht.Protect UserInterfaceOnly:=True, Password:=gPASS, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering:=True
shtWkSht.EnableSelection = xlNoRestrictions
Else
shtWkSht.Unprotect gPASS 'If sheet isn't to be protected then make it Unprotected
End If
Next shtWkSht
...
'-----------------------
Function IsThisSheet2BeProtected(sSheetName As String) As Boolean
'Checks the first line of code in a sheet module for "'Protect" (no double quotes). If found then the sheet is a system sheet and
'thus should be protected so routine will return TRUE (true that it's a system sheet to be protected).
Dim VBProj As VBIDE.vbProject, VBComp As VBIDE.VBComponent, CodeMod As VBIDE.CodeModule, FindWhat As String, Found As Boolean, sCodeName As String

On Error Resume Next
IsThisSheet2BeProtected = False 'Set default value
Set VBProj = ActiveWorkbook.vbProject
sCodeName = Worksheets(sSheetName).CodeName 'VBA uses Code Names, not Sheet.Name
Set VBComp = VBProj.VBComponents(sCodeName) 'Look in the sheet's...
Set CodeMod = VBComp.CodeModule '...code module
FindWhat = "'Protect" 'What we're looking for

'Search the first row & 8 columns of the code module of the sheet
If CodeMod.Find(Target:=FindWhat, StartLine:=1, StartColumn:=1, EndLine:=1, EndColumn:=8, WholeWord:=True, MatchCase:=True, PatternSearch:=False) Then
IsThisSheet2BeProtected = True
End If

Set CodeMod = Nothing
Set VBComp = Nothing
Set VBProj = Nothing
End Function
Now, if I 'Lock the project for viewing' and give it a password, save the workbook and close it, when I open the workbook the protection routine fails -- BTW, it's called from the Workbook_Activate event but even when I put it in the Workbook_Open event it still failed to execute properly. Every sheet is protected! I confirm it by viewing the Review ribbon, Changes section, Unprotect Sheet is displayed instead of Protect Sheet. Just as a note, the code in the Workbook_Open event DOES work and all macro security is turned on (Trust to the VB project, enable all macros, and it's a .xlsm workbook), just the Protection routine fails :dunno

What's going on and is there a work-around? : pray2: Trimmed-down test file is attached. Project password is "eto" (no quotes).

[CatDaddy]

unprotect sheet before setting the locked portions

[AirCooledNut]

unprotect sheet before setting the locked portions That didn't work. Here's the code change I made:
For Each shtWkSht In ThisWorkbook.Worksheets
'Only lock down certain sheets.
If IsThisSheet2BeProtected(shtWkSht.Name) Then
'Specify sheet protection properties
Application.StatusBar = "Protecting sheet " & shtWkSht.Name & "..."
shtWkSht.Unprotect gPASS
'IMPORTANT: UserInterfaceOnly:=True allows sheet to be locked but allows VBA to 'work' the sheet w/o having to deal w/password un/setting
shtWkSht.Protect UserInterfaceOnly:=True, Password:=gPASS, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering:=True
shtWkSht.EnableSelection = xlNoRestrictions
Else
shtWkSht.Unprotect gPASS 'If sheet isn't to be protected then make it Unprotected
End If
Next shtWkSht If this isn't correct there where should the unprotection take place?

[snb]

If you give every sheet that should be UI-protected a name that ends with "_pro" you can use this code in the workbook open event:


Sub Workbook_open()
For Each Sh In Sheets
If Right(Sh.Name, 3) = "pro" And Not Sh.ProtectionMode Then Sh.Protect "pw", , , , True
Next
End Sub


In this case there's no relation between protecting the VBA project and this code.
In your code you are interfering with the VBA-project. If that Project has been protected your code will fail.
I don't think it is possible to enter a protected VBA-project using VBA.
To accomplish what your goal seems to be it's not necessary to interfere with the existing VBA project.

[GTO]

Apologies if too quickly read, but snb's suggestion seems on-track. That said, I would personally suggest using a pattern in each worksheet's CodeName, as this property is less easily changeable.

[AirCooledNut]

snb, thanks for the explanation. I'm working with four different templates and the sheet names from the templates vary so it's not so simple, plus, wouldn't it be easy for an end user to simply rename the sheet to exclude the "_pro" (or whatever was used) and thus kill all protection (which is what GTO is suggesting)?

GTO, with a bazillion changes to each of the templates the CodeNames are all different, even for similar sheets they have in common.

<sigh>Okay, no super-big deal. Thx for the help folks.

[snb]

If you were more specific we could help....

[AirCooledNut]

The templates have a sheet named "Current_Year". The master workbook changes the sheet name to "2013" or whatever was entered in the master workbook grid -- I use a few named ranges to rename multiple things. There's also a "Forecast_Year" worksheet that works the same way but has a different [user entered] year. Other templates have other sheets with different sheet names that get changed during the replication process based upon user entered values. Anyway, protection bound to sheet naming isn't good.

I understand what snb is saying about interfering with the project code and the protection set, and that I need to find a way to work around it (stay away from ActiveWorkbook.vbProject and etc.).

Not a big deal and I have an explanation why :beerchug: Thanks!