Consulting

Results 1 to 10 of 10

Thread: Userform_Terminate ??

  1. #1

    Userform_Terminate ??

    Hi All,

    I have slowly worked my way through creating my userform - final steps.

    I have a dotm. that when opened it the userform appears - using the following module

    Sub AutoNew()
    StartProcess
    End Sub

    Sub StartProcess()
    Dim oFrm As New UserForm1
    If ActiveDocument = ThisDocument Then
    MsgBox "You are attempting to process the template." & vbCr & "Create a new document from the template."
    GoTo lbl_Exit
    End If
    With oFrm
    .Show
    Unload oFrm
    End With
    lbl_Exit:
    Set oFrm = Nothing
    Exit Sub
    End Sub

    I am wanting the word document to close if a user closes the userform by using the 'X'. I have used the following:

    Private Sub UserForm_Terminate()
    ActiveDocument.Close
    End Sub

    this works perfectly if the userform is closed (without being run), however, when the userform is run (via a command button) it brings up the save option - 'save', 'Don't save', 'cancel'.
    if the user clicks:
    'Save': they can save it then the document automatically closes.
    'Don't save': the document closes
    'Cancel': a error message comes up.

    I only want it to close everything if the user form is closed (without completing it) solely to prevent users from closing the userform and trying to manually complete the form.
    any guidance would be great.

    Thanks

  2. #2
    I think what you are looking for is
    ActiveDocument.Close Savechanges:=False
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    Thanks Graham.

    I have also tried that, but it is closing as soon as the Command Button is actioned. It is as if the Command Button contains a termination event in it?

    Could it be something in the Module that is causing this? I have used the "activeDocument.close" code before and it worked fine until i added that module?

    Thanks

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I think you're looking for this event

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
    End Sub

    https://msdn.microsoft.com/en-us/vba...eryclose-event


    Example from the link

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        'Prevent user from closing with the Close box in the title bar.
        If CloseMode <> 1 Then Cancel = 1
        UserForm1.Caption = "The Close box won't work! Click me!"
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Hi Paul,

    This seems to deactivate the 'X'. I want the user to be able to X the user form, but if they do, i want the document to also close with it?

    i have tried the standard Userform_Terminate, however for some reason, ever since i added the module (above) the Userform_Terminate is also closing the document when the userform is run.
    It is allowing the user to save, once they select the save location and press save, it is saving it and then closing it. Making the user find the save location and re open it to view.

  6. #6
    Get rid of the Userform_Terminate and add

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        If CloseMode = vbFormControlMenu Then
            MsgBox "This is the X Button"
            Hide
            Tag = 0
            Cancel = True
        End If
    lbl_Exit:
        Exit Sub
    End Sub
    If you have a cancel button on the form then add e.g.

    Private Sub CommandButton1_Click()
        MsgBox "This is the cancel Button"
        Hide
        Tag = 0
    lbl_Exit:
        Exit Sub
    End Sub
    You could validate the entries so that the user needs to make selections e.g.

    Private Sub CommandButton2_Click()
        MsgBox "This is the OK Button"
        'validate the entries e.g.
        If TextBox1.Text = "" Then
            MsgBox "Complete TextBox1"
            TextBox1.SetFocus
            GoTo lbl_Exit
        End If
        If TextBox2.Text = "" Then
            MsgBox "Complete TextBox2"
            TextBox1.SetFocus
            GoTo lbl_Exit
        End If
        Hide
        Tag = 1
    lbl_Exit:
        Exit Sub
    End Sub
    Modify the main code to handle the tags and process the document from the values entered in the userform (rather than do it in the form code)

    Sub StartProcess()
    Dim oFrm As New UserForm1
        If ActiveDocument = ThisDocument Then
            MsgBox "You are attempting to process the template." & vbCr & "Create a new document from the template."
            GoTo lbl_Exit
        End If
        With oFrm
            .Show
            If .Tag = 0 Then
                ActiveDocument.Close SaveChanges:=False
            End If
            'do stuff related to the document and the userform here
            Unload oFrm
        End With
    lbl_Exit:
        Set oFrm = Nothing
        Exit Sub
    End Sub
    Ultimately whatever you do some user will screw it up. You just have to plan for everything.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    You're a legend Graham.

    this is defiantly what I want it to do however once the userform is run it comes up with this error


  8. #8
    What error? If the following still causes an error, can you post the template with the userform?

    Sub StartProcess()
    Dim oFrm As New UserForm1
        If ActiveDocument = ThisDocument Then
            MsgBox "You are attempting to process the template." & vbCr & "Create a new document from the template."
            GoTo lbl_Exit
        End If
        With oFrm
            .Show
            If .Tag = 0 Then
                ActiveDocument.Close SaveChanges:=False
                GoTo lbl_Exit 'add this line
            End If
            'do stuff related to the document and the userform here
            Unload oFrm
        End With
    lbl_Exit:
        Set oFrm = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  9. #9
    Sorry Graham,

    It's the "Run-Time Error '13': Type Mismatch" but it only appears in the .Dotm

    The debug seems to pick up this:

    Untitled.jpg

    both .docm and .dotm attached

    thanks again for all your help
    Attached Files Attached Files

  10. #10
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'They Clicked the "X". 
    
    'Close Document with out saving
    ActiveDocument.Close SaveChanges:=False
    
    End Sub
    Go back to the Docm and in the Document_Close Event sub, Call "FormsUnload."
    In Private Sub FormsUnload(), Unload all UserForms in the Document's UserForms Collection.
    This is usually not necessary, but, just in case, you don't want it/them floating in memory.

    nb. If UserForms.Count = 0 then there are no UFs loaded in memory.

    nb. Word is wierd, you maight have to check whether the Document is the Template or a regular document. :
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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