Dean,

I have XL2003 and was able to reproduce your problem... I don't know why
it's behaving like that, but, how about calling WorkbookBeforeSave before
executing Save in the code. For example, when you save the file from VB
code, try it this way:

Sub test()

If WorkbookBeforeSave(ThisWorkbook, False) Then
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End If

End Sub


--
Hope that helps.

Vergel Adriano


"Dean Meyer" wrote:

Worksheet.Unprotect within WorkbookBeforeSave event fails if Save initiated
by VB

-----------------------------------------------------------------------------------


I trap the WorkbookBeforeSave event successfully and reliably.

Within that event-handling code, I use the Worksheet.Unprotect method.

In the Workbook object:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = WorkbookBeforeSave(ThisWorkbook, SaveAsUI)
End Sub

In a general module:
Function WorkbookBeforeSave(TargetWorkbook As Workbook, Optional
ByVal SaveAsUI As Boolean) As Boolean
...
TargetWorkbook.Worksheet(1).Unprotect
If TargetWorkbook.Worksheets(1).ProtectContents
Then
i = MsgBox("Failed to unprotect", vbOKOnly,
"DEBUG")
End If
...
End Sub

Unprotect works perfectly reliably when the user initiates the save.

Unprotect reliably fails (with no error message) when the save is
initated by my VB code.
Sub Test()
TargetWorkbook.Save
End Sub

Any idea why? Any work-around?

BTW, I absolutely do not want to disable users' ability to save in the
manner they're familiar with, replacing the usual button with my own
Save command as some have suggested. This application must be easy to
use and compatible with other spreadsheets.