I am trying to have a macro run before a work book closes. All this
macro does is sort and format the new data that has been entered. I
used the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "Sort"
End Sub

This is working and I am not sure what I have done wrong. Is it not
possible to call the macro this way? I would just put the entire macro
code in the before close but the macro that I want to run calls another
macro once it is finished. Any ideas or reference material online that
I can take a look at?

Thanks for the help once again

Kris

Is the Sort Macro in a general/standard module in the same workbook?

Public Sub Sort()
' code that sorts
' code that saves the workbook
End Sub

If so, your code ran fine for me.

--
Regards,
Tom Ogilvy


"Kris" wrote in message
oups.com...
I am trying to have a macro run before a work book closes. All this
macro does is sort and format the new data that has been entered. I
used the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "Sort"
End Sub

This is working and I am not sure what I have done wrong. Is it not
possible to call the macro this way? I would just put the entire macro
code in the before close but the macro that I want to run calls another
macro once it is finished. Any ideas or reference material online that
I can take a look at?

Thanks for the help once again

Kris

Yes it is in the same workbook and is
Sub Sort()
'sorting code
End Sub

However when I close the workbook all that happens... AH now I think I
know why.

I don't have code that saves the workbook after it runs the code.

I don't think I am understanding this correctly.

I have:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "Sort"
ActiveWorkbook.Save
End Sub

Now when I close the workbook it should run the macro Sort, save the
workbook, and then close it. But when I open my workbook up again it is
the same as it was when I clicked close, the macro didn't run or didn't
save after it ran, what am I doing wrong?

Is this macro in the ThisWorkbook Module?

If not, it won't run

You don't disable events anywhere?

Change ActiveWorkbook to ThisWorkbook although it should be the
activeworkbook at that time.

Put in a message to see if it works (assumes the sheet you are sorting is
the activesheet). You can always take out the msgbox command after you get
it working.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "Sort"
msgbox "Look at the sheet, is it sorted?"
ThisWorkbook.Save
End Sub



--
Regards,
Tom Ogilvy


"Kris" wrote in message
oups.com...
I don't think I am understanding this correctly.

I have:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "Sort"
ActiveWorkbook.Save
End Sub

Now when I close the workbook it should run the macro Sort, save the
workbook, and then close it. But when I open my workbook up again it is
the same as it was when I clicked close, the macro didn't run or didn't
save after it ran, what am I doing wrong?

Hi Kris,

Review Tom's solution! The code to save the workbook goes after the sort
code, in Sub Sort()!!

Regards,
GS

Tom suggested to have "Sort" as a public sub.
Workbook_before_close can be private. I would support Tom's hint.

Udo

Oh yeah, did I mention...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Sort()
End Sub

Good luck,
GS

I moved all of my macro's into the same module. And I changed
ActiveWorkbook to ThisWorkbook and added the message box. And it still
does not run (I didn't see the message box and the data was not
sorted.) Does the order of the Macro's in the module make a difference?
Also my macro takes data from the main worksheet and then creates new
worksheets based on that data, if this makes any difference at all. I
haven't disabled events so far in that macro, however I do disable
alerts in the sort macro, run a few functions, and then reenable them.

Tom thanks for all the help so far.

Event code for the BeforeEvent has to be in the ThisWorkbook module. You
can't put it just anywhere.

In the VBE, go to the project explorer and select you project. Go to the
thisworkbook entry for that project and right click on it - select view
code. You r code should be in that module.

See Chip Pearson's page on Events

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy




"Kris" wrote in message
oups.com...
I moved all of my macro's into the same module. And I changed
ActiveWorkbook to ThisWorkbook and added the message box. And it still
does not run (I didn't see the message box and the data was not
sorted.) Does the order of the Macro's in the module make a difference?
Also my macro takes data from the main worksheet and then creates new
worksheets based on that data, if this makes any difference at all. I
haven't disabled events so far in that macro, however I do disable
alerts in the sort macro, run a few functions, and then reenable them.

Tom thanks for all the help so far.

GS raises a good point which I hadn't focused on or mentioned. For emphasis
to Kris

since Sort is in the same workbook you can call it just by listing its name
or using the optional call command

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Sort()
ThisWorkbook.Save
End Sub


or

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sort
ThisWorkbook.Save
End Sub

--
Regards,
Tom Ogilvy



"GS" wrote in message
...
Oh yeah, did I mention...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Sort()
End Sub

Good luck,
GS

See I knew I wasn't understanding something simple. That did it Tom
Thanks! Thank you too GS and Udo.