VBA Excel action when closing workbook [duplicate]

Question

Is it possible automatically let Excel do an action when someone closes the file?

Situation: I have an Excel file which is also used by several other people. This file publishes a mhtml file whens saving. This mhtml file will be saved with the date of yesterday like "Dashboard 2015-01-12". The seen data in the mhtml file has to contain the date related to the file name. The data seen depends on a single cell in this excel file, G2.

I want the Excel file to do this: change a single cell (G2) into the date of yesterday. Then save it. Then close it.

I want this action to be done: when someone is closing the file.

Code I have so far:

Sub sbWriteCellWhenClosing()

Workbooks("BOOK1.XLS").Close SaveChanges:=True
Range("G2") = Format(Now - 2, dd - mm - yy)

End Sub

Edit:

Will this do the job?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Range("G2") = Format(Now - 1, dd - mm - yy)
   ActiveWorkbook.Close SaveChanges:=True
End Sub

Answer 1

This is using the Workbook code:

Option Explicit

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

In a separate Module:

Sub sbWriteCellWhenClosing()

    ActiveSheet.Range("G2") = Format(Now - 1, "dd-mm-yy")   '-1 for yesterday
    ActiveWorkbook.Save

End Sub