Thank you for the explanation. I looked through my extensive code of numerous
modules and found a single instance of an END statement. The statement
should have been and "End If" statement, but I didn't get an error messsage,
so I never noticed it. I have tried getting this global workbook_open event
to work several times before with much disappointment. IT would work for a
short time and then quit. This was because of the "End" statement in a
workbook_close event. It feels great to have it work, but even greater to
understand that the fix should be permanent. Thanks for taking the time.

"Jim Thomlinson" wrote:

You are storing the Appevents object as a global. If your code errors out and
requires you to end or if you Choose to stop the code or if your code
executes the stand alone line "End" then your global variables and objects
get cleared and you need to re-initialize them...
--
HTH...

Jim Thomlinson


"mcambrose" wrote:

THank you for the response. I tried the code you provided and it didn"t work
at first. I saved the workbook with the revised code you provided and then it
started working. Next I went back to my old code and it worked. I don't have
any idea what happened, but somehow your code helped solve it. I went back
and opened an old copy of my spreadsheet with my unchanged code and it worked
properly. I hate it when I don't understand the reason something starts
working because I really don't learn anything. However, thank you and all
the others for your efforts which helped solve the problem.

"Jim Thomlinson" wrote:

Did you instantiate an instance of Appevents? Try this code. Run Test. Note
that for your purpose you can instantiate Appevents in the Workbook open
event of Personal.xls...

Public WithEvents Appevents As Excel.Application

Sub test()
Set Appevents = Excel.Application '**Run me
End Sub

Private Sub Appevents_WorkbookOpen(ByVal Wb As Excel.Workbook)
MsgBox "Workbook open event is working"
End Sub

--
HTH...

Jim Thomlinson


"mcambrose" wrote:

I used the following code directly from John Walkenbach's VBA book (except I
inserted a msg box command rathe rathe than running a macro just to show me
it was working before getting more complicated) and the event only fires when
the file in which it resides is opened (pesonal.xls). I thought it should
fire every time any workbook is opened. Any ideas? Thanks

Public WithEvents Appevents As Application
Private Sub Appevents_WorkbookOpen(ByVal Wb As Excel.Workbook)
MsgBox "Workbook open event is working"
End Sub


"RyanH" wrote:

Sounds like you could use an Application Open Workbook Event. This is
straight out of the help section.

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

' your code here

End Sub
--
Cheers,
Ryan


"mcambrose" wrote:

I have a problem with a couple of toolbars that are attached to many of my
files. I have a macro that strips these off if the file is open, but I want
to run this macro automatically every time a new file is opened. I understand
the workbook_open can be used, but if I understand correctly, I have to put
the code in each of the workbooks. I want my code to reside in my
personal.xls workbook that is always open. I need for my macro to execute
automatically every time Excel opens any file. Thanks for any suggestions.