Workbook_BeforeClose?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kartune85 - 26 Jun 2006 01:33 GMT

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.

Signature

kartune85

Reply to this Message

N10 - 26 Jun 2006 01:46 GMT

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

> 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
[quoted text clipped - 16 lines]
>
> Kartune85.

Reply to this Message

N10 - 26 Jun 2006 01:48 GMT

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

> Try this
>
[quoted text clipped - 30 lines]
>>
>> Kartune85.

Reply to this Message

kartune85 - 26 Jun 2006 02:07 GMT

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

Signature

kartune85

Reply to this Message

GS - 26 Jun 2006 02:45 GMT

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

Reply to this Message

kartune85 - 26 Jun 2006 02:58 GMT

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.

Signature

kartune85

Reply to this Message

Bob Phillips - 26 Jun 2006 08:53 GMT

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

Signature

HTH

Bob Phillips

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

> 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.

Reply to this Message