Long time to OPEN, CLOSE, and SAVE an Excel file

[rhkan]

I have an Excel workbook with about 60 Worksheets, many formulas, and
some macros/VB code. It has grown to about 50 MB. Despite the size,
this file could be opened/closed/saved in less than 2 minutes.

However, once I added PROTECTION to the workbooks and each of the
sheets, the workbook took over 10 minutes to open/close/save!!

To try to solve this, I added the following VB code below to unprotect
the entire workbook and its 60 sheets JUST BEFORE CLOSING the file.
And then I added code to protect the workbook whenever the FILE is
OPENED. This did not improve the process.

Any suggestions? Please help...


*************************************************************
CODE
*************************************************************

Public Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Unprotect ("richard")
Sheet1.Unprotect ("richard")
'the previous line is repeated for all 60 sheets
End Sub
----------------------------------------------------------------------------

Public Sub Workbook_Open()
ThisWorkbook.Protect ("richard")
Sheet1.Protect ("richard")
'the previous line is repeated for all 60 sheets
End Sub

 

[DonkeyOte]

have you turned off screen updating and switched calculation to manual?

eg

Private Sub Workbook_Open()

Dim ws as Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each ws in ActiveWorkbook.Worksheets
ws.Unprotect Password:="password"
Next ws

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

and obviously same code for close with ws.protect replacing unprotect.