×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Call macro on Exit or Close
2

Call macro on Exit or Close

Call macro on Exit or Close

(OP)
I have a simple macro set up to automatically save a file in CSV format.  How do I automatically call this macro when Save, Exit, or Close is selected?  

I found the Workbook_beforeClose event but what do I do with it?  The following doesn't do anything:  


Sub SaveAsCSV()
'
' SaveAsCSV Macro
' Macro to save as CSV on exit.
'

'
    ActiveWorkbook.SaveAs Filename:= _
        "C:\file.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Me.Saved = False Then Me.Save: SaveAsCSV
End Sub


Where do I need to put the Workbook_BeforeClose part?  

Thanks for helping this Visual Basic newbie!  

RE: Call macro on Exit or Close

Looks like you need to change

 If Me.Saved = False Then Me.Save: SaveAsCSV

to

 If Not Me.Saved Then call SaveAsCSV

If you want to confirm that the BeforeClose event code is running, drop the line

msgbox "BeforeClose was triggered"

in your Workbook_BeforeClose sub.

If you save your Excel workbook normally before you close then you won't call the CSV save routine because Excel Me.Saved will return true, even though your CSV wasn't created.  If you want to call SaveAsCSV every time then just put "call SaveAsCSV" as the only line in Workbook_BeforeClose.

RE: Call macro on Exit or Close

You need to name your macro "Workbook_BeforeClose(Cancel As Boolean)" which is the default empty macro in your workbook

TTFN



RE: Call macro on Exit or Close

(OP)
Thanks, handleman.  I didn't realize I needed the 'call' command.  I added the message so it looks like the BeforeClose event does not run.  Any suggestions?  

I see in Excel's VBA help 'Using Events with the Application Object' that it states I need a new class module.  I see how to do that.  Now I have the following in a Class Module called 'MyEventClassModule':  


Public WithEvents App As Application

Dim MyExcelClass As New MyEventClassModule

Sub InitializeApp()
    Set MyExcelClass.App = Application
End Sub
Public Sub Class_Initialize()
    Call InitializeApp
End Sub

Sub SaveAsCSV()
'
' SaveAsCSV Macro
' Macro to save as CSV on exit.
'

'
    ActiveWorkbook.SaveAs Filename:= _
        "C:\File.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "BeforeClose was triggered"
    Call SaveAsCSV
End Sub



and it still doesn't work.  Am I getting close?  

RE: Call macro on Exit or Close

I think I figured it out.  Any of the Workbook_ event routines have to be stored on the Workbook.  If they're stored elsewhere, such as in a Module, they won't be triggered.

On the left-hand side of the screen in the VBA editor you have the project explorer that shows Microsoft Excel Objects in one folder (Sheet1, Sheet1, Sheet3, ThisWorkbook), and I'm guessing in your case also a "Modules" folder containing "Module1".  That gets created automatically when you create a macro by recording, which is the easiest way to start.  If you double-click on "ThisWorkbook" you will get another code window where all your Workbook_ event procedures go.

So basically the final question in your first post was totally the correct question and totally unanswered by my reply.  Put the Workbook_BeforeClose part in the ThisWorkbook section of the Project Explorer.

Sorry for the roundabout!

RE: Call macro on Exit or Close

(OP)
Yes!!!!  That works.  Thank you.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources