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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nap - 23 Mar 2006 17:04 GMT

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

Reply to this Message

Dave Peterson - 23 Mar 2006 19:46 GMT

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.

> Hi everyone,
>
[quoted text clipped - 30 lines]
> Nap's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32722
> View this thread: http://www.excelforum.com/showthread.php?threadid=525723

Signature

Dave Peterson

Reply to this Message

Nap - 23 Mar 2006 21:49 GMT

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

Signature

Nap

Reply to this Message

Dave Peterson - 23 Mar 2006 22:42 GMT

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

> Hi Dave,
>
[quoted text clipped - 27 lines]
> Nap's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32722
> View this thread: http://www.excelforum.com/showthread.php?threadid=525723

Signature

Dave Peterson

Reply to this Message