Workbook_BeforeClose?

[kartune85]

Am I able to run procedures as per the following code? I thought seeing
as it is a Private Sub in module "B4Close" that it would run
automatically before the workbook closes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CopyData1
MsgBox "Data copied, now click OK to save and close"
ThisWorkbook.Save
Application.EnableEvents = False
Application.DisplayAlerts = False
Cancel = True
End Sub

I just want to duplicate data from cells on sheet1 to sheet2 and then
save (and remove the "Do you want to save..." dialog) before the
Workbook closes.

Can anyone guide me on this one?

Kartune85.

 

[N10]

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CopyData1
MsgBox "Data copied, now click OK to save and close"
ThisWorkbook.Save
Application.EnableEvents = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Cancel = True
End Sub

N10

 

[N10]

oops try this

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CopyData1
MsgBox "Data copied, now click OK to save and close"
Application.EnableEvents = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Cancel = True
End Sub

n10

 

[kartune85]

It's not actually running the sub. I've got the code in a module of it's
own plus the 'Sub CopyData1' procedure. But when I close the Workbook it
doesn't show the MsgBox but it still shows the "Do you want to save..."
dialog.

I haven't called the procedure from anywhere but from what I gathered,
it's not neccessary to call these particular types of procedures.

Kartune85

 

[Guest]

This procedure must be in the ThisWorkbook module to fire before the
workbook is closed. CopyData1() can be in a standard module OR, if it's only
called in the Workbook_BeforeClose event procedure, it could be in that
module.

HTH
Regards,
Garry

 

[kartune85]

Is there problems with having more than one module in a workbook.

I've created a second module because I need to 'Sub DeleteModule()' for
one of them because it contains the 'Sub Auto_Open()' which I don't want
to carry over when I SaveAs. Both modules ("module1", "B4Close") are in
the same Workbook.

 

[Bob Phillips]

But where is your Workbook_BeforeClose? It must be in ThisWorkbook, not a
code module.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)