?
Solved

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

Posted on 2007-10-11
15
Medium Priority
?
799 Views
Last Modified: 2010-05-18
Hello,

In Microsoft Excel, how can I set up a macro to automatically run whenever a workbook saves or closes?

Thanks
0
Comment
Question by:hpsuser
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 14

Accepted Solution

by:
Jai S earned 600 total points
ID: 20055297
To answer the first part:

1. In the VBE (Open Excel ALT+F11), double click on "ThisWorkbook".
2. In the left drop down, choose Workbook.
3. Now put the name of your macro after the line:



Private Sub Workbook_Open()


This will now run your macro whenever you open Excel.

OR:

To run the macro on the Close Event:

Do 1 and 2 from above:

3. On the right dropdown menu choose "BeforeClose"
4. Put your macro name after the line:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20055578
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
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20055643
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:Jai S
ID: 20055676
@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...
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20055713
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
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20056041
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
0
 

Author Comment

by:hpsuser
ID: 20059053
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
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20059145
The routine All_Sheets_DealerCode7 should reside in a normal module ... is it so?

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20059166
    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
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20059289
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 :)
0
 

Author Comment

by:hpsuser
ID: 20060024
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.
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20060204
Did you placed the other macro's in the ThisWorkbook Code pane?
0
 
LVL 33

Assisted Solution

by:Jeroen Rosink
Jeroen Rosink earned 300 total points
ID: 20060211
the autoclose macro is not necesarrily placed in the  ThisWorkbook code pane.
And still make it run. Though it is overrulled by macros which is storede in the ThisWorkbook code pane.
Just double click in the explorer on ThisWorkbook and paste your code inthere.
0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 600 total points
ID: 20073807
Recall that to do your processing before a save you need to populate the beforesave workbook event handler i.e.

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

Note I would always advise however that you use the drop down in the VBE for workbook handlers as this ensures the routine is always correctly structured.

Chris
0
 

Author Comment

by:hpsuser
ID: 20238730
thanks everyone, I was able to use this code along with some other and got it working.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question