Tuesday, 30 November 2021
  3 Replies
  5.2K 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
2 years 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
2 years ago
·
#2361
0
Votes
Undo
Thanks again,

That works and simplifies what I am working on.

Viepyr
2 years ago
·
#2362
0
Votes
Undo
Good :)

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