BeforeClose problem

[bob engler]

I would like to save a WB each time it is closed. I have added this code
to the BeforeClose for the WorkBook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\DTS" &
Format(ActiveWorkbook.Sheets(1).Cells(3, 11), "mmddyy")

End Sub

I am trying to save the active WB in a file DTSmmddyy where the date comes
from Sheet 1 Cell 3,11 when the
WB is closed. This is a temporary workaround as I get all the WB data into a
DB to analyze historical data. I will
write a routine to load the data from the dated DTS WB after the DB is
ready.
When I close the WB it creates the first 2 DTS files OK as I change the
dates but none after that and the BeforeClose
function moves from the WB to sheet 1 when looking at the VB.

Putting a MsgBox between the 1st and 2nd line of the Sub gives the box on
the first 2 tries but just lets the WB close
without the box after that.

Any Ideas????


Thanks

 

[bob engler]

Ignore this. I had not saved the code to the WB. Too many interruptions when
working at home.