Private Sub Workbook_BeforeClose() really isn't working

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

I've managed to place it in module1, I'm deleting the code above it 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 is 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 I click the close button on the workbook that SHOULD tell the procedure 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 would appreciate any help I can get.

Kartune85

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" <kartune85.2a1dmn_1151370605.9541@excelforum-nospam.com> wrote
in message news:kartune85.2a1dmn_1151370605.9541@excelforum-nospam.com...
>
> You can't call this function as a procedure because it comes back with
> 'argument not optional'.
>
> I've managed to place it in module1, I'm deleting the code above it
> 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 is
> 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 I
> click the close button on the workbook that SHOULD tell the procedure
> 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 would
> appreciate any help I can get.
>
> Kartune85
>
>
> --
> kartune85
> ------------------------------------------------------------------------
> kartune85's Profile:
http://www.excelforum.com/member.php...o&userid=35586
> View this thread: http://www.excelforum.com/showthread...hreadid=555823
>

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

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.

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 Auto_Open()' and so far it's working everytime.

Hello, I am Boubacar, I live and work in Colorado, US. I love Excel Forum and it helps me a lot with my programmation. So great and rewarding to be a member of the excelforum community.