Macro to delete a macro

[Alan B]

I have an auto_open macro on a workbook that performs some functions, then
does a save as with today's date as the file name. The problem is that the
new file has the auto_open macro in it too, and when I open it, the macro
runs. I would like to put some code in to prevent the macros being saved
with the new sheet, or in some way delete the auto-open before it saves. Any
ideas would be welcome.

Regards

Alan

 

[papou]

Hello Alan

Place the code into your auto_open macro below your last instructions and
amend accordingly (Module name):

Dim MyModule
Set MyModule = ThisWorkbook.VBProject.VBComponents("Module2")
ThisWorkbook.VBProject.VBComponents.Remove MyModule

HTH
Cordially
Pascal

 

[papou]

Alan
You may also need to amend security settings:
From the Excel spreadsheet menu:
Tools, Macro, Security
Approved Editors tab (please note caption may vary, I have a french version
of Excel)
Tick "Trust Visual Basic Project"
Click OK and save your workbook

HTH
Cordially
Pascal

 

[Alan B]

Pascal,

Thank you for your response. I have created a second Macro called sub
delandsave() which contains the following code:

Sub delandsave()
'
'Deletes auto_open
'
Dim MyModule
Set MyModule = ThisWorkbook.VBProject.VBComponents("Autpen")
ThisWorkbook.VBProject.VBComponents.Remove MyModule
'
'Creates new workbook using date in DATA worksheet cell AG1 as name
'
Dim SaveName As String
SaveName = Sheets("query").Range("ag1").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="\\colorado\Impromptu\finance\Stock Sheets\"
& SaveName & ".xls"

End Sub

This is called from the auto_open, and should delete the auto_open macro.
When I run it, I keep getting an error on the "Set MyModule =.." line.

Any ideas?

Regards

ALan Berry

 

[Tom Ogilvy]

Do you have a module named Autpen? If you do, you shouldn't.

I believe papou assumed you had a separate module (don't have procedures and
modules with the same name) that contained only the Autpen procedure.

 

[papou]

Hello Alan
You should place the code in your auto_open macro and NOT in a seperate
macro.
As mentionned Tom in his post, your module should NOT have the same name as
your macro.
My sample works for a module named "Module2", this name should be amended to
reflect your module name (and NOT macro name).
The module name appears in the Modules folder from the VBAproject tree of
your workbook, available in the VBA editor.

HTH
Cordially
Pascal