run macro on Save of Another Workbook from XLSM file

Question

Hi Guys i have a small issue that I need help with.

I got a xlsm file named "vba.xlsm" that I have set in a directory to be automatically open when any excel file is open. This using open At Startup Option of excel.

Now i need to run a a code on the xlsm file, when any file is saved. The code is in the the xlsm file, lets , and need to be run when any excel file is opened and saved.

Any ideas on how i can achieve this?

Clarification :

I have a folder in my local drive "C:\xls" where i have a xlsm file "vba.xlsm".

This vba.xlsm is opened every time a excel workbook is open or created.

in this vba.xlsm there is a piece of code that i need to be executed everytime a workbook is saved.

Is there a way i can garantee my code in vba.xlsm is run when a workbook is saved?

I have tried the Workbook_BeforeSave and Workbook_AfterSave events, but these are only fired if the vba.xlsm is saved, not if other workbook is saved.

Any idea how i can detect the save event of another workbook from my vba.xlsm ?

@TimWilliams

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "saveEvent"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Public WithEvents appevent As Application
Attribute appevent.VB_VarHelpID = -1
    
Private Sub appevent_WorkbookAfterSave(ByVal Wb As Workbook, ByVal Success As Boolean)
   MsgBox Wb.Name & "Saved"
End Sub

Private Sub appevent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
   MsgBox Wb.Name & "Before Save"
End Sub

ThisWorkbook of vba.xlsm

Private Sub Workbook_Open()
  Dim objSaveEvent As New saveEvent
  Set objSaveEvent.appevent = Application
End Sub

Answer 1

If you want to capture application events but you do this:

Private Sub Workbook_Open()
  Dim objSaveEvent As New saveEvent      '<< your app event-capturing class
  Set objSaveEvent.appevent = Application
End Sub

...then as soon as Workbook_Open is complete, your objSaveEvent object goes out of scope and disappears, so no events will get intercepted.

You need something like this...

In the ThisWorkbook module of your "vba.xlsm":

Dim objSaveEvent As saveEvent 'global, for persistence

Private Sub Workbook_Open()
  Set objSaveEvent = New saveEvent 'populate Global object
  Set objSaveEvent.appevent = Application
End Sub

Class module saveEvent:

Option Explicit

Public WithEvents appevent As Application
  
Private Sub appevent_WorkbookAfterSave(ByVal Wb As Workbook, _
                                       ByVal Success As Boolean)
   MsgBox Wb.Name & " Saved"
End Sub

Private Sub appevent_WorkbookBeforeSave(ByVal Wb As Workbook, _
                                        ByVal SaveAsUI As Boolean, _
                                        Cancel As Boolean)
   MsgBox Wb.Name & " Before Save"
End Sub