Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Auckland, North Island, New Zealand
    Thanked 0 Times in 0 Posts

    Bypass an event macro (Excel)

    I have a macro that runs on the 'Workbook_BeforeClose' event but I want another macro (assigned to a button)to close the book without running the event macro.

    Any ideas?

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    New York, NY
    Thanked 29 Times in 27 Posts

    Re: Bypass an event macro (Excel)

    Hi Simon,

    You sure want a lot![img]/w3timages/icons/laugh.gif[/img]
    Seriously that does sound near impossible. Perhaps some of the clever minds here can suggest a less roundabout way, but here is one (roundabout) strategy (sorry can't provide a code sample at the moment):

    The first thing that your "close without event" code needs to do is disable the Workbook_BeforeClose event code in some way. There may be some clean and clever way to do this but at most drastic, you could have it remove the code in the event procedure.

    This though would require you have code that runs when the workbook opens, and tests for the presence of the Workbook_BeforeClose code, and if it's missing, restores it or otherwise enables it.

    Hopefully someone will suggest another, less cockeyed way!


  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Queanbeyan, New South Wales, Australia
    Thanked 0 Times in 0 Posts

    Re: Bypass an event macro (Excel)

    Try this code in "ThisWorkbook":
    Option Explicit
    Private blnAlreadyClosing As Boolean
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If blnAlreadyClosing Then
    Cancel = True
    Exit Sub
    End If
    MsgBox "closing"
    End Sub

    Public Sub cmdCloseIt()

    blnAlreadyClosing = True

    End Sub

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    blnAlreadyClosing = False
    End Sub

    Assign cmdCloseIt to your button
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4

    Re: Bypass an event macro (Excel)

    Hi Forrests,

    If I did not misunderstand your question this should be fairly easy. Just put this line at the beginning of the macro attached to the command button:

    Application.EnableEvents = False

    This line will disable all event macros, so the macro will run on its own.

    Only problem is that after the workbook is closed, the events will still be disabled, so you need to run the line Application.EnableEvents = True somewhere.

Posting Permissions

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