Loading Time - Maybe a quick way?

T

Trebby

Registered User.
Local time
Today, 13:40
Joined
Apr 18, 2015
Messages
10
Hi,

I'm wondering if you can advise?

I have a bit of code that does everything I need (allows ungrouping when sheets are protected) but when the workbook is first loaded it runs a lot of checks (worksheet to worksheet) and loading time is slow. I am wondering if my code can be adjusted or rewritten to make the load time faster?

Code: 
Private Sub Workbook_Open()
    With Sheet1
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
        With Sheet2
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
        With Sheet3
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
        With Sheet4
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet5
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet6
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet7
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet8
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet9
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet10
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet11
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet12
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet13
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet14
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet15
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet16
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet17
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet18
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet19
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet20
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet21
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet22
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet23
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet24
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet25
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet26
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet27
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
            With Sheet28
        .unprotect Password:="craig"
       .Protect Password:="craig", UserInterfaceOnly:=True
       .EnableOutlining = True
     End With
End Sub

Any help is greatly appreciated.

Thanks
 
Code: 
Try this out...
Private Sub Workbook_Open()

    Dim sht As Variant
    
    For Each sht In Worksheets
        sht.Unprotect "craig"
        sht.Protect "craig", UserInterfaceOnly:=True
        sht.EnableOutlining = True
    Next
    
End Sub
 
I honestly hope your VBA is locked along with the sheets, even with vba locked I know of a way to edit the workbooks source and change the password, but I'm sure this security is just for the user's curiosity protection lol.
 
BlueIshDan said:
Code: 
Try this out...
Private Sub Workbook_Open()

    Dim sht As Variant
    
    For Each sht In Worksheets
        sht.Unprotect "craig"
        sht.Protect "craig", UserInterfaceOnly:=True
        sht.EnableOutlining = True
    Next
    
End Sub
Click to expand...

Hi Dan,

Thanks for taking the time to look at this. Love the compactness of this code but unfortunately it's still jumping from sheet to sheet when loading up.

I honestly hope your VBA is locked along with the sheets, even with vba locked I know of a way to edit the workbooks source and change the password, but I'm sure this security is just for the user's curiosity protection lol.
Click to expand...

lol... yep. Still new to VBA, and I doubt there is code worth stealing or indeed information.
 
You can benifit a lot by turning off screen updating (costs a lot of resources) as well as turning of automatic calculatations if your spreadsheet uses a lot of formula's ....

Code: 
Application.ScreenUpdating = False
Application.Calculation = xlManual

Obviously dont forget to turn them back on :)

Code: 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 
namliam said:
You can benifit a lot by turning off screen updating (costs a lot of resources) as well as turning of automatic calculatations if your spreadsheet uses a lot of formula's ....

Code: 
Application.ScreenUpdating = False
Application.Calculation = xlManual

Obviously dont forget to turn them back on :)

Code: 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Click to expand...

Thanks Namliam - I think the issue I have is it's such a big file, with loads of formulas. Dan has shorted the code for me which is better. I will run with this for now, until I find an alternative method.

Thanks for your help guys.
 
Trebby, namliam has provided is a definite improvement performance wise. Use the code! lol

Code: 
Private Sub Workbook_Open()

    Dim sht As Variant

    Application.ScreenUpdating = False
    Application.Calculation = xlManual

    For Each sht In Worksheets
        sht.Unprotect "craig"
        sht.Protect "craig", UserInterfaceOnly:=True
        sht.EnableOutlining = True
    Next

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub
 
BlueIshDan said:
Trebby, namliam has provided is a definite improvement performance wise. Use the code! lol

Code: 
Private Sub Workbook_Open()
 
    Dim sht As Variant
 
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
 
    For Each sht In Worksheets
        sht.Unprotect "craig"
        sht.Protect "craig", UserInterfaceOnly:=True
        sht.EnableOutlining = True
    Next
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
End Sub
Click to expand...

Tried it - and it worked but still getting the loading time, which I believe is due to loads of worksheets being protected and grouping.

Again... Thanks for the time and help.
 
You must log in or register to reply here.

Similar threads

G
Solved How to sort filters on a protected sheet?
Replies
1
Views
636
goncalo
G
I
Combine data from multiple workbooks and sheets
Replies
0
Views
927
IainG
I
L
Copy multiple; sheets into one
Replies
9
Views
2,066
mdjks
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom