Lock and Unlock multiple tabs at once

  • Thread starter Thread starter ab
  • Start date Start date
A

ab

How can I lock and unlock multiple tabs at once. This workbook contains 100
tabs. I will be nice to lock and unlock all the tabs at once without having
to go thru the tabs individually
 
ab., you can use a macro like this

Sub Protect_All_Sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect

Next ws

End Sub



Paul B
 
Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
'Sheets(n).UnProtect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub



Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
'ws.UnProtect Password:="justme"
Next ws
End Sub



Gord Dibben MS Excel MVP
 
The VBA code works excellently but I cannot when the worksheets are locked make formatting changes to the unlocked cells. I have tried using the following statements but keep getting errors.

AllowFormattingColumns=True
AllowFormattingRows=True

What is wrong?
 
Sub Protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Next ws
End Sub


Gord Dibben MS Excel MVP
 
Back
Top