I and others have written an Audit Trail for Excel 2007, which when working should be as close to CFR Part 11 compliant as I can get it. However, I am getting this error message when I close after entering data (see attached file to try it and for all of the workbook, modules and userform codes and access to the Custom UI -make sure to enable macros):
"Run-time error '1004': The cell or chart that you are trying to change is protected and therefore read only. To modify a cell or protected chart, firat remove protection Using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password.",
but this only occurs if I don’t use Save As before closing the file (using the ‘Close’ or ‘Exit Excel’ in the ‘Office Button’ dropdown menu or the X in the upper right hand corner of the window and only when changes were made). If Save As is selected before closing, the macro finds the passwords in the "Golbal Declarations" modue and works properly. If not, after I try to close, if I select ‘Debug’ the sections of the code with an asterix (*) before the lines below that are highlighted by the Debug. I have included the codes for both, when I enter data without clearing and it fails to lock the cells if you select ‘End’, and when I clear the data after entering and it fails to add the “-“ before the user name. The interesting thing is when I open the file that I ended the debugger (sometimes I have to open it, enable macros, close it and reopen it), then re-save it using Save AS and then exit; when I re-open the file the cells are locked and the “-“ is added to the user names. The attached file has gone through this process.

I see two options (there may be others, but these were what I thought of) that would be acceptable for use, whichever is easier to accomplish:
1. Block the ‘Close’ or ‘Exit Excel’ in the ‘Office Button’ dropdown menu or the X in the upper right hand corner of the window and have a message box appear “Must use ‘Save As’ before closing” before the Run-time error ‘1004’ is activated?
2. Fix the ‘Close’ or ‘Exit Excel’ in the ‘Office Button’ dropdown menu or the X in the upper right hand corner of the window so that they work the same as the ‘Save As’?

The Debug that I get for the Run-time error '1004' with data entry and not clearing anything before closing is below (to see the full code open the file as there is way to much to put here):

Else
' DO need to lock it and mark it as locked
If Not IsEmpty(Worksheets(sName).Range(cAddress)) Then
With Worksheets(auditWS.Range(awsWSNameCol & anyCellListed.Row).Value)
.Unprotect Password:=nonAuditWSPassword
* .Range(anyCellListed.Value).Locked = True
.Protect Password:=nonAuditWSPassword
End With
'record the locked status on the Audit Trail sheet
With auditWS
.Unprotect Password:=auditWSPassword
.Range(awsLockedCol & anyCellListed.Row) = "Locked"
.Protect Password:=auditWSPassword
End With
End If
End If

If IsEmpty(Worksheets(sName).Range(cAddress)) Then
' change the user id to have a "-" in front of it if it doesn't already
' so that username doesn't match for "can this user audit this data" later.
'rewritten 31 OCT 2011
'make sure the empty cell is unlocked on the report sheet
With Worksheets(auditWS.Range(awsWSNameCol & anyCellListed.Row).Value)
.Unprotect Password:=nonAuditWSPassword
.Range(anyCellListed.Value).Locked = False
.Protect Password:=nonAuditWSPassword
End With
'mark cells on the audit sheet with same username, sheet name, cell address
'with the "-" to allow editing/auditing by that person later.
For Each newTestCell In CellListRange
If auditWS.Range(awsWSNameCol & newTestCell.Row) = sName And _
auditWS.Range(awsCellCol & newTestCell.Row) = cAddress And _
auditWS.Range(awsUserCol & newTestCell.Row) = uName Then
'mark with "-", make sure we don't pile up the "-"s!
If Left(auditWS.Range(awsUserCol & newTestCell.Row), 1) <> "-" Then
auditWS.Unprotect Password:=auditWSPassword
* auditWS.Range(awsUserCol & newTestCell.Row) = _
* "-" & auditWS.Range(awsUserCol & newTestCell.Row)
auditWS.Protect Password:=auditWSPassword
End If
End If
Next
'turn event processing back on!
Application.EnableEvents = True
Else
' DO need to lock it and mark it as locked