protecting multiple ranges in different sheets

[siddharth_kale]

Hi,

Is there a way in which I could protect A1:A5 and C1:C5 in different sheets?

It is quite tedious to select multiple ranges like this and allow specific users to edit these ranges and that too in different sheets.

Please let me know if there is a better way to do it.

Regards,
Siddharth

 

[Trevor G]

This is fairly ugly but will give you some code to work with.

code to unprotect all sheets

Sub Protect_Example1()
' Loop through all sheets in the workbook.
For i = 1 To Sheets.Count
Sheets(i).Unprotect

Next i
End Sub

code to protect all sheets

Sub Protect_Example1()
' Loop through all sheets in the workbook.
For i = 1 To Sheets.Count
Sheets(i).Protect

Next i
End Sub

Code to go through 2 sheets and do something with a range in each sheet, if you need more copy part of the code and alter sheet names.

Sub Macro2()
'
' Recorded Macro to Protect sheet but unlock ranges
'
Sheets("Sheet2").Select
Range("A1:A5,C1:C5").Select
Range("C1").Activate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Sheets("Sheet3").Select '
Range("A1:A5,C1:C5").Select
Range("C1").Activate
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub