Excel 2013 protect/unprotect macros on bulk sheets confirmed performance issue

[kstrick9]

There is aknown issue with Excel 2013 and a protect/unprotect all macro for large numbersof sheets. In my case, I have about 150 sheets that I run a protect all macrounder a WB Open event. In 2007 it takes seconds and in 2013 it takes about 2 to 3 minutes. <o></o>
<o></o>
Per MS development team..." this is a result of the change in Office 2013 to use a more secure encryption algorithm that takes longer to run. As a result, it takes longer to encrypt the password for password protected sheets. For single sheet protection operations this isn’t noticeable, but when add-ins or macros unlock/lock sheets in bulk the result can be noticeable. The product group has concluded that it’s something they don’t intend to fix in this version and unfortunately there are no workarounds other than to minimize the number of times you lock/unlock worksheets and only do it when they need to be modified"<o></o>
<o> </o>
I'm working on a macro as a workaround that will only verify if a sheet is protected and ifit is not protected then protect the sheet. In other words, if only one sheet isaccidentally not protected when the wb is closed the program only has to lockone sheet upon opening rather than 150. So far the following macro greatlyreduces the opening time except that I seem to be having an issue with theUserInterfaceOnly = True not sticking when the workbook is closed and reopened.For example, if a wb is closed and reopened every sheet with macros throws upan error when running the macro due to the sheet protection; hence theUserInterfaceOnly is somehow not reloading when the wb opens. HELP!<o></o>
<o></o>

Private Sub Workbook Open ()

[FONT=Calibri][COLOR=#000000]Dim x,iTotalSheets As Integer<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Dim ws AsWorksheet<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]iTotalSheets= Worksheets.Count<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]For x = 1 ToiTotalSheets<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]    Set ws = Worksheets(x)<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]        If Not ws.ProtectContents Then<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]            ws.ProtectPassword:="password", DrawingObjects:=False, Contents:=True, Scenarios:= _<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]            False, UserInterfaceOnly:=True,AllowFormattingCells:=True, AllowFormattingColumns:=True,AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]        End If<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Next<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Set ws =Nothing<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri]<o:p></o:p>[/FONT]
[FONT=Calibri][COLOR=#000000]End Sub[/COLOR][/FONT]

<o></o>