Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Thanked 0 Times in 0 Posts

    Prevent workbook from closing (2000)

    Hi all Does anyone know how cancel the user selected close workbook command including the savefile = true option and cancel it?
    I have a spreadsheet that must perform a controlled save routine when it saved either with save, save as or saved when closing. Code in the before_Close sub and before save subs takes care of this. The save portion works fine for each scenario, but excel crashes when closing after the activeworkbooks.close savefile = False command. I belive what I need is to cancel the original user command then take care of the saving and closing in my subs. Please help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Prevent workbook from closing (2000)

    I'd declare a public variable in a standard module:

    Public blnCanClose As Boolean

    Put the following code in the Before Close event:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = Not blnCanClose
    End Sub

    Set blnCanClose to True in your controlled Save procedures when all conditions are met.

    BTW the first argument of Close is called SaveChanges, not SaveFile, and it is ActiveWorkbook, not ActiveWorkbooks.

Posting Permissions

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