Workbook_Close event seems to stop unprotecting sheets...

This is a curly one... have posted on another forum too with no replies yet!

I have a spreadsheet which writes to a MS Access database on closing. The spreadsheet asks if the user is going to continue with the application when they close the sheet. If they are, it merrily runs off, writes to a database, returns the unique ID from the MS Access table, writes that to the file, saves the file then closes. Well, that's the plan.

However, Excel throws a wobbly when I try to write the unique ID to a sheet. The sheet is locked while I am doing the database access. I then unlock the sheet and attempt to write the ID back to a cell in the sheet. Excel throws the "Run time error '1004' - The cell or chart you are trying to change is protected and therefore read-only". Code below:

Please Login or Register  to view this content.

The place where it stops is the 3rd line of code. I can confirm that the sheet is still locked through the user interface.

Now as far as I can tell, my code is fine. What I believe is happening is that because I trigger this code from the Workbook_Close() event, somehow it refuses to lock/unlock any sheets because its in the middle of closing. But the strange thing is when I enter Debug mode, then go to the user interface, I can unlock the sheet using the Tools - Protection - Unprotect Sheet function. Once this is performed the code executes successfully.

Can anyone explain why Excel doesn't want to unlock sheets on the Workbook_Close event? Anyone else had the same problem?

Using Excel/Access 2003 on XP.