Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Tuesday, 30 November 2021
  3 Replies
  5K 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
·
#2362
0
Votes
Undo
Good :)

Amanda
2 years ago
·
#2361
0
Votes
Undo
Thanks again,

That works and simplifies what I am working on.

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
  • Page :
  • 1
There are no replies made for this post yet.