Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cancel _Close macro (2000 sp3)

    I have created a Word document with a UserForm that makes it easy for the user to enter correct values in a table. Since users can't be trusted, I want to validate the values in the table to be sure they didn't mess anything up. I have this code in the Document_Close macro and have been looking for a way to cancel the close event and put out an error message if I find bad data. I found this code:
    If FoundBadData Then
    MsgBox "Fix this, Please"
    ActiveDocument.Saved = False
    SendKeys "{ESC}"
    Exit Sub
    End If
    This works great... once. After that the Document_Close event never runs again until I reload the document.
    Is there some switch I need to turn back on so it will run again?
    I saw a previous post that was similar and the suggestion was to capture the DocumentBeforeClose event with a class. I was relieved to find in another place that this doesn't capture the event until after the "Do you want to save" dialog box pops up. I'd really like to have control over the timing of that.
    Any thoughts you have on this would be most welcome.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Cancel _Close macro (2000 sp3)

    Don't use ESC, it terminates macro execution. Instead, use the DocumentBeforeClose event and set its Cancel argument to True. In my Word 2002, this is effective before the "Do you want to save..." dialog kicks in.

    In the class module, say clsAppEvents:

    Public WithEvents app As Application

    Private Sub app_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
    If foundBadData Then
    MsgBox "Fix this, Please"
    Cancel = True
    End If
    End Sub

    In a standard module:

    Public myApp As clsAppEvents

    Public Sub InitializeApp()
    Set myApp = New clsAppEvents
    Set myApp.app = Application
    End Sub

    In the ThisDocument module:

    Private Sub Document_Open()
    InitializeApp
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cancel _Close macro (2000 sp3)

    Great! Thank you very Much!

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel _Close macro (2000 sp3)

    If you don't trust your users, you might want to prevent them closing a form with the little X button in the caption:

    Private Sub Userform_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then Cancel = 1
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cancel _Close macro (2000 sp3)

    Another great idea! I noticed that I could close a UserForm that way just before you posted this. Great timing! Thank you very much for this code, I'll give it a try.
    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Cancel _Close macro (2000 sp3)

    It might be slightly safer to use

    Private Sub Userform_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = (CloseMode = vbFormControlMenu)
    End Sub

    This allows the Task Manager (and Windows shutdown) to close the userform.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel _Close macro (2000 sp3)

    I found the code in my post above a few years ago on the net (possibly mvps) and we didn't have any trouble with it.
    But I see what you mean and use your version. Thanks for the enhancement, Hans.

Posting Permissions

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