Macro to delete another macro and the whole module

[Andrew]

Can I have an macro which will stop the Auto_Close macro from running.
And Is there any macro to delete the module of an opened workbook??

 

[dk]

Can I have an macro which will stop the Auto_Close macro from running.
And Is there any macro to delete the module of an opened workbook??

You can use Application.EnableEvents to turn off events (and therefore override the Auto_close procedure) e.g. Running disable events will close the workbook without Auto_Close running.

Sub DisableEvents()
Application.EnableEvents = False
ThisWorkbook.Close
End Sub

Sub Auto_close()
MsgBox "Hello"
End Sub

As for the second part take a look here:-

http://www.cpearson.com/excel/vbe.htm

It's about a third the way down. Before using the code you must go to Tools, References and select Microsoft Visual Basic for Applications Extensibility Library.

HTH,
D

 

[NateO]

Couple of additional thoughts.

First, auto macros (open & close) aren't really event procedures, I don't think enabling or disabling the events prevents these macros from running. However, Auto macros do not run if a file is being opened or closed via vba (another macro). Also, if you're opeing or closing the file manually, hold your shift key, this will disable the macros.

Second, Chip's code works great. The only thing I've noticed is that you don't necessarily need to declare the:
VBCodeMod As CodeModule

I got hung up with this when working with class modules....


Cheers, Nate

 

[Guest]

First, auto macros (open & close) aren't really event procedures, I don't think enabling or disabling the events prevents these macros from running. However, Auto macros do not run if a file is being opened or closed via vba (another macro). Also, if you're opeing or closing the file manually, hold your shift key, this will disable the macros.

In the sense that Excel will look for procedures Autpen (after Workbook_Open) and Auto_Close (after Workbook_BeforeClose) when opening or closing a file then these ARE events occurring within the Excel application. If you run the code then you'll see that this is the case. This is a throwback to earlier versions of Excel which didn't support Workbook_Open or Workbook_close events and should generally not be used.

Regards,
D

 

[NateO]

Not to be argumentative, yep, auto macros are literally events, but not technically "event procedures." And yes, it has everything to do with Excel's evolution..

But here's where I struggle. Your code works as intended (kudos), and when I don't re-enable events manually, I open a workbook with "event procedures" (i.e., workbook_open) and none of these fire. While still having events disabled, I open a workbook with an auto_open macro, and this macro does indeed launch, as does the auto_close. It's all a little mysterious to me.....

Thank God it's Friday!

Nate
This message was edited by NateO on 2002-03-08 12:24

 

[Ivan F Moala]

One other thing to consider......
The code won't work on a locked project.

Ivan