Note: The other languages of the website are Google-translated. Back to English
Support is Online
We're back! We are here to assist you. Please be patient, we will respond to your tickets shortly.
Official support hours
Monday To Friday
From 09:00 To 17:30
  Tuesday, 30 November 2021
  3 Replies
  2.6K Visits
0
Votes
Undo
Greetings,

I would like to protect and unprotect multiple worksheets and disable the Select Locked Cells option so that the protected cells cannot be selected.

I have found some VBA code that can lock multiple worksheets at once and a bit of code for the Locked Cells bit but I have no clue on how to merge the two.

I have the cells I want unlocked through the Protection tab in the Alignment section on the Ribbon. 

For FYI: This is a different workbook than my earlier topic.

Sample code I found to protect all the worksheets:Sub ProtectAllWorksheetsWithInputbox()
'Step 1: Declare your variables
Dim ws As Worksheet
Dim Pwd As String
'Step 2: enter your password to protect all worksheets
Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
'Step 3: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Step 4: Protect and loop to next worksheet
ws.Protect Password:=Pwd
Next ws
End Sub

Code I found to disable the Select Locked Cells option:




  1. With ActiveSheet
  2. .Protect
  3. .EnableSelection = xlUnlockedCells
  4. End With



Thanks,

Viepyr
1 year ago
·
#2360
0
Votes
Undo
Hi Viepyr,

We've managed to merge the two codes, please try.

 Sub ProtectAllWorksheetsWithInputbox()
     'Step 1: Declare your variables
     Dim ws As Worksheet
     Dim Pwd As String
     'Step 2: enter your password to protect all worksheets
     Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
     'Step 3: Start looping through all worksheets
     For Each ws In ActiveWorkbook.Worksheets
         'Step 4.1: Protect and loop to next worksheet
         ws.Protect Password:=Pwd
         'Step 4.2: disable the Select Locked Cells
         ws.EnableSelection = xlUnlockedCells
     Next ws
 End Sub


Hope this works for you :)

Amanda
1 year ago
·
#2361
0
Votes
Undo
Thanks again,

That works and simplifies what I am working on.

Viepyr
1 year ago
·
#2362
0
Votes
Undo
Good :)

Amanda
  • Page :
  • 1
There are no replies made for this post yet.

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL