Clever Geek Handbook

VBA Excel action on workbook close

Is it possible for Excel to auto-execute when someone closes the file?

I am new to VBA on stackoverflow so please have mercy ...

Situation: I have an Excel file that is also used by several other people. This file publishes the mhtml file on save. This mhtml file will be saved with the date yesterday as "Dashboard 2015-01-12". The visible data in the mhtml file must contain the date associated with the file name. Observed data depends on one cell in this excel file, G2

I want the Excel file to do this: change one cell (G2) to yesterday's date. Then save it. Then close it

I want this action to be performed: when someone closes the file

The code is still there:

Sub sbWriteCellWhenClosing ()

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

End Sub

Edit:

Will this be done?

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


source to share


2 answers


This is 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
+6


source


You must use event Workbook.BeforeClose

: http://msdn.microsoft.com/en-us/library/office/ff194765%28v=office.15%29.aspx



to do this in the project window in the VBA editor, you need to use ThisWorkbook and place your code there. Your code will only work for books modified this way.

+2


source






More articles:


All Articles