Run-time error '1004' after protecting all sheets

[Dicko]

I am unable to solve this particular problem and seek your kind assistance.
There are times when I need to change the password protection of all my sheets.
After unprotecting all the sheets, I use the following macro to re-protect (with new password) all the sheets:-

***********************************************************

Sub ProtectAllSheets()

Dim pwd1 As String, pwd2 As String
pwd1 = InputBox("Please Enter The New Password")
If pwd1 = "" Then Exit Sub
pwd2 = InputBox("Please Re-enter The New Password")
If pwd2 = "" Then Exit Sub

'Check passwords are identical
If InStr(1, pwd1, pwd2, 0) = 0 Or InStr(1, pwd1, pwd2, 0) = 0 Then
MsgBox "You Entered Different Passwords. No Action Taken"
Exit Sub
End If
For Each ws In Worksheets
ws.Protect Password:=pwd1, UserInterFaceOnly:=True
ws.EnableSelection = xlUnlockedCells
Next
MsgBox "All Sheets Have Been Protected With The New Password."
Exit Sub

End Sub

***********************************************************

Most of my sheets have the following code included to automate current date entry:-

col = Left(Target.Address, 2)
If col = "$D" Then Target.Offset(0, -3) = Now()

***********************************************************

During the session when I protect the sheets, all works well.
However, when I save and close the file and then re-open the file I receive the Run-time error '1004' in every sheet that has the above code and the following code is highlighted:-

Target.Offset(0, -3) = Now()

***********************************************************

Is there additional code or a change of code that is required to overcome this error?

 

[Darren Bartrup]

The main code doesn't appear to have any problems. Is Target ever less than column D? Is there a chance it could be column $AA or above? LEFT(Target.Address,2) will return $A in that case and try and offset by -3 columns so vanishing off the edge of the sheet and throwing an error.

Look at the column number rather than it's letter:

    If Target.Column = 4 Then
        Target.Offset(, -3) = Now()
    End If

 

[Jonmo1]

The UserInterFaceOnly setting is not saved upon save/close/reopen.
It has to be reset for each session of the book.
Usually done in a WorkBook_Open event code.

 

[Dicko]

Thank you for your replies which are always appreciated.
The problem that I face is with UserInterFaceOnly having to be reset for each session of the book.
I am avoiding using the WorkBook_Open event code as I want to change Sheet protection by using UnProtectAll & ProtectAll macros periodically and entering the password by dialog box.
The resetting of the UserInterFaceOnly seems to be the problem.
I will try to work out an alternative to my dilemma.
Thank you again,
Dicko