If you call your macro from the Workbook_Open() event it will fire
when the workbook opens:
Private Sub Workbook_Open()
MyMacro
End Sub
This needs to be put in the ThisWorkbook code module, with MyMacro
in a regular code module. To fire before close (also in the
ThisWorkbook code module):
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MyOtherMacro
End Sub
