Application Defined or object Defined Error 1004 after every cell update

[Skylar]

So I have some code that seems to giving me an error after everytime its exports data to a protected sheet. It appears after it exports to a cell; it will will lock up the sheet afterwards because when I call "unlocker" module above the export it runs. I don't not want to have to put this above every change as my full code is pretty long (and I'd like to know why this is happening). I have similar code on a bunch of other workbooks and no issues. Any Ideas?



Module:

Public Sub locker()
Sheets("Sheet1").Protect "Password"
ActiveWorkbook.Protect "Password"
End Sub

Code Example:

Private Sub save4later_Click()

Call unlocker
If locationtype.Value = "Option 1" Then
Sheets("Sheet1").Range("R3") = "Street Address 1"
'error will occur here unless call unlocker right above
Sheets("Sheet1").Range("R4") = "City Address 1"


Else
Sheets("Sheet1").Range("R3") = "Street Address 2"
'error will occur here unless call unlocker right above
Sheets("Sheet1").Range("R4") = "City Address 2"


End If



'Export of Data
'error will occur here unless call unlocker right above
Sheets("Sheet1").Range("E8").Value = Property.Text
'unprotected sheet error does not occcur

Sheets("Lists").Range("A15").Value = MeterID.Text 
'error will occur here unless call unlocker right above
Sheets("Sheet1").Range("k16").Value = Question1.Text
'error will occur here unless call unlocker right above
Sheets("Sheet1").Range("Q30").Value = sepques.Text
'error will occur here unless call unlocker right above
Sheets("Sheet1").Range("G51").Value = TestFlow.Text


Call locker
Me.hide

End Sub

Thanks

[JBeaucaire]

When a worksheet is locked using VBA, you have one extra parameter available to you that manual locking does not have, it is called UserInterfaceOnly:=True

Add this parameter to your PROTECT command and from that moment on while that session of Excel is active on that workbook, VBA will be able to operate on that sheet without the need to unlock anymore.

So, you could run your LOCKER program as part of a Workbook_Open macro to reset this flag (it does not persist, must be set each time the workbook is opened) and then all your other macros will be free to operate on that locked sheet.

Public Sub Locker()
    Sheets("Sheet1").Protect "Password", UserInterfaceOnly:=True
    ActiveWorkbook.Protect "Password"
End Sub

In ThisWorkbook:

Sub Workbook_Open()
    Call Locker
End Sub

[Paul_Hossler]

I assume that sub unlocker is just a reverse of sub locker

Do you have any event handlers in the workbook?

[Skylar]

When a worksheet is locked using VBA, you have one extra parameter available to you that manual locking does not have, it is called UserInterfaceOnly:=True

Add this parameter to your PROTECT command and from that moment on while that session of Excel is active on that workbook, VBA will be able to operate on that sheet without the need to unlock anymore.

So, you could run your LOCKER program as part of a Workbook_Open macro to reset this flag (it does not persist, must be set each time the workbook is opened) and then all your other macros will be free to operate on that locked sheet.

Public Sub Locker()
    Sheets("Sheet1").Protect "Password", UserInterfaceOnly:=True
    ActiveWorkbook.Protect "Password"
End Sub

In ThisWorkbook:

Sub Workbook_Open()
    Call Locker
End Sub

This worked! Thanks! I'll be using this from now on... Think of all that time I wasted!