'VBA editing cells in protected sheet

I have protected a workbook using vba code. I would also like to let the user entering some values in the specific cells (n4, p7:p10).

I have tried by using the following code but it doesn't work. A pop-up error message mentions "Error 1004".

Range ("n4,p7:p10").select
Selection.locked=false

vba

Solution 1:^[1]

Const wsPass As String = "Password123" '//Change to your password

For Each ws In ThisWorkbook.Sheets
    ws.Unprotect wsPass
    ws.Range("N4, P7:P10").Cells.Locked = False
    ws.Protect wsPass
Next ws

Just a further note - you can protect the sheet in VBA to lock it from the user, but still allow programmatic access (e.g. using a macro) by using the user interface option:

Sheets("RandomWorksheet").Protect Password:="Password123", UserInterfaceOnly:=True

This would negate the need for any "unprotect" macro as your code would still run without hindrance.

Solution 2:^[2]

If the sheet is protected you can unprotected it in your code. This is what I normally use.

Dim protect As Boolean
protect = False
If ActiveSheet.ProtectContents Then
        protect = True
        ActiveSheet.Unprotect Password:="password"
End If

Range ("n4,p7:p10").select
'Selection.locked=false

If Not (ActiveSheet.ProtectContents) And protect = True Then
            ActiveSheet.protect Password:="password"
End If

Just make sure you change password to your password

Solution 3:^[3]

This worked for me and didn't show the runtime error 1004:

Option Explicit
Private Sub Workbook_Open()
    Dim R As Range

    Worksheets("Tabelle1").Unprotect Password:="MYPASSWORD"
    For Each R In Worksheets("Tabelle1").Range("B3:L4, I8:M12, J13, Q13, J21, Q21")
        R.MergeArea.Locked = False
    Next
    Worksheets("Tabelle1").Protect Password:="MYPASSWORD", UserInterfaceOnly:=True
    ActiveSheet.EnableSelection = xlUnlockedCells    

End Sub

This routine does the following: The range in the for-each loop is editable by the user. The rest of the sheet is protected. The vba macro can still change all cells. The last command disables the selection of protected cells, so that no warnings will appear. Paste the code into the "ThisWorksheet" ("DieseArbeitsmappe") to set the protection when opening the file.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2
Solution 3

'VBA editing cells in protected sheet

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]