I'm sorry to ask again, but my workbook_open event has quit working again. It
probably has to do with some code being stopped as you mentioned in your
earlier response. I found the "end" command yesterday (and removed it) that
I thought was the only problem. Since it has hapened some more today, I must
also have a problem with code stopping. How do i reinitialize the appevents
(particularly my workbook_open event) as you mentioned. As you can see I know
just partially understand the class module stuff. I tried using the immediate
window and just typing in the set command you used in your test procedure
(Set Appevents = Excel.Application '**Run me). I thought that might
reinitialize. I'm not sure I even understand when this is initialized the
first time. I guess it is initialized when my personal.xlsm file is opened. I
don't want to have to keep opening and closing this file. Thanks for any help.

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

Private Sub Appevents_WorkbookOpen(ByVal Wb As Excel.Workbook)
Dim mystring As String
MsgBox "Workbook open event is working"
'Delete_unused_toolbars
End Sub

"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.