Using VBA in Excel to run a macro whenever workbook saves or closes

I should think you want the beforesave and beforeclose events in the workbook i.e.

private sub workbook_beforeclose
    call your_macro_name
end sub

private sub workbook_beforesave
    call your_macro_name
end sub

Chris

Hello,

here the macros, to store in thisworkbook codepane:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call YourMacro
End Sub
Private Sub Workbook_BeforeSave1(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call YourMacro
End Sub


Jeroen

@chris
@roos
wht is the difference between what i said and what you have written ?
i think its always better to post a different approach rather than pasting the same thing again and again...

Hello,
There is a differnece in approach between chris and mines approach and yours. As ours run on different events,
I posted my code also though it is similar as Chris post with the addition that I also psoted the necessary strings to be used in this event.

Jeroen

jaiganeshsrinivasan

What Jeroen said largely, the fundamental principle is what you defined in the first post.

I 'corrected' it to refer to the beforeclose and beforesave events, (you put open and close)

Jeroen nearly corrected what I put :) to add the properties but incorrectly put beforesave1 rather than beforesave IM not so HO!

Chris

I'm not able to get it to work....here's the code I have, but when I close or save workbook nothing happens:

Private Sub Workbook_BeforeClose(cancel As Boolean)
    Call All_Sheets_DealerCode7
End Sub

Private Sub Workbook_BeforeSave1(ByVal SaveAsUI As Boolean, cancel As Boolean)
    Call All_Sheets_DealerCode7
End Sub

The routine All_Sheets_DealerCode7 should reside in a normal module ... is it so?

Chris

    Insert | Module to insert a code module into the project
     In the project tree select the module.
     Insert the required macro(s) into the selected module, ('Module1' or similar)
Close the Visual Basic Editor.

If you do have to move it make sure the 'original copy of your routine is deleted.

Chris

As Chris said: You have to leave the 1 out of the Private Sub Workbook_BeforeSave1(

I added the 1 in my macro to avoid running my macros when saving :)

It does reside in a normal module (not a worksheet code module).

I tried putting it in "sub auto_close()" and that seems to work, but what I really want is to have it run on save instead.

Did you placed the other macro's in the ThisWorkbook Code pane?

thanks everyone, I was able to use this code along with some other and got it working.