VBA protect not working on large spreadsheets

[Paraquat Pete]

Hello. This is my first post on any type, though I have been reading several threads from your forums which has assisted me a lot.
But ... the threads on this and other forums have not assisted me with my issue namely VBA not protecting worksheets with a password and have tried various coding with the same effect.

The code is:
Sub lockdown()
For Each ws In ThisWorkbook.Worksheets
ws.Protect "Open"
Next ws
End Sub

I run the code on very small size (<2Mb) workbooks with the correct outcome - namely clicking the "Unprotect" button prompts a Unprotect Sheet Password message box. However, large workbooks (>4M) the code protects the sheets but does not lock it with a password. Clicking the Unprotect Sheet button unlocks the sheet. It is exactly the same coding. The sheets will lock manually with a password.

 

[diddi]

hi and thanks for your first post.
you need to reuse the password when you protect the sheets again

Sub lockdown()
    Dim ws as Worksheet
    For Each ws In ThisWorkbook.Worksheets
       ws.Protect Password:="mypassword"
    Next ws
End Sub

 

[Paraquat Pete]

Thanks diddi
Your code works well on small workbooks. But with anything over 10Mb in size the command:
'ws.Protect Password:="mypassword" '
does not do its job.
What would be causing this problem?

(Apologies about the original post not being indented. I had copied and pasted the code and, in my haste, did not notice that the formatting wasn't copied with it.)

 

[Paraquat Pete]

Thanks diddi
Your code works well on small workbooks. But with anything over 10Mb in size the command:
'ws.Protect Password:="mypassword" '
does not do its job.
What would be causing this problem?

(Apologies about the original post not being indented. I had copied and pasted the code and, in my haste, did not notice that the formatting wasn't copied with it.)

P.S. I had indented the above reply but formating was gone on posting.

 

[diddi]

hilight your code an click here

maintains indenting.

i am not sure what to suggest, it should work all the time. only thought i can make is to somehow split your worksheets into smaller pieces if possible, or if they are not sheets the user sees, strip out all the pretty formatting etc which bloats them considerably