MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro looping for each sheet in workbook

Posted by Susan Golinsky on July 30, 2001 11:01 AM


I have written a macro to unprotect a sheet in a workbook, but need to loop it for each sheet. The macro will be used in various workbooks that each have a different number of sheets. Right now I have inelegantly copied the 2 lines of code more times than could possibly be needed so that it poops out when it gets to the end. I would prefer the elegant approach.

Thank for any suggestions,


Posted by Jerid on July 30, 2001 11:34 AM

Susan, here is one way. This function does both protect and unprotect, depending on what you pass it True or False.

Sub ProtectAll()
Call SheetProtection(True)
End Sub

Sub UnprotectAll()
Call SheetProtection(False)
End Sub

Public Sub SheetProtection(bProtectionOn As Boolean)
On Error GoTo ErrHandler

Dim Sht As Worksheet
Dim sCurrentSheet As String

sCurrentSheet = ActiveSheet.Name

Application.ScreenUpdating = False
For Each Sht In Application.Worksheets

If bProtectionOn = True Then
ActiveSheet.Protect userinterfaceonly:=True
Selection.Locked = True
ActiveSheet.EnableSelection = xlUnlockedCells
End If

Next Sht


Exit Sub

MsgBox (Err.Number & vbCrLf & Err.Description)
GoTo ExitHandler

End Sub