VBA protect not working on large spreadsheets

Paraquat Pete

New Member
Joined
Feb 19, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,298
Office Version
  1. 2010
Platform
  1. Windows
hi and thanks for your first post.
you need to reuse the password when you protect the sheets again

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

Paraquat Pete

New Member
Joined
Feb 19, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 19, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 20, 2004
Messages
3,298
Office Version
  1. 2010
Platform
  1. Windows
hilight your code an click here :)
1613962740153.png
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,690
Messages
5,637,841
Members
416,985
Latest member
mrindira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top