Macro looping for each sheet in workbook

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

Hi,

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,

Susan

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
Sht.Activate

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

Next Sht

Worksheets(sCurrentSheet).Activate
Application.Range("A1").Select

ExitHandler:
Exit Sub

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

End Sub

Jerid