How to run a macro (in personal.xls) whatever file (in the listing) open

[tanglk]

I have a macro in personal.xls. I want the macro in personal.xls to
execute whenever a file is open. Based on the filename, i will further
execute certain commands.

The filename that fit the criteria is listed in personal.xls as well.

regards

 

[Dave Peterson]

You'll need an application event that monitors when you open another workbook.

Put this in your personal.xls's project under the ThisWorkbook module.

You may have to modify your workbook_open event to include the code--you'll want
to merge the code into one procedure:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

Select Case LCase(Wb.Name)
Case Is = "book1.xls", "book2.xls"
Case Else
Exit Sub
End Select

Call YourMacroHere(Wb.Name)

End Sub

And I had this in a general module in my personal.xls workbook.

Option Explicit
Sub YourMacroHere(myName As String)
MsgBox myName
End Sub

You can read a lot more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm