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").Cod eModule
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

--
kartune8
-----------------------------------------------------------------------
kartune85's Profile: http://www.excelforum.com/member.php...fo&userid=3558
View this thread: http://www.excelforum.com/showthread.php?threadid=55582

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

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").Cod eModule
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


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

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
------------------------------------------------------------------------
kartune85's Profile: http://www.excelforum.com/member.php...o&userid=35586
View this thread: http://www.excelforum.com/showthread...hreadid=555823

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.


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