Protecting and Locking A Range on a Series of Worksheets

[davidlocke83]

I'm attempting to protect the entire worksheet except for a set range for a series of worksheets that are identical. The workbook is comprised of time surveys for a number of cost centers. Each time survey is identical. However, I do not want them overriding some items and I don't want them to be able to view some hidden columns. Step 1 was using a Protect VBA, which works fine. I've run into problems with the unprotecting just the range.

I will start by saying I'm extremely green at VBA and I have piecemealed the below together using several forums. While I've been getting several errors, the last one is an "Error 91: Object Error Not Set" at the "Set Input Range = ws.Range ("A40:AT350")".


Sub UnProtectRangeInAllWorksheets()
Dim ws As Worksheet
Dim Pwd As String
Dim InputRange As Range
Set InputRange = ws.Range("A40:AT350")
Pwd = InputBox("Enter your password to unprotect all worksheets", "Unprotect Worksheets")
On Error Resume Next
For Each ws In Worksheets
If InputRange.Locked = True Then
ws.Unprotect Password:=Pwd
End If
If Err <> 0 Then
MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
End If

On Error GoTo 0
Next ws

End Sub

 

[TryingToLearn]

Try this:

Sub UnProtectRangeInAllWorksheets()

Dim ws As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Unprotect Worksheets")
On Error Resume Next
For Each ws In Worksheets
    With ws
        'ALLOW CODE TO MAKE CHANGES WITHOUT UNPROTECTING SHEET
        .Protect Password:=Pwd, userinterfaceonly:=True, DrawingObjects:=True
        Range("A40:AT350").Locked = False
        If Err <> 0 Then
            MsgBox "You have entered an incorect password. All worksheets could not " & _
             "be unprotected.", vbCritical, "Incorect Password"
        End If
    End With
    On Error GoTo 0
    'RESET ERROR CODE
    Err.Clear
Next ws
End Sub

 

[davidlocke83]

Try this:

Sub UnProtectRangeInAllWorksheets()

Dim ws As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Unprotect Worksheets")
On Error Resume Next
For Each ws In Worksheets
    With ws
        'ALLOW CODE TO MAKE CHANGES WITHOUT UNPROTECTING SHEET
        .Protect Password:=Pwd, userinterfaceonly:=True, DrawingObjects:=True
        Range("A40:AT350").Locked = False
        If Err <> 0 Then
            MsgBox "You have entered an incorect password. All worksheets could not " & _
             "be unprotected.", vbCritical, "Incorect Password"
        End If
    End With
    On Error GoTo 0
    'RESET ERROR CODE
    Err.Clear
Next ws
End Sub

So that is unprotecting and unlocking the whole sheet; whereas I'm just trying to unlock and unprotect that one range. Would an If function here work? Like IF it is in that range unlock Else Lock?

 

[davidlocke83]

Try this:

Sub UnProtectRangeInAllWorksheets()

Dim ws As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Unprotect Worksheets")
On Error Resume Next
For Each ws In Worksheets
    With ws
        'ALLOW CODE TO MAKE CHANGES WITHOUT UNPROTECTING SHEET
        .Protect Password:=Pwd, userinterfaceonly:=True, DrawingObjects:=True
        Range("A40:AT350").Locked = False
        If Err <> 0 Then
            MsgBox "You have entered an incorect password. All worksheets could not " & _
             "be unprotected.", vbCritical, "Incorect Password"
        End If
    End With
    On Error GoTo 0
    'RESET ERROR CODE
    Err.Clear
Next ws
End Sub

This does work. Key for those that try to use this for their own work, is to make sure the file starts as unproctected. If you run a full protection, and then run this macro it unproctects everything for some reason.

 

[TryingToLearn]

1. The wording "unprotect all worksheets" was in your macro.
2. The '.Protect Password:=Pwd, userinterfaceonly:=True, DrawingObjects:=True' should not unprotect the worksheets but just allow the macro code to make changes.
3. The original macro asked for input of a password so I assumed it was not protected to start.
4. For that matter, why not just protect the sheet with the relevant range unlocked already and no macro would be needed.

Hope this helps. I will not be available to check for your reply for a while.
Another alternative would be to assign a different password to each ws, have each center enter their own password which would only unprotect the single worksheet to be used. This could be done using the WorkBook Open section. Sorry for no code but have a plane to catch.

 

[davidlocke83]

1. The wording "unprotect all worksheets" was in your macro.
2. The '.Protect Password:=Pwd, userinterfaceonly:=True, DrawingObjects:=True' should not unprotect the worksheets but just allow the macro code to make changes.
3. The original macro asked for input of a password so I assumed it was not protected to start.
4. For that matter, why not just protect the sheet with the relevant range unlocked already and no macro would be needed.

Hope this helps. I will not be available to check for your reply for a while.
Another alternative would be to assign a different password to each ws, have each center enter their own password which would only unprotect the single worksheet to be used. This could be done using the WorkBook Open section. Sorry for no code but have a plane to catch.

No worries. It is working great. I thought it was broken, but it wasn't. Just need to make sure it is clear of protection before starting. On 4, I have 60 sheets that I'm applying this too. I didn't think you could apply a range lock and protect on multiple sheets at once without a macro.