I currently have a Macro enabled workbook that my company uses to help calculate and total information. That information is then taken and stored on hidden sheets via a macro that also protects it then a second workbook uses Macros to pull the data from it and into it.
The issue we are running into is that the former workbook acts slow in Office 2013 when inputting data then tabbing to the next input zone (the sheets are set so that only certain fields can be modified) and it takes about 7-10 seconds before you can do anything. However, in 2010 it was fine. I did some digging and found that it is by design as Microsoft upped the security related to Macros and protected workbooks.
My question is if anyone happens to know how to remove the protection? I have tried a few methods and changed the protection in one of the Macros then tried removing the protection Macros. Still no change. If I disable Macros from running at all in Excel, it is very fast, I can tab to the next field and input data immediately. That tells me that it is built into the Macro itself somewhere and I think I know where. It seems that every time it is moving to a new field it is having to run through the protection which is now SHA2 512bit vs the older SHA1 160bit. Each sheet has its own macro on top of the macro of the workbook, which I think is the culprit:
Private Sub WorkSheet_Calculate()
ActiveSheet.Unprotect Password:="XXXXXX"
If ActiveSheet.Range("S42").Value = "N" Then
ActiveSheet.Buttons("Button 1").Enabled = False
ActiveSheet.Buttons("Button 1").Font.ColorIndex = 15
Else
ActiveSheet.Buttons("Button 1").Enabled = True
ActiveSheet.Buttons("Button 1").Font.ColorIndex = 1
End If
ActiveSheet.Protect Password:="XXXXXX"
End Sub
If I remove the "Unprotect" part, it still acts slow but otherwise acts normal. However, if I remove the "Protect" part of this Macro it will cause the sheet to error out. I have tried quite a few things and had absolutely no luck.
Anyone have any tips? We don't really need the protection but the calculations that the workbook does is very helpful time wise for our employees that use it.
Thanks in advance.