BeforeSave event fired but not working when triggered by SaveAs.

[Anders]

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

 

[JLGWhiz]

Maybe I'm missing something, but I don't see where you tell it to open
workbook2. You have wbk.Activate but it does not specify which one, so the
logic will tell it to activate ThisWorkbook. Maybe you didn't post the
complete code and I'm reading it wrong.

 

[Tom Ogilvy]

Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls")

would seem to indicate what wbk refers to.

 

[Anders]

Just like Tom says, the "Set wbk = Workbooks.Open...." instruction is what
opens Book2.

Note that the code below is a direct copy & paste of a fully working sample
that I wrote just for this post to demonstrate the problem. If anyone wants
to try it out then create two workbooks called Book1.xls and Book2.xls then
copy all code between the dashed lines and paste it in the ThisWorkbook
module of the two workbooks. You can save the workbooks anywhere you want as
long as both workbooks are in the same directory.

Regards,
Anders