run macro at closing of the workbook in Excel

Q140. I want to automatically run a procedure whenever I close my WorkBook so that it is in a known state - how can I achieve that?

For example, every time the user closes the WorkBook, I would like to ensure that certain Sheets are hidden, log out if logged in as admin and do some calculations and updates.

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Private Sub WorkBook_BeforeClose(Cancel As Boolean)
  3. Call ResetMyWorkBook
  4. End Sub

a) The above macro code needs to be included under "ThisWorkBook" as shown below. When the WorkBook is closed, "WorkBook_BeforeClose" procedure is run which in turn calls "ResetMyWorkBook".

excel run macro at closing of the workbook

  1. Option Explicit
  2. Public Sub ResetMyWorkBook()
  3. 'Reset code goes here.
  4. End Sub

b) The custom "ResetMyWorkBook" macro code needs to be included under "Modules" as shown below.

excel excel run macro at closing of the workbook

 

You can find similar Excel Questions and Answer hereunder

1) How do I assign a macro to a shape using VBA?

2) How can I execute an event at a predetermined time from my Macro?

3) How can I prevent users from seeing / accessing my macro code?

4) I have a very complex macro that runs for a long time - is there any way I can set up an audible alarm indicating macro run is over?

5) How can I clear cell after activating a routine when there is a change in value of a cell?

6) I frequently use a Macro - is there a way to quickly access the Macro using a Keyboard Shortcut?

7) I want to automatically run an initialization procedure whenever I open my WorkBook so that it is ready for use - how can I achieve that?

8) I have a WorkBook that loads a form automatically when it is opened. In Excel, how can I suppress the form from loading on file open when required?

9) How can I make my macro wait for 5 secs before executing the next command?

10) How can I quickly navigate to a frequently used field in a voluminous worbook?

11) Vba code to password protect workbook in Excel

12) How can I save a WorkSheet as a new WorkBook using VBA?

13) How can I add a Timestamp after macro execution?

14) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

15) Calling a macro from another workbook in Excel