run code direct from startup

p10rpc · September 25th, 2003, 06:08 AM

I am looking for a way to start code as soon as a file is opened, without the user having anything to click on etc. Any ideas???

Cheers

RogerWaters · September 25th, 2003, 06:22 AM

Try This

---------------------------------------------

If you place the code in a module sub called

Sub auto_open()

This will run on opening the spreadsheet.

Roger Waters

chrislepingwell · September 25th, 2003, 06:35 AM

Actually you should be using the object event model e.g.

Code:

Private Sub Workbook_Open()
'your code
End Sub

and similarly for close

Code:

Private Sub Workbook_Close()
'your code
End Sub

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know

RogerWaters · September 25th, 2003, 06:42 AM

Hi Chris,

Is the use of events better than the auto_open() ?

Roger Waters

chrislepingwell · September 25th, 2003, 08:10 AM

Yes, because Auto_xxxx macros don't actually execute if the file (Spreadsheet, word doc etc) is opened/closed via code. If there is an auto_xxxx macro in the file it must be explicity called using something similar to:-

Code:

set wbk = workbooks.open "blah.xls"
wbk.runautomacros xlAutoOpen

On the other hand, events are are always fired, unless explicitly turned off:-

Code:

Application.EnableEvents False

(equivalent to holding down the shift key to prevent auto_open macros from running when open workbooks).

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know

Romulus · October 18th, 2003, 06:37 AM

Is the object event model supported pre xl97? I still have some users on 95 - It's a long story, but I'd like to make my code as standard as possible - I always have to write 2 versions!