turning off the _beforeclose event
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: turning off the _beforeclose event

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

    Default

     
    I am trying to get this piece of code to work:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    myans = MsgBox("Do you want to save the changes you made?", 67)
    If myans = vbYes Then
    Sheets(1).Range("A1") = ""
    ActiveWorkbook.Save
    ResetExcel
    ActiveWorkbook.Close
    ElseIf myans = vbNo Then
    ResetExcel
    ActiveWorkbook.Close
    End If
    Cancel = True
    End Sub

    ..but when I run it, the ActiveWorkbook.Close command triggers the whole routine again! Can I put a line in just before which will stop this happening?

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You're already in the process of closing your workbook, you don't need those "Close" statements in there. If you really want the workbook to close get rid of that "Cancel = True" statement as well. This cancels the close action.

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Mark. That works fine if I choose "Yes" to the save changes message, but if I choose "No", I get two messages, one from my code, and one from Excel. I know there is a way to disable events, but I can't find it/ remember. can anyone help?

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Me again! Sussed it, I have put the ResetExcel macro call in _windowDeactivate instead. Thanks for the help!

  5. #5
    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

      
    Also try;


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not ThisWorkbook.Saved Then
    Select Case MsgBox("Do you want to save the changes you made? ", 67)
    Case vbCancel
    Cancel = True
    Exit Sub
    Case vbYes
    resetexcel
    ThisWorkbook.Save
    Case vbNo
    resetexcel
    ThisWorkbook.Saved = True
    End Select
    End If

    End Sub



    Kind Regards,
    Ivan F Moala From the City of Sails

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