Macro to delete another macro and the whole module
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Macro to delete another macro and the whole module

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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??

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Code:
    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

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Guest

    Default

    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 Auto_Open (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




  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Ivan


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com