Hi,

I have two workbooks, Book1 and Book2, both with macros (see bleow).

Here's my problem: If I open only Book2 in Excel and click the save button
or choose Save / Save As on the file menu, then the BeforeSave event fires
and runs fine, just like expected. So far so good.

When I open only Book1 and run the code in the dummy() sub, then when the
ActiveWorkbook.SaveAs instruction (Ref 1) executes, the code execution
continues in Book2 and the BeforeSave event fires, just like expected, and
all the code in the event handler does runs (I can single step from line to
line with F8) BUT none of the instructions do anything; the switch between
sheet 1 and 2 doesn't happen and the unprotection of sheet 1 doesn't happen
and when the "timestamp" is being written to cell A1 (Ref 2), I get run-time
error saying the worksheet is protected.


Any ideas why the code in BeforeSave doesn't work OK when the BeforeSave
event is fired by the SaveAs instruction?. Same problem if I replace SaveAs
with Save.

I DO want the code in BeforeSave to run also when the SaveAs instruction
executes.

I'm using Excel2003 on Win2k and WinXP.



Book1 has one worksheet, B1S1, which is password protected

Book2 has two worksheets B2S1 and B2S2.

The code in Book1 (located in ThisWorkbook) is:

'-------------------------------------------------------------------------------
Option Explicit

Sub dummy()
Dim wbk As Workbook

On Error GoTo errHandler

' Set wbk to reference the opened workbook
Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls")

' Switch back to this workbook and do some stuff...
ThisWorkbook.Activate
Range("A1").Value = Range("A1").Value + 1

' Switch to the opened workbook, Book2
wbk.Activate

' Disable "Overwrite Y / N / C?" question
Application.DisplayAlerts = False

' Save Book2
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Book2.xls" ' (Ref
1)

' Close Book2
wbk.Close

' Enable alerts
Application.DisplayAlerts = True

Exit Sub

errHandler:
Debug.Print Err.Number
Debug.Print Err.Description

End Sub
'-------------------------------------------------------------------------------


The code in Book2 (located in ThisWorkbook) is:

'-------------------------------------------------------------------------------
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Switch to sheet 2
B2S2.Activate
'
' Some more code here....
' Do some stuff with sheet 2
'

' Switch back to sheet 1
B2S1.Activate

' Unprotect sheet 1
B2S1.Unprotect "abc"

' Add timestamp to cell A1
Range("A1").Value = Str(Date) & " " & Str(Time) ' (Ref 2)

' Protect sheet 1
B2S1.Protect "abc"
End Sub
'-------------------------------------------------------------------------------


Regards,
Anders