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

[Dean Meyer]

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.

 

[Dean Meyer]

My current solution: If VB wants to save, calls a procedure that
includes:

Sub FileSave(TargetWorkbook as Workbook)
If Not WorkbookBeforeSave(TargetWorkbook ) Then
Application.EnableEvents = False
TargetWorkbook .Save
Application.EnableEvents = True
End If

 

[Dean Meyer]

My current solution: If VB wants to save, calls a procedure that
includes:

Sub FileSave(TargetWorkbook as Workbook)
If Not WorkbookBeforeSave(TargetWorkbook ) Then
Application.EnableEvents = False
TargetWorkbook .Save
Application.EnableEvents = True
End If

 

[Dean Meyer]

My current solution: If VB wants to save, calls a procedure that
includes:

Sub FileSave(TargetWorkbook as Workbook)
If Not WorkbookBeforeSave(TargetWorkbook ) Then
Application.EnableEvents = False
TargetWorkbook .Save
Application.EnableEvents = True
End If