MrExcel Publishing
Your One Stop for Excel Tips & Solutions

automatically removing auto_open macro from template

Posted by Robert Yerby on April 23, 2001 4:27 AM

If I create an auto_open macro in an excel template, I would like the last command to remove (or at least rename)the auto_open macro so that it exists in the template but none of the files that are created from the template. Is this achievable?

Posted by Dave Hawley on April 23, 2001 4:35 AM


Hi Robert

It should be, try this

Sub Auto_open()
If Right(ThisWorkbook.FullName, 3) <> "xlt" Then
'Your Code
End If
End Sub

As a point of interest Robert, the prefered method now is to use the Workbook_Open placed within the Module of "ThisWorkbook". Best way to get there is to right click on the sheet picture, top left next to "File" and select "View Code"


OzGrid Business Applications

Posted by Tom Urtis on April 23, 2001 9:55 AM

Just curious Dave...

Hi Dave, I saw your response and am curious as to why using the Workbook_Open in the "This Workbook" module is preferred over the Auto_Open procedure? Are there advantages to one over the other? Thanks. Tom

Posted by Dave Hawley on April 23, 2001 7:46 PM

Re: Just curious Dave...

Hi Tom

There are no real advantages (yet). The Workbook_Open was introduced in Excel 97 along with other Events and is intented to replace Auto_Open. Excel has left the Auto_Open operational for backward compatability. So I guess this means that at a later release Auto_Open will no longer work.

OzGrid Business Applications