Run-time error '1004'

[eherron]

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 linked file https://skydrive.live.com/view.aspx?cid=E64C264EBE8F1355&resid=E64C264EBE8F1355!159 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 in italics below is 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.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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’?
<o></o>
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):

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

<o></o>
The Debug that I get for the Run-time error '1004' with data entry and not clearing anything before closing is below (again to see the full code open the file):

 If IsEmpty(Worksheets(sName).Range(cAddress)) Then

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

 

[eherron]

While no one here sent a reply, I got my error corrected. If anyone is interested in the final result they can veiw the code in the attached file link to 'Audit Trail 10-31-11 ABC-3' https://skydrive.live.com/view.aspx?cid=E64C264EBE8F1355&resid=E64C264EBE8F1355!160. To see how the Ribbon (Menu Bar) was hidden, you will need to open the file in the 'Custom UI Editor for Microsoft Office', which is a free download at http://openxmldeveloper.org/cfs-file.ashx/__key/communityserver-componen...
Gene