Window_Open in personal.xls run each time I open subsequent workbooks.

[Nap]

Hi everyone

It seems my previous attempt at asking this question has cause
confusion

I am using personal.xls to store macros, like most people
One of the macros I have, checks any loaded workbook for certai
information (file name) and if there is a match, makes some automati
entries in that loaded workbook

When I open a file, it is usually by double clicking on a spreadshee
in Windows Explorer. This means that when Excel opens, it first load
*personal.xls*, raises the event Window_Open, and then continues t
open the *second* workbook (the one I double clicked on)

Since the second does not contain any macros, there is no Window_Ope
handler to execute

My problem is
1) I do not want any macros in the *second* workboo
2) I want the Window_Open handler in *personal.xls* to be run each tim
I load a workbook using the double click method

Cheers
Nap

PS.. Sorry about the double post, don't know how that happened. I
seems to be an empty thread

 

[Dave Peterson]

You could use an application event that just looks for any workbook that's being
opened:

Chip Pearson has some notes at:
http://www.cpearson.com/excel/AppEvent.htm

A short sample (which goes under thisWorkbook in personal.xls):

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_NewWorkbook(ByVal Wb As Workbook)
MsgBox "Hey you created a workbook named: " & Wb.Name
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Hey you opened a workbook named: " & Wb.Name
End Sub


===
There's a Workbook_WindowActivate event, but I don't see a window_open event.

 

[Nap]

Hi Dave,

Thanks for the link. I posted the same question on another forum and
had someone give me the same link, so I have already looked at it.

I tried the example available from it and it worked fine in the xls
file supplied. But when I copied the code to the PERSONAL.XLS sheet,
it gave me an error stating that the EVENTCLASS is a user-defined type
that hasn't been defined. So I changed it to Class1 and it then
worked. Here is the change I made:

I changed the line:

Code:

 

[Dave Peterson]

You could drop the class and just put that code into ThisWorkbook.