Unprotect sheets in workbook deactivate event

[TonyQ]

G'day Comrades,

This is my first post. I'm using Excel 2007. The file has most cells protected as the users interact with it through dialog boxes.

I have a some code in the Workbook_Deactivate event which 1) unprotects all sheets, 2) does some formatting, 3) re-protects all sheets before closing. This works fine when I take action to close the file. However, because it is used by several people who invariably leave it open and lock out other users, I added in a sub I found called TestForUpdates which checks for updates and closes the file if it is not in active use after five minutes.



' If EditDate <= Now - TimeValue("00:05:00") Then
' If ThisWorkbook.Saved = False Then
' ThisWorkbook.Save
' End If
' ThisWorkbook.Close
' Else
' Application.OnTime Now + TimeValue("00:00:20"), "TestForUpdates"
' End If

My problem - when the Workbook_Deactivate code runs following on from the "ThisWorkbook.Close" code cited above, the worksheet.unprotect
code (see below) does not appear to work.

For Each wsheet In ThisWorkbook.Worksheets
wsheet.Unprotect Password:="3256"
Next wsheet

I would appreciate some advice on this please.

Tony

[ajryan88]

Hi,

Try placing your worksheet unprotect code into the Workbook_BeforeClose subroutine also, found in the ThisWorkbook module.

Let me know if this helps

[TonyQ]

Thanks for the response mate, but the result is the same. The unprotect code is equally ineffective in the Workbook_BeforeClose sub.

[ajryan88]

OK,

Do you have a sample workbook that you could upload?

Thanks

[ajryan88]

Or...try placing the worksheet unprotect code just before ThisWorkbook.Close is called...

[TonyQ]

Excellent mate. I put it in another sub called just before ThisWorkbook.Close - it did the trick. Thanks very much.

[ajryan88]

Perfect!

No problems. Please don't forget to mark this thread as solved