CODEWITHSUNDEEP
excelvba
Rafael Osipov
Rafael Osipov

Reputation: 740

Unprotect Specific Range In Protected Worksheet

I have a Worksheet that I need to deliver protected to users.

How can I protect the whole sheet except a specific range I want to leave them for changes.

Here is my code, it returns a run time error in the line .Locked = false.

Sub Protect()
With main
    .Protect Password:=1234
    .Range("U:V").Locked = False
    .Range("AH:AH").Locked = False
End With

With bakaraWS
    .Protect Password:=1234
End With

If segmenWS = "" Then
    Exit Sub
Else
    With segmenWS
        .Protect Password:=1234
        .Range("E:E").Locked = False
        .Range("H:H").Locked = False
    End With
End If
End Sub

Upvotes: 0

Views: 1278

Answers (1)

user10794223
user10794223

Reputation:

You've got to unlock cells before protecting the worksheet or protect the worksheet with the UserInterfaceOnly:=True argument so that VBA can manipulate a locked worksheet.

Sub Protect()

    With main
        .UnProtect Password:=1234
        .Range("U:V").Locked = False
        .Range("AH:AH").Locked = False
        .Protect Password:=1234
    End With

    With bakaraWS
        .Protect Password:=1234
    End With

    If segmenWS = "" Then
        Exit Sub
    Else
        With segmenWS
            .UnProtect Password:=1234
            .Range("E:E").Locked = False
            .Range("H:H").Locked = False
            .Protect Password:=1234
        End With
    End If

End Sub

If you want to manipulate the worksheet with VBA without unprotecting it first, use this alternative.

    With main
        .Protect Password:=1234, UserInterfaceOnly:=True
        .Range("U:V").Locked = False
        .Range("AH:AH").Locked = False
    End With

Upvotes: 1

Related Questions