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 caused confusion.

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

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

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

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

Cheers,
Nap.

PS.. Sorry about the double post, don't know how that happened. It 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 wrote:
>
> Hi everyone,
>
> It seems my previous attempt at asking this question has caused
> confusion.
>
> I am using personal.xls to store macros, like most people.
> One of the macros I have, checks any loaded workbook for certain
> information (file name) and if there is a match, makes some automatic
> entries in that loaded workbook.
>
> When I open a file, it is usually by double clicking on a spreadsheet
> in Windows Explorer. This means that when Excel opens, it first loads
> *personal.xls*, raises the event Window_Open, and then continues to
> open the *second* workbook (the one I double clicked on).
>
> Since the second does not contain any macros, there is no Window_Open
> handler to execute.
>
> My problem is:
> 1) I do not want any macros in the *second* workbook
> 2) I want the Window_Open handler in *personal.xls* to be run each time
> I load a workbook using the double click method.
>
> Cheers,
> Nap.
>
> PS.. Sorry about the double post, don't know how that happened. It
> seems to be an empty thread.
>
> --
> Nap
> ------------------------------------------------------------------------
> Nap's Profile: http://www.excelforum.com/member.php...o&userid=32722
> View this thread: http://www.excelforum.com/showthread...hreadid=525723

--

Dave Peterson

[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:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thanks for your code snippet. A little different to the one in the example, but works the same.

Thanks,
Nap

[Dave Peterson]

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

Nap wrote:
>
> 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:
> --------------------
> Dim Appclass As New EventClass
> --------------------
> to
> Code:
> --------------------
> Dim Appclass As New Class1
> --------------------
>
> Thanks,
> Nap
>
> --
> Nap
> ------------------------------------------------------------------------
> Nap's Profile: http://www.excelforum.com/member.php...o&userid=32722
> View this thread: http://www.excelforum.com/showthread...hreadid=525723

--

Dave Peterson