Handling the WorkbookAfterSave Event in VBA
 

Hi Chip,

I tried this, but WorkbookAfterSave isn't listed in the dropdown list of
events for XLApp. I see WorkbookBeforeSave, WorkbookAfterXmlImport, and a
few dozen other events, but not WorkbookAfterSave.

I'm using Excel 2007. Do I need Excel 2010?

Thanks.

Steve

"Chip Pearson" wrote in message
...
Because application events are for all open workbooks, you need to use
the WorkbookAfterSave event, which is called when any workbook is
saved. The WB parameter references the workbook that was saved.

E.g., in Class1,

Public WithEvents XLApp As Excel.Application

Private Sub Class_Initialize()
Set XLApp = Application
End Sub

Private Sub Class_Terminate()
Set XLApp = Nothing
End Sub

Private Sub XLApp_WorkbookAfterSave(ByVal Wb As Workbook, _
ByVal Success As Boolean)
MsgBox "AfterSave: " & Wb.Name
End Sub

Then, in ThisWorkbook:

Private XLEvents As Class1

Private Sub Workbook_Open()
Set XLEvents = New Class1
End Sub

As long as the workbook containing this code remains open, you'll get
WorkbookAfterSave events when any workbook is saved.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Sun, 16 May 2010 15:00:33 -0400, "Steve Flaum"
wrote:

I can handle the Excel WorkbookAfterSave event in VB 2010 as explained at
http://msdn.microsoft.com/en-us/libr...ffice.14).aspx.
I
can also handle built-in events of the Excel application in VBA as
described
at http://msdn.microsoft.com/en-us/libr...ffice.10).aspx.
However, I cannot figure out how to handle the WorkbookAfterSave event in
VBA. If I use a statement such as:

Private WithEvents ExcelApp as Application

a number of application events are available, but WorkbookAfterSave isn't
one of them.

I suppose I could create a VB 2010 component which catches and rethrows
the
event, but there must be a better way. Can anyone tell me what it is?

Thanks.