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

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

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

"tanglk@gmail.com" wrote:
>
> 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