Workbook_BeforeClose?
 

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.


--
kartune85
------------------------------------------------------------------------
kartune85's Profile: http://www.excelforum.com/member.php...o&userid=35586
View this thread: http://www.excelforum.com/showthread...hreadid=555427

Workbook_BeforeClose?
 

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

"kartune85" wrote in
message ...

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.


--
kartune85
------------------------------------------------------------------------
kartune85's Profile:
http://www.excelforum.com/member.php...o&userid=35586
View this thread: http://www.excelforum.com/showthread...hreadid=555427

Workbook_BeforeClose?
 

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

"N10" wrote in message
...
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

"kartune85" wrote
in message ...

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.


--
kartune85
------------------------------------------------------------------------
kartune85's Profile:
http://www.excelforum.com/member.php...o&userid=35586
View this thread:
http://www.excelforum.com/showthread...hreadid=555427

Workbook_BeforeClose?
 

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


--
kartune85
------------------------------------------------------------------------
kartune85's Profile: http://www.excelforum.com/member.php...o&userid=35586
View this thread: http://www.excelforum.com/showthread...hreadid=555427

Workbook_BeforeClose?
 

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

Workbook_BeforeClose?
 

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.


--
kartune85
------------------------------------------------------------------------
kartune85's Profile: http://www.excelforum.com/member.php...o&userid=35586
View this thread: http://www.excelforum.com/showthread...hreadid=555427

Workbook_BeforeClose?
 

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)

"kartune85" wrote
in message ...

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.


--
kartune85
------------------------------------------------------------------------
kartune85's Profile:
http://www.excelforum.com/member.php...o&userid=35586
View this thread: http://www.excelforum.com/showthread...hreadid=555427