Protecting every sheet and not allowing to select locked or unlocked cells

**doubl3d80** · 04-25-2012, 12:00 PM

I have a workbook with about 50 sheets in it. I need to be able to protect all the sheets but also don't want the user selecting locked or unlocked cells. Below is the code that I have pieced together from other searching. Right now it is protecting the sheets but doesn't prompt for a password when un-protecting it. It also doesn't let you select but will have the cells highlighted from when it was saved. Thanks for any input and if you could also give code for unlocking the entire workbook at one time that would be appreciated.


Sub Protect_sheets()
 
    Dim wSheet          As Worksheet
    Dim Pwd             As String
 
    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        
    With wSheet
    .Protect
    .EnableSelection = xlUnlockedCells
    End With
        
    wSheet.Protect Password:=Pwd
    
    
    Next wSheet
 
End Sub

**DGagnon** · 04-25-2012, 12:14 PM

Try this:


Sub Protect_sheets()
 
    Dim wSheet As Worksheet
    Dim Pwd As String
 
    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        With wSheet
            .Protect Password:=Pwd
            .EnableSelection = xlNoSelection
        End With
    Next wSheet
 
End Sub

Sub UnProtect_sheets()
    Dim wSheet As Worksheet
    Dim Pwd As String
 
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        wSheet.Unprotect Password:=Pwd
    Next wSheet
End Sub