Run A Macro Every Time Excel opens any file

[mcambrose]

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.

 

[RyanH]

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

 

[mcambrose]

I tried this previously and as far as I can tell, it only works for the
workbook that this code resides in. I have the code in my personal.xls
workbook in the appclass module. When I open the personal.xls file the macro
runs, but when I open any other file, it does not run. Do I just have it in
the wrong location for it to be global?

 

[mcambrose]

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

 

[Jim Thomlinson]

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

 

[RyanH]

Put this in your Personal Workbook under the ThisWorkbook Module

' make Application events available
Private WithEvents App As Application

Private Sub Workbook_Open()
' set the App vairable
Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

MsgBox "Workbook Opened: " & Wb.Name

' your code here

End Sub

Hope this helps!

 

[RyanH]

One more thing! I forgot to tell you that the App variable is initialized
when you first open the Personal Workbook. So for this code to work just
close the Excel Application and reopen it and everything should work fine.

 

[mcambrose]

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]

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

 

[mcambrose]

I don't seem to be getting any errors unless they are being trapped, but your
mentioned the "end" statement and that intriqued me. Can you give me an idea
of where or how the might happen.
can this esecution of the "end" command happen inside any of my macros? If
this is too much to ask, just let me know. Thanks.

 

[mcambrose]

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.

 

[mcambrose]

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