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

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" <kartune85.29zhcb_1151282102.47@excelforum-nospam.com> wrote in
message news:kartune85.29zhcb_1151282102.47@excelforum-nospam.com...
>
> 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
>

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" <limbic_lesion@hotmail.com> wrote in message
news:ytKdnROHpbvQswLZRVny2A@bt.com...
> 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" <kartune85.29zhcb_1151282102.47@excelforum-nospam.com> wrote
> in message news:kartune85.29zhcb_1151282102.47@excelforum-nospam.com...
>>
>> 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
>>
>
>

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

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

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.

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" <kartune85.29zl9z_1151287203.8718@excelforum-nospam.com> wrote
in message news:kartune85.29zl9z_1151287203.8718@excelforum-nospam.com...
>
> 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
>