Can I stop my Auto_Open macro from running?

**SimonHobbs** · 09-26-2008, 06:35 AM

Having written an Auto-Open macro on an Excel Template and got it all nicely sorted, I then added code to remove the whole module after running it, so that when the new workbook is re-opened after saving as .xls it would not run the Auto_Open macro again. (The macro only being required to set up the new form from the template.)

However I now cannot get back into the template macro to modify it, since each time I open it the Auto_Open macro runs right through and deletes itself!

It is still there but I cannot open it without deleting it! I feel a bit stupid but don't want to re-write the whole thing.

I seem to remember that there is a way to stop the Auto_open macro from running but I cannot remeber what it is.

Help please.

**Protiusmime** · 09-26-2008, 06:46 AM

Possibly this will work...
Hold down the SHIFT key when you open the workbook.

**royUK** · 09-26-2008, 06:47 AM

You need to create a new workbook from the template without enabling macros, then make the amendments in that workbook. Finally save the new workbook as the template to overwrite the original.

**Protiusmime** · 09-26-2008, 06:54 AM

Good morning, RoyUK:
Quick question... will that work in all versions of Excel? I am using Excel2003. I can hold a shift key down and prevent an On-Open event from running. Just curious. Thanks.

**royUK** · 09-26-2008, 08:22 AM

The OP is dealing with a template workbook (*.xlt), that is why I have suggested opening a new workbook from the template. By not enabling macros the auto open will not run & the workbook can be edited without it deleting the macro.

**SimonHobbs** · 09-26-2008, 09:54 AM

Thanks to you both,
Obvious as soon as you suggested it Roy but none the less welcome.

Proteusmime : The Shift key does not stop the macro running, on Excel 2002, perhaps because the Command to open the template is "New" rather than "Open"