Private Sub Workbook_BeforeClose() really isn't working

[kartune85]

You can't call this function as a procedure because it comes back wit
'argument not optional'.

I've managed to place it in module1, I'm deleting the code above i
using:

Sub DeleteMostCode()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod
ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines - 61
.DeleteLines StartLine, HowManyLines
End With

End Sub

And I've got the procedure positioned at the bottom of the code and i
as follows:

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

It doesn't want to execute, otherwise I would see the MsgBox. When
click the close button on the workbook that SHOULD tell the procedur
to run but it just shows the alert: "Do you want to save?"

After I get this sorted out, the forms pretty much finished. I woul
appreciate any help I can get.

Kartune8

 

[NickHK]

Code such as
Private Sub Workbook_{All Events}
Needs to be on ThisWorkbook not a module.

Same as
Private Sub Worksheet_{All Events}
Needs to be on the WS that it referes to.

NickHK

 

[kartune85]

You're a champ Nick. I didn't realise that the code had to be in
'ThisWorkbook' (I didn't know code even went in there).

The procedure actually works now, I just have to tweak the code inside
it and it'll be sweet.

Thanks for all your help.

Kartune85

 

[kartune85]

I'm troubleshooting it now but it did it the first time I tried it but
now it doesn't want to execute it, even tho the code is sitting inside
'ThisWorkbook'. It's only slightly frustrating.

 

[kartune85]

I finally figured out that I had 'Application.EnableEvents' set to
'False' so once it ran the procedure, it disabled all the events,
including Workbook_BeforeClose. Even when I reopened it, it wouldn't
run.

I set 'Application.EnableEvents = True' in 'Sub Autpen()' and so far
it's working everytime.