[RESOLVED] Excel Autostart macro..?

[Static]

Ok, I know you can add an xls file to the XLStart folder and it will always load that first, and you can add code to run... but here is my problem

I Need to run code on every workbook that is opened.. The Autostart xls file is no good because it runs the code BEFORE the next workbook opens.

How can I run code on the workbook that is being opened? (Without having to code every workbook because that is not possible for this situation)

(basically I need to set the print options in excel for header/footer so that ever workbook prints the same)

Thanks!

[john24]

use one of your workbooks as an example and record a new macro doing what you want - most of this vba code should copy over to vb - not the most elegant of solns but it works assuming that you want to do the same 'thing' to all of your workbooks

[RobDog888]

Moved from Classic VB forum. :)

Are they being opened manually?

[Static]

yes... say the user get a file off e-mail.. or the lan... etc..

double clicks to open.. bam I want to re-format the printing.


how about an add-in? (Never made one but maybe?)

[RobDog888]

Yes, an addin would be perfect as long as the users are not running an older version of Excel that may not support your formatting functions you use.

[Static]

with an Add-in.. would the user have to run it each time? or can it be triggered every time a new workbook is opened? (How do I do that!!)

[RobDog888]

To be honest I havent created many Excel addins (.xla) but VB Add-Ins have an OnConnection and OnStartupComplete. You could also create the Workbook_New or Workbook_Open events.

[Static]

Excel add-ins seem to have NOTHING! ..hmmm

will play around a bit.. probably be back soon to ask another ?

Thanks

[Static]

nope..

I cant seem to get it to "hit" the other workbooks.

if I use it as an addin.. it will run the code on the addin first, then open the workbook you are opening.

any other ideas?

[RobDog888]

Other then creating a global hook for the creation of a new window and check if its an Excel window. I'm not sure what else you could do.

[Static]

well.. its not pretty but might work:

xlt file that will format all NEW workbooks...
and a Personal.xls file that has an API timer...
every minute it looks at all workbooks open..
if the open workbook is not in its list of "previously formatted" books
then it formats it.. otherwise it skips it.

(I see holes in this one lol! but its a start)

[JustinLabenne]

Can I just ask why every workbook? Cannot see why the print options are not set before distribution but hey:

Try this code in your personal.xls:
Goes into a class module:

Public WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
' your code here
MsgBox "A new workbook is created!"
End Sub

Then this goes in the ThisWorkbook code module of the personal.xls:
Dim ApplicationClass As New AppEventClass

Private Sub Workbook_Open()
Set ApplicationClass.App = Application
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set ApplicationClass.App = Nothing
End Sub

[Static]

Justin! You are Awesome!

I was so close.. but yet couldnt quite get the events...

this line is my saviour: "Public WithEvents App As Application"

Excelent... thank you!! thank you!!

[Static]

oh, Justin, every workbook is for audit.. so it always prints Filename / Author / Date&Time

Thanks Again

[JustinLabenne]

Glad to help, you can also look into putting the code into an Add-in and just keeping the addin loaded when the AppEvents are needed. I use this method as I never have used a Personal.xls.

Hope it goes well......