Excel 2013 unProtect/Protect much slower

Excel 2013 Unprotect and Protect is much slower. All over VBA code in a spreadsheet where I have many worksheets I protect and unprotect all specified worksheets instead of protecting a specific sheet. Each individual subroutine may recall the same two processes. How do I correct the slowness without having to remove this code all together and trying to change the code to just protect specific individual sheets.

Here is the code. Microsoft said for 2013 they are fixing the problem so take all this logic out. Has anyone experienced this issue and what did you do?

Please Login or Register  to view this content.

Whatever version of Excel you are using, unprotecting and protecting all worksheets in order to work on one probably isn't the most efficient way of working.

I would suggest that you either use .Protect UserInterfaceOnly:=True for each sheet. You would need to apply the setting in the Workbook Open event handler.

Alternatively, amend the subroutine to operate on only one worksheet and pass it the worksheet, or worksheet name, as a parameter.


Regards, TMS

Is difficult to always protect a single sheet. We did this mass sheet protect/unprotect logic because in many cases we wanted to be able to update multiple sheets and we did not have the information up front. May be clearing all the data on all the individual worksheets for a new fiscal year. We may be resetting data on all the individual worksheets from processed to unprocessed. Most of the cases I guess I could change the logic to unprotect/protect a specific sheet because in most cases I know which sheet is being processed. But in a few cases we need to keep the unprotect/protect all sheets. Will change it wherever I can but some special processes I cannot change the protection. We use this spreadsheet to do our Food Services Invoice processing for many different accounts. Each account is a different worksheet within the spreadsheet.

Hopefully this might speed up the process wherever possible. Won't be perfect but will be better than it is in 2013. Not a problem at all before 2013.

If you use UserInterfaceOnly, and set it in the Workbook Open event handler, your code can operate on protected sheets, but users cannot.

You would be able to remove all protect and un-protect code.

Tedious, but perhaps a worthwhile exercise, given that you have to do something anyway.

Perhaps make a copy of the workbook and test this approach and see if it improves performance.

Regards, TMS

Another idea is to use:

If Not Sht.ProtectContents then Sht.Protect Password:= MyPwd

And

If Sht.ProtectContents then Sht.Unprotect Password:= MyPwd

These two lines will only protect sheets if they are not already protected, and unprotect sheets if they are not already unprotected, thus saving time protecting and unprotecting sheets when it is not necessary. :-)

Edit: Oops I just realized you're already using this trick, in which case the tips above should help.