Unprotecting not working - 1004 error

[plsm5882]

Hi.

Unprotecting is failing for some reason. I am using UserInterfaceOnly:=True but it still complains.

Public Sub SaveWB()
Dim wSh As Worksheet
Set wSh = ThisWorkbook.Worksheets("Summary")
wSh.Unprotect

With Application
.DisplayAlerts = False
.ScreenUpdating = False
On Error Resume Next
wSh.Range("C5").Value = Hour(Now) & ":" & Minute(Now) & ":" & Second(Now) ' Fails here with 1004 error stating the sheet is still protected
If err.Number = 0 Then
ThisWorkbook.Save
If err.Number <> 0 Then
Call ErrMsg(err.Number, err.Description, "Error Saving", "The spreadsheet could not be saved. Please make sure the location where you open it from is still available.")
On Error GoTo 0
End If
End If
.ScreenUpdating = True
.DisplayAlerts = True
End With

wSh.Protect UserInterfaceOnly:=True

Set wSh = Nothing
End Sub

Hope someone has some insights.

Thanks

 

[jasonb75]

Did you remove the password from your code before posting, or did you forget to put it in?

It should be

wSh.Unprotect Password:= "your password goes here"

Although I would have expected a prompt for the password before it got to the problem line if that was the case.

 

[plsm5882]

No, I dont use a password. I simply use the protection to stop accidental deletion of data. Unprotect works everywhere else. Is it not optional then?

Anyway, I've tested if it makes a difference to the error and unfortunately it doesn't.

 

[jasonb75]

If there is no password then as far as I can see, your code should have worked, I've tried to make it fail without success.

If you run

Sub Test()
Dim wSh As Worksheet
Set wSh = ThisWorkbook.Worksheets("Summary")
wSh.Unprotect
End Sub

does it remove the protection and allow you to edit the sheet manually?

 

[plsm5882]

It does. I protect and unprotect 7 or 8 times in total, this sheet several other times and other sheets too. They all work except for this one. It's odd and very frustrating...

I'll try some error handling...

Also, unly using interface only protection, instead of unprotecting and protecting, could I unlock and lock the cell?

As far as I know I shouldn't have to do either when using interface only...

 

[jasonb75]

Interface only protection will only work after it has been unlocked once.

Common practice would be to use the Workbook_Open code event to sort the protection out so that it is ready for any other code.

Private Sub Workbook_Open()
Dim ws As Workbook
For Each ws In ThisWorkbook.Worksheets
    ws.Unprotect
    ws.Protect UserInterfaceOnly:=True
Next
End Sub

 

[plsm5882]

Yep, already doing that. I assume you meant "Dim ws As Worksheet"?

Could it be because I call it from:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call SaveWB
End Sub

 

[jasonb75]

In that case, I see no reason for the problem that you're having, I have tested your code and it works fine.
As you say that the problem is only on one sheet, it could be a problem with that specific sheet, I would suggest creating a new sheet and see if it works on that.

 

[plsm5882]

I cleaned up all my code and it seems OK now. Not sure what caused it.