Create auto open VBA for ALL excel files opened?

[Jarvoisier]

Thanks in advance for any help on this thread. My question is as follows: Is it possible to create a VBA auto-open macro that will run on ALL excel files, not just one specific file? In my line of work it is necessary to format excel spreadsheets in the same manner. With the hope of streamlining the process of processing files, I would like to use the macro code listed below to automatically run every time any excel file is opened rather than having to manually open the file and then push keystrokes to run the macro.

I have researched on this and other forums and have only been able to automatically run a macro when a specific file opens....yet I have not figured out (or learned for that matter) if it is even feasible to create a macro for what I am requesting.

Many thanks in advance and please see the code below for the macro that I'd like to run automatically. Like I mentioned earlier, I can only get this macro to run on the personal.xlsm file itself, and not on successive excel files opened that I would like to have the macro run on automatically.

Private Sub Workbook_Open()
'
' Jarvis_Header_Footer Macro
' Macro recorded 8/23/2011 by Rob Jarvis
'
'
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next

For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
With ws.PageSetup
.LeftHeader = ""
.CenterHeader = "&F"
.RightHeader = ""
.CenterFooter = "&A"
.RightFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = True
.PrintTitleColumns = ""
End With
Next ws
End Sub

 

[Kenneth Hobson]

You have so many threads going on, I can't keep track. Several people crosspost when they first start using these amazing forums. Here is an article on crossposting. http://www.excelguru.ca/node/7

To solve the problem with the code that I posted, use an On Error routine. Your routine took 20-40 seconds which is quite long.

e.g.

Private Sub aOpen_WorkbookOpen(ByVal Wb As Workbook)
    On Error Resume Next
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then MyMacro
End Sub

Here are some of the cross posts:
'Original thread?, http://www.mrexcel.com/forum/showthread.php?t=575178
'http://www.mrexcel.com/forum/showthread.php?t=576295
'http://www.ozgrid.com/forum/showthread.php?t=157749
'http://www.excelforum.com/excel-programming/790772-need-help-cleaning-up-vba-add-in-code.html

 

[Jarvoisier]

You have so many threads going on, I can't keep track. Several people crosspost when they first start using these amazing forums. Here is an article on crossposting. http://www.excelguru.ca/node/7

To solve the problem with the code that I posted, use an On Error routine. Your routine took 20-40 seconds which is quite long.

e.g.

Private Sub aOpen_WorkbookOpen(ByVal Wb As Workbook)
    On Error Resume Next
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then MyMacro
End Sub

Here are some of the cross posts:
'Original thread?, http://www.mrexcel.com/forum/showthread.php?t=575178
'http://www.mrexcel.com/forum/showthread.php?t=576295
'http://www.ozgrid.com/forum/showthread.php?t=157749
'http://www.excelforum.com/excel-programming/790772-need-help-cleaning-up-vba-add-in-code.html

Gotcha, and yes, sorry for the multiple posts...I didn't want to keep "bumping" the old one, so i wasn't sure if I should start a new thread on this forum or troll the other excel forums out there. Not to mention I didn't take into consideration this past weekend was a holiday, hence nobody responding. lol

I will insert the code you wrote and report back to see if I can get the add-in to run properly. Thank you again for your time and attention, it is much apprecaited!