Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Userform Closing Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    469
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Userform Closing Excel

    Hello,

    I have a userform that has decided that it is going to close excel each time I use it. I'm not sure where I've gone wrong in my coding and was hoping someone could double check it for me.

    thanks!

    Code:
     Private Sub CommandButton5_Click()'"next" page button
    On Error GoTo Helper
    'Application.Visible = False
        Unload Me
        UserForm2.Show
    
    
    'Error Clearing Code
    Exit Sub
    Helper:
        resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
        "with error codes [1044] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
        "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
            If resp = vbYes Then
                UserForm18.Show
                'MsgBox ("Success")
            ElseIf resp = vbNo Then
                Exit Sub
            ElseIf resp = vbCancel Then
                Exit Sub
            End If
    End Sub
    
    Private Sub UserForm_Terminate()
    On Error GoTo Helper
    Unload Me
                'Application Closer
                    If Workbooks.Count > 1 Then
                        ActiveWorkbook.Close
                    Else: Application.Quit
                    End If
    'Error Clearing Code
    Exit Sub
    Helper:
        resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
        "with error codes [1060] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
        "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
            If resp = vbYes Then
                UserForm18.Show
                'MsgBox ("Success")
            ElseIf resp = vbNo Then
                Exit Sub
            ElseIf resp = vbCancel Then
                Exit Sub
            End If
    End Sub

  2. #2
    Board Regular synergy16's Avatar
    Join Date
    Mar 2016
    Location
    new england
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Closing Excel

    do you have another workbook open?

  3. #3
    Board Regular
    Join Date
    Oct 2018
    Posts
    469
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Closing Excel

    Quote Originally Posted by synergy16 View Post
    do you have another workbook open?
    First time it happened, yes.

    Second time I tried, no.

    Repeated attempts, no.

    The userform should call another userform (#2) when I hit the CMD ("Next") button, not close excel....

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Userform Closing Excel

    In this part

    Code:
    Private Sub UserForm_Terminate()
    On Error GoTo Helper
    Unload Me
                'Application Closer
                    If Workbooks.Count > 1 Then
                        ActiveWorkbook.Close
                    Else: Application.Quit
                    End If
    'Error Clearing Code
    Exit Sub

    If the active book is the book of the form, then the book is closed.
    Code:
    If Workbooks.Count > 1 Then
                        ActiveWorkbook.Close
    If there is more than one book, then you close excel
    Code:
    Else: Application.Quit
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Oct 2018
    Posts
    469
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Closing Excel

    Yes, but I wrote that thinking that if the userform was closed (i.e. hitting the "x") it would close the workbook. The CMD button code (a "Next" button) should take the user to the next userform and unload the current.

  6. #6
    Board Regular
    Join Date
    Oct 2018
    Posts
    469
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Closing Excel

    ahhh ok so now I see your point! thank you!

    So question... If I wanted the "x" for the userform to close the workbook, what would that code look like- basically I don't want the user to get into the workbook if they close the userform (because the userform requires a "password" to proceed and closing the userform gets past the password!).

    Thanks

  7. #7
    Board Regular
    Join Date
    Oct 2018
    Posts
    469
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Closing Excel

    Nevermind I'm being an idiot this morning....duhhhh

    Query Close will do the trick......thanks!

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Userform Closing Excel

    Do not worry, sometimes it happens to all of us
    Regards Dante Amor

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,499
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Userform Closing Excel

    Just as an aside, there is no point in having Unload Me in the Terminate event. It's already unloading if that event is firing!

  10. #10
    Board Regular
    Join Date
    Oct 2018
    Posts
    469
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Closing Excel

    Yep....i'm not even sure how I came to write the terminate event in there....I was shooting myself in the foot...I'm guessing it was one of my late-night sessions.

    Thanks!

    On a side note- anyone have ideas on if I can get a userform to display an error code from another module?

Some videos you may like

User Tag List

Tags for this Thread

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
  •