Worksheet.Unprotect within WorkbookBeforeSave event fails if Save

[Dean Meyer]

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.

 

[Vergel Adriano]

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

 

[Vergel Adriano]

Correction. it should be like this

Sub test()

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

End Sub

 

[Dean Meyer]

Exactly the work-around I came to. But I still don't understand why this and
other methods fail when called from an event. If we knew the reason, we
could predict other problems. Oh well.... Thanks, Virgil! --Dean

 

[bta]

If TargetWorkbook Is ActiveWorkbook, a work-around is to execute the builtin
command bar control "File > Save" instead of TargetWorkbook.Save
Here a code example:

'http://support.microsoft.com/kb/213552
Const id_menu_item_SAVE As Long = 3
Const id_menu_item_SAVE_AS As Long = 748
Const id_menu_main_FILE As Long = 30002
Const title_worksheet_menu_bar As String = "Worksheet Menu Bar"


Dim builtin_save As CommandBarControl
Dim builtin_save_as As CommandBarControl



Private Sub FindSomeBuiltinCommands()

Dim menu_item As CommandBarControl
Dim popup_menu As CommandBarPopup



Set popup_menu =
Application.CommandBars(title_worksheet_menu_bar).FindControl(Type:=msoControlPopup, ID:=id_menu_main_FILE)

For Each menu_item In popup_menu.Controls
If menu_item.ID = id_menu_item_SAVE Then Set builtin_save = menu_item
If menu_item.ID = id_menu_item_SAVE_AS Then Set builtin_save_as =
menu_item
Next

End Sub



Private Sub DoSave()

Call builtin_save.Execute
' When using "Call ThisWorkbook.Save" instead,
"Worksheet.Unprotect/.Protect" does NOT work !!!
End Sub



Private Sub TEST()

Call FindSomeBuiltinCommands
Call DoSave
End Sub

 

[Anish Patel]

Just came across this issue in Excel 2007 as well.

The problem we were having was that you couldn't add a worksheet or delete a
worksheet.

Using this solution seemed to work.