autostart macro (xls 97)

Topic

New Reply

Any way to autostart a macro on the open or close of that file??? If yes what does it look like, thanks.

Reply | Quote

Replies

scrappe7

Happy New Year. Yes Two ways:

Old style: Name the subroutine Auto_Open() or Auto_Close() , these are special names that will run the code at the time when the workbook is opened.

New style: Place the code to run in the Private Sub Workbook_Open() and Private Sub Workbook_BeforeClose(Cancel As Boolean)

Remark: Try and keep the Auto_Open() routine to a minimum of 5 lines. Do something like:

Sub Auto_Open()
Call ContinueWithTheCode
End Sub

where ContinueWithTheCode will do the work. This is due to a bug in Excel 5.x and I am not sure if it was ever addressed.

HTH

Wassim

Reply | Quote

Hey wassim,

I used auto_open but the coding that i ran has a close of that file automatically. How can i stop the auto_open from executing automatically on the open of the file. So that i can edit that file? I cant get to my coding in that file, i just need to pause it for edit

Reply | Quote

scrappe7

OK how are you opening the workbook that has the Auto_Open that you want to disable?

If you are using Excel 97, you can use the Macro Virus tool, and say Do not Run Macros and that would be it. But make sure you have the VBE open first.

You can hold the Shift Key down while opening the file.

If you are opeing the workbook via code well as the OLH says “If the workbook being opened has any Auto_Open macros in it, they won’t be run when you open the file from Visual Basic.”

HTH

Wassim

Reply | Quote

Thank you wassim problem solved.

Reply | Quote

Hi Wassim

The Auto_Open bug you mention seems to have been fixed in Excel 97. I’ve been running about a 12+ line Auto_Open for several years without any signficant performance hits. Each line is a separate Call. The machine it was written on was a P166 w/ 80Mb RAM. (No issue in Excel 2000 either.)

HTH

Reply | Quote