Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question Unanswered: Workbook_Close() problem

    I am using Private Sub Workbook_Close() to execute some code before the workbook is closed. Even though the user says Cancel when the Save box appers the above code runs. How can this be prevented. I tried using Workbook_DeActivate() but it doesnt work.

    ~BS

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Can you post your entire code? (Sanitize if necessary)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2004
    Posts
    78

    Question

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean) 
        Dim WBkName As String
        WBkName = Right(ActiveWorkbook.Name, 3) 
        If WBkName <> "xnv" Then     'or WBkName = "xls"
            Call EnableCopyCutAndPaste
        End If
    End Sub
    This code checks if the file extension in xls or xnv (these extensions generated by excel addin softwares) and calls EnableCopyCutAndPaste only if file extension is not xnv.

    Copy/Cut is disabled in xls file on worksheet_open. It is only enabled after xls file is closed, hence the above code.

    The problem is that if I select file-->close and then cancel button on the xls file, xls file remains open and the code for Workbook_BeforeClose is also executed thus enabling copy/paste.

    ~BS.

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    I have used a variation from an example at 'The Spread Sheet Page', j-walk.com.

    See the example about the middle of the page at this link.
    Handling the Workbook BeforeClose Event



    /
    ~

    Bill

  5. #5
    Join Date
    Dec 2004
    Posts
    78

    Thumbs up

    Thanks Bill. This is what I needed. Just added Me.Close in VbYes and VbNo so that worksheet is closed. Also if Me.Close is not entered, excel prompts its own Save box again.

    Thanks again.

    ~BS

Posting Permissions

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