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.

Hi,

The burden is in the fact that it will calculate every time an action occurs. The macro enables and disables a button. So the real question would be what this button does. My advice would be to create a separate macro for your button. The check for the value is only done on one range.

If the calculation is set to manual you might gain some speed. Question is does that satisfy the user.

So in short, find an other sort of action that can trigger your macro.  

Maurice

There actually is a Macro for the button as well.

The button itself actually completes the worksheet and takes everything and calculates it together into some hidden worksheets and then locks the workbook from being changed. After that is done, a second workbook has a Macro that calls to the first workbook and pulls data from it and places it in specific columns. It is vastly more complicated than it needs to be, it has Macros for Macros, but it is easier than trying to write a whole new workbook.

There are 6 Macros themselves that are apart from each worksheets Macro:

- Button
- Unprotect
- Protect
- Complete
- Email (the macro that sends a copy to a specified email on the workbook)
- One I don't quite know what it does has this:

Function PrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function

Then of course there is the above Macro which is in each sheet that acts as a day of the month.

It is quite a badly done workbook but not much I can do except try every resource to find a patch type solution until we can build something better.