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.

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.

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?

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

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!
--
Cheers,
Ryan


"mcambrose" wrote:

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?

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

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.

--
Cheers,
Ryan


"RyanH" wrote:

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!
--
Cheers,
Ryan


"mcambrose" wrote:

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?

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

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.

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.

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.

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.