Error Closing Userform

S

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
I'm popping an error when I hit the "X" closing a userform. Here's the code that should close it. Am I missing something obvious?

thanks!

Code: 
Private Sub Userform_QueryClose(cancel As Integer, closemode As Integer)On Error GoTo Helper
If closemode = 0 Then
    'Application Closer
    If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
    Else: Application.Quit
    End If
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 [1026] 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
            Call Error_Handle(sProcName, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub
 
Sort by date Sort by votes
In that case
Code: 
With Activesheet
    .Range("D9").Formula = IIf(.Previous.Name Like "Noon*","='Voyage Specifics'!D8","=PrevSheet(D8)")
End With
If you want to use a variable to hold a formula then it should be String, not Double, although there is no need for it here.
Also, you're attempting to use A1 references with FormulaR1C1 which will not work, even if your syntax was correct.
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Interesting. So pure curiosity, and again thank you because this is a huge help,

The R1C1 seems to be working (minus in that position). Now I tried adding brackets and running the formula which seemed to work but I can correct. R1C1 is purely based on columns and rows then...understood.

Now last piece- if I want to define a “name” that’s displayed in the top bar of a pop up window, that would be a string, agreed? Same as if I wanted to define a word within a pop up text box (see below) looking at defining sprocname

Code: 
If resp = vbyes then
Call error_handle(sprocname, err.number, err.description)
 
Last edited:
Upvote 0
That is correct, assuming that by pop up, you mean message box then you would assign the string to the title.
 
Upvote 0
Excellent. Thank you thank you.

Ok I found the userform closing issue that has plagued me. So, if another excel workbook is open, then when I close one of the initial (opening) userforms, this problem isn't experienced. If this workbook, upon opening, is the only one open, the problem persists. SO, here's how the workbook is designed to open:

The user clicks to open the workbook. The workbook should immediately open and hide itself, presenting only a userform (so that this looks like a program running). Through a series of userforms, I've essentially created menus and such that the user can click through. Various options open other userforms which, in turn, create various excel sheets. The "base sheets," or sheets that are universal across all of the various workbook options, are hidden upon the creation of optional sheets, and thus these hidden sheets hold "user preferences" (names and references for VBA) as well as give VBA a reason to exist.

Now, if I close one of the early userforms, I generate an error that goes to this procedure. I attempted to remove the "On error goto" piece so I could debug the error but that failed- when clicking debug, it would flash the vba screen and then shutdown excel. I've included the shutdown code following below. Thanks!

Code: 
Sub SetButtons()

On Error GoTo Helper
        
        Dim a As Button
        Dim b As Button
        Dim c As Button
        Dim d As Button
        Dim e As Button
        Dim f As Button
        Dim j As Button
        Dim k As Button
        Dim l As Button
        Dim m As Button
        Dim n As Button
        Dim o As Button
        Dim p As Button
        Dim q As Button
        Dim r As Button
        Dim s As Button
        Dim t As Button
        Dim u As Button
        Set w = Sheets("Developer")
        
        w.Buttons.Delete
        
        Set a = w.Buttons.Add(30, 345, 150, 25)
        a.OnAction = "DemoTest"
        a.Characters.Text = "Registration"


        Set b = w.Buttons.Add(190, 345, 150, 25)
        b.OnAction = "ClearRegistry"
        b.Characters.Text = "Clear Registration"


        Set c = w.Buttons.Add(30, 385, 150, 25)
        c.OnAction = "Transfer1"
        c.Characters.Text = "Make a Program Copy"
        
        Set d = w.Buttons.Add(190, 385, 150, 25)
        d.OnAction = "Terminate"
        d.Characters.Text = "Delete All Active Directories"


        Set e = w.Buttons.Add(30, 425, 310, 25)
        e.OnAction = "Terminate_Program"
        e.Characters.Text = "Terminate Program"
                        
        Set f = w.Buttons.Add(375, 25, 150, 25)
        f.OnAction = "VoyageSpecifics"
        f.Characters.Text = "New Voyage Specifics Sheet"
        
        Set j = w.Buttons.Add(375, 65, 150, 25)
        j.OnAction = "Addnoonsheet"
        j.Characters.Text = "New Noon Sheet"
        
        Set k = w.Buttons.Add(375, 105, 150, 25)
        k.OnAction = "addnoonssheet"
        k.Characters.Text = "New Noon# Sheet"
        
        Set l = w.Buttons.Add(375, 145, 150, 25)
        l.OnAction = "arrivalsheetmaker"
        l.Characters.Text = "New Arrival Sheet"
        
        Set m = w.Buttons.Add(375, 225, 150, 25)
        m.OnAction = "Removeprotection"
        m.Characters.Text = "Unprotect All Sheets"
        
        Set n = w.Buttons.Add(375, 265, 150, 25)
        n.OnAction = "master_reset"
        n.Characters.Text = "Reset Master Template"
        
        Set o = w.Buttons.Add(375, 185, 150, 25)
        o.OnAction = "testcreator"
        o.Characters.Text = "Test Voyage"
        
        Set p = w.Buttons.Add(375, 305, 150, 25)
        p.OnAction = "Kill_error_log"
        p.Characters.Text = "Delete Error Log"
        
        Set q = w.Buttons.Add(375, 345, 150, 25)
        q.OnAction = "Email_Developer"
        q.Characters.Text = "Email Error Log"
        
        Set r = w.Buttons.Add(375, 385, 150, 25)
        r.OnAction = "Open_error_log"
        r.Characters.Text = "View Error Log"
        
        Set s = w.Buttons.Add(375, 425, 150, 25)
        s.OnAction = "DevSave"
        s.Characters.Text = "Save"
        
        Set t = w.Buttons.Add(375, 465, 150, 25)
        t.OnAction = "Quitter"
        t.Characters.Text = "Quit"
        
'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 [1010] 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
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

Shutdown Code
Code: 
 Private Sub Userform_QueryClose(cancel As Integer, closemode As Integer)
On Error GoTo Helper
If closemode = 0 Then
    'Application Closer
    If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
    Else: Application.Quit
    End If
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 [1026] 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
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

AND

Private Sub Workbook_BeforeClose(cancel As Boolean)
On Error GoTo Helper
'This is the last event to run as Excel is closing


Sheets("Notes").Visible = xlVeryHidden
Sheets("Developer").Visible = xlVeryHidden




'Kills Noon/Arrival Sheets to Preserve Template
If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
        Application.DisplayAlerts = False
        Sheets("Developer").Range("A2:D3,A5:D5,A7:D7").ClearContents
    For Each sh In ThisWorkbook.Worksheets
        If LCase(sh.name) Like "noon*" Then
            If Len(sh.name) > 4 Then
                If IsNumeric(Mid(sh.name, 5, Len(sh.name))) Then sh.Delete
            Else
                sh.Delete
            End If
        End If
    Next
    If SheetExists("Arrival") Then
        Sheets("Arrival").Delete
    End If
    If SheetExists("Voyage Specifics") Then
        Sheets("Voyage Specifics").Delete
    End If
Else: Sheets("Ports").Visible = xlVeryHidden
End If


Application.DisplayAlerts = True


ActiveWorkbook.Save


'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 [1014] 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
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If


End Sub

Any thoughts on how I might go about debugging this?
 
Last edited:
Upvote 0
Why not use
Code: 
If Workbooks.Count = 1 Then workbooks.Add
to create a new workbook if there is only one workbook open?

I could be wrong, but I have a feeling that trying to run the code with no 'Active' workbook could be something that will never work.
 
Last edited:
Upvote 0
Sorry, I'm confused what you mean with this.

The shutdown code is intended to close only the activeworkbook if other workbooks are open and if the active is the only one open, then close excel. It seems to work everywhere excel if I close a starting (initial) userform. If I close an initial userform, I get a "1010" (I made the numbers so I could quickly find the errors) error which means the error was in that button creation set of code and I'm not sure why nor how to debug it. The button creation shouldn't throw an error (in my opinion) if the userform is closed.
 
Last edited:
Upvote 0
What I mean is that I think that this could be the problem when there is no other workbook open.
sassriverrat said:
The workbook should immediately open and hide itself, presenting only a userform (so that this looks like a program running).
Click to expand...
Have you tried closing the workbook nstead of quitting the application?

Another thing to try would be to enter the Stop command as the first line of the code, then use f8 to step through it until you find thepoint of failure.
 
Upvote 0
jasonb75 said:
What I mean is that I think that this could be the problem when there is no other workbook open.

Have you tried closing the workbook nstead of quitting the application?

Another thing to try would be to enter the Stop command as the first line of the code, then use f8 to step through it until you find thepoint of failure.
Click to expand...


I've tried closing the workbook (but then the application has to be shown again and closed. Currently, the application is hidden when the userforms are showing.

So I inserted the stop command in the userform query close coding on multiple userforms (I could cause the 1010- 424 object required error to popup closing various userforms) and when stepping through the whole series of closing code, it didn't seem to have a problem....not sure why. So the only time the coding that seems to error (1010) errors is in the startup code below. See anything?

Code: 
Private Sub Workbook_Open()


'On Error GoTo Helper


'This will show the Userform 1 when starting and hide the excel sheet in the background
'Also checks to see if program has ever been run, and if not, loads program for the first time
Application.Visible = False
Dim s As String
Dim namer As String
Dim d As Variant
Dim ExpirationDate As Date
Dim pass1 As String
Dim pass2 As String




With Sheets("Developer")
    s = .Range("B34")                   'username
    'pass1 = .Range("B15:E15").Value     'sheet unprotect
    'pass2 = .Range("B39").Value         'date password
    ExpirationDate = .Range("E37")      'expiration date
    d = .Range("B39").Value             'registration key
    initialdate = .Range("C36")         'initializing date
End With




'ExpirationDate = (Sheets("Developer").Range("E37"))     'expiration date
'edate = Sheets("Developer").Range("E37")
namer = Sheets("Notes").Range("N4")                     'just a name






s = GetSetting("DemoTest", "Registration", "Username")
'Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
If s = "" Then
        Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
        Sheets("Developer").Range("B34:F34").ClearContents
        's = InputBox("Welcome to the " & namer & " Voyage Reporting System." & vbCrLf & "Please input the appropriate name to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", namer, "Bridge")
        'UserForm17.Show
        s = cInputBox()
        'MsgBox s
        
        If s <> "" Then
            's = cInputBox()
            Sheets("Developer").Range("B34") = s
            SaveSetting "DemoTest", "Registration", "Username", s
            Sheets("Notes").Visible = xlSheetVisible
            Sheets("Notes").Select
            Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
            Sheets("Developer").Range("C36") = Date
            'If s <> "" Then MsgBox "Welcome to the " & name & " Voyage Reporting System." & vbCrLf & "Please input the appropriate data to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", vbOKOnly, name
            Application.Visible = True
        End If


Else:
    If ExpirationDate > Date Then
        If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
             UserForm1.Show
        ElseIf ActiveWorkbook.name = "Current Voyage Report.xlsm" Then
             UserForm2.Show
        Else: UserForm3.Show
        End If
    Else:
TryAgain:
        d = InputBox("Your workbook date has expired. Please enter the registration key to renew your license.", namer)
        If d = vbCancel Then GoTo Closer
        If d = "" Then GoTo Wrong
        If d = CStr(Worksheets("Developer").Range("B39").Value) Then
            Sheets("Developer").Range("C36") = Date
            MsgBox "Welcome Back " & s, vbOKOnly, namer
        Else: GoTo Wrong
        End If
        If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
             UserForm1.Show
        ElseIf ActiveWorkbook.name = "Current Voyage Report.xlsm" Then
             UserForm2.Show
        Else: UserForm3.Show
        End If
    End If
        
End If




'Application.Visible = True   'inserted just to check workbook
'Protects/Hides sheets on startup
Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.name = "Notes" Then
            sh.Protect Password:=Worksheets("Developer").Range("B17:E17").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Ports" Then
            sh.Protect Password:=Worksheets("Developer").Range("B19:E19").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Developer" Then
            sh.Protect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
            End If
    Next sh
    
'Sets buttons on Developer sheet
Call SetButtons
    


Exit Sub
'Closing code
Closer:
    If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
    Else: Application.Quit
    End If


Exit Sub
'Error Clearing Code
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 [1011] 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
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
        
Exit Sub
'Wrong coding
Wrong:
            MsgBox "Password Incorrect, Please try again.", vbCritical, namer
            GoTo TryAgain
    
End Sub
 
Upvote 0
Try the Stop command as the first line of workbook open, then step through.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
UserForm Close Event Hangs
Replies
0
Views
502
trough
T
B
  • Solved
Issue with Userform and List Box on MAC
Replies
4
Views
1K
bcmk29
B
D
  • Question Question
Issue with EntireRow.Delete
2
Replies
11
Views
1K
RoryA
RoryA
R
  • Question Question
Search for values using an advanced search and update then display pivot table results in a userform listbox
Replies
1
Views
562
Rfriend
R
R
  • Solved
Error 1004 - Delete method of range class failed
3 4 5
Replies
42
Views
3K
MARK858
MARK858

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top