File takes a long time to open...sometimes

[u742884]

I have a file with a ton of formulas so it takes a little while to open. However sometimes it takes a VERY long time to open. I have an auto_open sub:

Sub auto_open()
With Application
.Calculation = xlManual
End With
Splash.Show
AllLock
Application.Goto Reference:="VeryStart"
End Sub

Splash is a form with button that just kills the form.
AllLock is another sub:

ub AllLock()
Sheets("Start").Select
Range("A1").Select
LockSheet

Sheets("A").Select
Range("A1").Select
LockSheet

Sheets("B").Select
Range("A1").Select
LockSheet

Sheets("C").Select
Range("A1").Select
LockSheet

.
. Repeats until Sheets("K). A-J are identical; K has very little on it.
.

End Sub

When this all works properly, the file opens, calculates (that's the lengthy part), then you quickly see it run through the sheets as it locks them--2 seconds or so to finish all of them. Other times, you see it move painfully slow between each sheet--perhaps a minute on each one!

Related to this problem, sometimes when I just select one of the tabs, I have to sit and wait a while before being able to maneuver between the cells.

Anyone have any ideas? Maybe turning the windows off might help?

 

[Norie]

What does LockSheet actually do?

What about turning off screen updating?

Sub AllLock()

    Application.ScreenUpdating = False
    
    Sheets("Start").Select
    Range("A1").Select
    LockSheet

    For I = Asc("A") To Asc("K")
        Sheets(Chr(I)).Select
        Range("A1").Select
        LockSheet
    Next I
    
    Application.ScreenUpdating = True
    
End Sub

 

[u742884]

Locksheet is pretty inelegant:

Sub LockSheet()
ActiveSheet.Protect "mypassword"
End Sub

I will try the screen updating--can't hurt.

Thanks.