Workbook_BeforeSave behaviour

[Werner Rohrmoser]

Hello,

below 2 code snippets:

1. Located in ThisWorkbook DocumentModule
************************************************************************************************************
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

With Worksheets("Tabelle3")
.Select
.Unprotect
End With
MsgBox "Hello"
End Sub
************************************************************************************************************
2. Located in a regular Module:
************************************************************************************************************
Option Explicit

Public Sub SaveProc()
ThisWorkbook.Save
End Sub
************************************************************************************************************

When I use the save button on the Excel menu it works, Tabelle3 is
selected and unprotected,
the message box appears.
When I use the macro located in the regular module Tabelle3 will be
NOT selcted and NOT unprotected,
but the message box shows up.
Any help would be appreciated.
Thanks.

WIN XP 5.1.2600 SP1 Build 2600
Excel 2002 SP3

 

[Bob Phillips]

Perhaps change it to

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

With Thisworkbook.Worksheets("Tabelle3")
.Select
.Unprotect
End With
MsgBox "Hello"
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Werner Rohrmoser]

Bob,
thanks for your reply.

I changed it, but the behaviour is the same, no change.

Regards
Werner

 

[bta]

Work-around :
Execute the built-in command bar control "File > Save" instead of
ThisWorkbook.Save
Here a code example:

1. Located in the ThisWorkbook Object:
======================================

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean _
)

With Worksheets("Tabelle3")
.Select
.Unprotect
.Range("B2").Value = Now
.Protect
End With

MsgBox "Hello"
End Sub



Private Sub Workbook_Open()
Call Worksheets("Tabelle3").Protect
Call FindSomeBuiltinCommands
End Sub



2. Located in a Module:
=======================

'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



Public Sub FindSomeBuiltinCommands(Optional ByVal
dummy_to_prevent_this_sub_from_beeing_listed_in_Tools_Macro_Macros As Boolean
= True)

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



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

 

[Werner Rohrmoser]

Thanks for the work-around.
I'm going to test and use it.

Werner