CODEWITHSUNDEEP
vbaexcel
Oil Drum Alley
Oil Drum Alley

Reputation: 65

Error 400 when protecting a worksheet

Based on the value of a particular cell I need to, potentially, unprotect a worksheet, set a range to locked and reprotect the worksheet. Conversely if the value of the cell (in this case cell B4 is equal to "work") then I need to unprotect the worksheet, unlock the cells, and then reprotect the worksheet. The reason for this is that I want to stop the user tabbing to cells A8:B19 when cell B4 is not equal to work. When B4 = "work" the user can input numbers into cells A8:B19. There are limited options to input when B4 <> "work" and I have the setting to only tab between unlocked cells checked which makes input easier.

Currently I have this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
If Range("B4").Value = "work" Then
    Shapes("Rectangle 1").Visible = False
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    ActiveSheet.Range("A8:B19").Locked = True
    ActiveSheet.Protect  ‘When the code hits this line it throws error 400
    Application.EnableEvents = True
End If
If Range("B4").Value <> "work" Then
    Shapes("Rectangle 1").Visible = True
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    ActiveSheet.Range("A8:B19").Locked = True
    ActiveSheet.Protect ‘When the code hits this line it throws error 400
    Application.EnableEvents = True
End If
End If
End Sub

Clearly it depends on the value of cell B4 as to which "ActiveSheet.Protect" causes the error to occur, but it always does. Commenting out the offending line allows the VBA code to run as expected aside however it leaves the worksheet unlocked. I've tried moving the "ActiveSheet.Protect" line to further down the sub, calling it in a different sub etcetera and no luck, it always causes error 400. I'm aware that Elseif would no doubt be better practise however I changed from If, ElseIf End If to see if it made any difference. It didn't.

Curiously, I tried a similar thing to prove the principle in another Excel sheet with the following code:

   Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Dim i As Integer
    If Cells(1, 1) = "unlock" Then
        i = 1
        Shapes("Oval 1").Visible = False
        Application.EnableEvents = False
        ActiveSheet.Unprotect
        For i = 1 To 5
            Cells(i, 2) = i
        Next i
        ActiveSheet.Range("C1:C5").Locked = False
        ActiveSheet.Protect
    ElseIf Cells(1, 1) <> "unlock" Then
        i = 1
        Shapes("Oval 1").Visible = True
        Application.EnableEvents = False
        ActiveSheet.Unprotect
        For i = 1 To 5
            Cells(i, 2) = ""
            Cells(i, 3) = ""
        Next i
        ActiveSheet.Range("C1:C5").Locked = True
        ActiveSheet.Protect
    End If
    Application.EnableEvents = True
End If
End Sub

This works exactly as I'd expect, and doing a similar thing. Aside from the different cells and a for loop I can't see any difference between the two samples of code above with regards to the unprotect, change locked variable, protect process.

Baffled, any help much appreciated.

Upvotes: 2

Views: 1957

Answers (1)

SierraOscar
SierraOscar

Reputation: 17647

Use the UserInterfaceOnly argument - this protects the sheet, but still allows any programmatic changes to occur without the need to unprotect and reprotect:

Sheets("Some Sheet").Protect Password:="Pass123", UserInterfaceOnly:=True
Sheets("Some Sheet").Range("A1").Value = "Foo" '// code runs without error

You can use the Workbook_Open event to ensure any required sheets are locked in this way and then there's no need to manage it in any further code:

Private Sub Workbook_Open()

For Each ws In ThisWorkbook.Sheets
    ws.Protect UserInterfaceOnly:=True
Next

End Sub

Upvotes: 2

Related Questions