PDA

View Full Version : [SOLVED] Where to Store Auto_Open Macros?



Anne Troy
02-24-2005, 06:48 PM
I was always taught to use Workbook_Open, etc., and not to use Auto_ macros.

But right now, I'm dealing with an Auto_Open.
Where do you store it? Does it have to be in ThisWorkbook? Or can it reside in a standard module?

Please ignore my title here at VBAX when answering this question, or realize that my title here is highly undeserved. LOL!!

Paleo
02-24-2005, 06:50 PM
Hi Anne,

the Workbook_Open resides in This_Workbook.

Anne Troy
02-24-2005, 06:50 PM
I know. I need to know where Auto_Open goes. :(

johnske
02-24-2005, 06:54 PM
Hi Anne,

It can be used in a standard module.

Regards,
John

Anne Troy
02-24-2005, 06:54 PM
Thanks!

Paleo
02-24-2005, 06:59 PM
Hi,

gee I only see it at my Imediate Window. How do you use that John?

Anne Troy
02-24-2005, 07:03 PM
You just name the macro:


Sub Auto_Open ()

But I thought that I read that MS discourages the use of them anymore because of the wave of macro viruses that had been spread several years ago.

johnske
02-24-2005, 07:08 PM
Hi Paleo,

On opening the workbook it runs automatically... The same as Workbook_Open, but it may sometimes be easier to import & export basic modules between books with an Auto_Open macro in it rather than using a "ThisWorkbook" module with a Workbook_Open event...

John

johnske
02-24-2005, 07:12 PM
Just saw your reply Anne,

Yes, possibly because of the ease of importing/exporting it would be easier to spread a macro virus...

Regards,
John

Anne Troy
02-24-2005, 07:13 PM
John, may I print your real name in an Office VBA book for making a small, but not insignificant, contribution? If so, post or PM your real name, please. :)

johnske
02-24-2005, 07:15 PM
OK, will do...

Paleo
02-24-2005, 07:35 PM
Great, thanks for teaching me that, but I think I better still using on This_Workbook then because of the viruses thing.

Ken Puls
02-24-2005, 08:31 PM
Hey guys,

I thought that Auto_Open was a hangover from Excel 5.0 and it's macro language (XLM?) which was the precursor to VBA. Could be wrong though. :dunno

johnske
02-25-2005, 02:34 AM
Hey guys,

I thought that Auto_Open was a hangover from Excel 5.0 and it's macro language (XLM?) which was the precursor to VBA. Could be wrong though. :dunno

Hi Ken,

Dunno about that, but I know this worx in '97 and I just tried it in 2k and it worx there also:


Sub auto_open()
MsgBox "Hi there! :o)"
End Sub

regards,
John

Richie(UK)
02-25-2005, 04:01 AM
Hi all,

Here is Chip Pearson's take on the subject:


If you have programmed in previous versions of Excel, you are probably familiar with the Auto_Open and Auto_Close macros, which execute when the workbook is opened or closed, and with the OnEntry and OnSheetActivate properties, which execute when you enter a cell or worksheet. While these macros are still supported in Excel97, for compatibility with workbooks created in previous version, they have been replaced by event procedures.

In general, you should not use the Auto_Open and Auto_Close macros in new workbooks. While they will still work as expected, you should become familiar with and learn to use the new event procedures. Of course, if you are developing a workbook that will be used by users with an earlier version of Excel, you cannot use event procedures and must use the Auto_Open and Auto_Close macros. Remember that when you open a workbook through VBA (with the Workbooks.Open method), the Auto_Open macro is not executed. You will have to use the RunAutoMacros method of the Workbook object to run it.

Full text here (http://www.cpearson.com/excel/events.htm)