Userform Unintentionally Closing

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,893
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have an application in which userform1 (uf9_poststaff) is open.
From within that userform, the user can edit a textbox which triggers a set of calculations.

Depending on the result of the calculations, one of two 2nd forms (showmodal=true) are displayed for the user to work with. Both userforms have a [CANCEL] button intended to close the active userform and return them to the first userform (uf9_poststaff) where they can can continue editing information there.

With one form (uf9d_cupe1), cancelling will close that form, but the other stays open. The second one (uf9d_cupe1ot), with very similar code, results in that userform closing, but uf9_poststaff closing as well.

I will try to capture the code that I think is most relevant to the cause, but my apologies in advance if I've failed to include the relevant or complete code. The whole project is several thousand lines long, so not something I can post.

Suppose a change to textbox cu3_end of userform uf9d_poststaff. We will change the value from default "15:00" to "12:00"

Rich (BB code):
Private Sub cu3_end_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    If mbEvents Then Exit Sub
    On Error GoTo badtime
    bu = cu3_endbu.Value
    etc_cu3 = CDate(cu3_end.Value)
    Debug.Print etc_cu3
    Debug.Print bu
    If etc_cu3 < CDate(cu3_start.Value) Then
        errorcap1a = "Invaid time entry. Please retry."
        errorcap1b = "The end of the shift must be after it's start. [" & format(cu3_start.Value, "h:mm AM/PM") & "]."
        nt_invalid_time_entry.Show
        cu3_end.Value = format(bu, "h:mm")
        Exit Sub
    Else
        'cu2_end.Value = format(bu, "h:mm")
        stv2 = CDate(Me.cu3_start.Value)
        etv2 = CDate(Me.cu3_end.Value)
        jt = IIf(etv2 = 0, 1, etv2)
        Me.cu3_hours.Value = format(DateDiff("n", stv2, jt) / 60, "general number")
        If Me.cu3_hours.Value = 8 Then
            Me.cu3_notes.Value = ""
        ElseIf Me.cu3_hours.Value < 8 Then
            hd = 8 - CDbl(Me.cu3_hours)
            uf9dlb1 = "CUPE employee is deficient of min. 8 hours."
            uf9dlb2 = "Please select from below to account for " & hd & " hours."
            uf9d_cupe1.Show
            If absel <> "" Then
                Me.cu3_notes.Value = "[" & hd & "] hours " & absel & "."
                Me.cu3_hours.Value = "8"
            Else
                cu3_end.Value = format(bu, "h:mm")
                'cu2_end.Value = format(etc_cu2, "h:mm")
                stv2 = CDate(Me.cu3_start.Value)
                etv2 = CDate(Me.cu3_end.Value)
                jt = IIf(etv2 = 0, 1, etv2)
                Me.cu3_hours.Value = format(DateDiff("n", stv2, jt) / 60, "general number")
            End If
        Else        'overtime allocation? If Me.cu3_hours.Value > 8 Then
            hd = CDbl(Me.cu3_hours) - 8
            uf9dlb3 = "CUPE employee is elligible for overtime."
            uf9dlb4 = "Please select from below to account for " & hd & " hours."
            uf9d_cupe1ot.Show
            If absel <> "" Then
                Me.cu3_notes.Value = "[" & hd & "] hours " & absel & "."
                'Me.cu3_hours.Value = "8"
            Else
                cu3_end.Value = format(bu, "h:mm")
                'cu2_end.Value = format(etc_cu2, "h:mm")
                stv2 = CDate(Me.cu3_start.Value)
                etv2 = CDate(Me.cu3_end.Value)
                jt = IIf(etv2 = 0, 1, etv2)
                Me.cu3_hours.Value = format(DateDiff("n", stv2, jt) / 60, "general number")
            End If
        End If
        
    End If
    'Stop
    Exit Sub

badtime:
    errorcap1a = "Invaid time entry. Please retry."
    errorcap1b = "Enter time in 24H format (hh:mm)."
    nt_invalid_time_entry.Show
    cu3_end.Value = format(bu, "h:mm")

End Sub

With this code, error checking is first done to ensure a proper time (24hr format) has been entered. A calculation is used to determine the number of hours difference between two times, a shift start and end time. This value is the assessed. If it is less than 8, as it is in this case, a second form (uf9d_cupe1) is opened. This form's showmodal property is set to true and appears overtop the primary form when opened.

Should the user wish to bail from any further data entry on uf9d_cupe1, they may exit by pressing a "CANCEL" commandbutton. Doing this with close uf9d_cupe1 and return the user to uf9d_poststaff.

Rich (BB code):
Private Sub uf9d_cancel_Click()
    uf9dlb1 = ""
    uf9dlb2 = ""
    absel = ""
    Unload uf9d_cupe1
End Sub

The three variables in this code are declared publicly in another module.

Upon closing of this form, code is resumed after the purple line in the top code above. The carried over value of 'absel' is referenced. If it's empty, which it will be as it was emptied in the cancel button code, code is executed that returns some uf9d_poststaff fields back to the original, and then the code ends with "Exit Sub". Userform uf9d_poststaff remains open for the user to continue working with it.

Now, lets assume we change textbox cu3_end of userform uf9d_poststaff. We will change the value from default "15:00" to "21:00" This is opposite to the first scenario where the employee was deficient of hours. In this case they exceed 8 hours are eligible for overtime.

The Sub cu3_end_BeforeUpdate code is executed with the entry of the new value. The If Else/If End If routine is assessed determining that Me.cu3_hours.Value > 8 and the associated code is executed. Userform uf9d_cupe1ot is opened (showmodal property is set to true), while in the background uf9d_poststaff remains open.

Should the user wish to bail from any further data entry on uf9d_cupe1ot, they may exit by pressing a "CANCEL" commandbutton. Doing this with close uf9d_cupe1ot and return the user to uf9d_poststaff.

Rich (BB code):
Private Sub uf9d_cancelot_Click()
    uf9dlb3 = ""
    uf9dlb4 = ""
    cb_schot.Value = False
    cb_ciot.Value = False
    absel = ""
    Unload uf9d_cupe1ot
End Sub

Some variables are cleared, and a couple checkboxes reset prior to userform uf9d_cupe1ot closing. Once closed, code resumes above after the purple line. Variable absel is empty. The code then resets some fields in uf9d_poststaff, to fianlly exit the sub. Now ... unlike the similar situation above, uf9d_poststaff closes! It should remain accessible as it does in the first scenario.

Is anyone able to suggest why this code is providing a different outcome and what I could do to resolve it?

If I remove the "Exit Sub", the error section "badtime" is executed unwantingly.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,980
dont use : Unload uf9d_cupe1ot
until you want the form to close.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,893
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The effort to post was more than the possible (maybe not correct) solution.

Hi ranman ... thanks for your simple solution. I think I am interpreting what you are saying. I think I'm simply closing the forms at the wrong time. I should move them to the uf9_poststaff rather than from within the code of the respective form? I find it odd that I was able to close the similar form, uf9_cupe1, with no issue where it was coded, but uf9_cupe1ot coded at basically the same spot in different code resulted in a different outcome. I'll give it a try.

I also came up with a solution ... perhaps not a correct one ... but it seems to be doing the trick.

I changed the showmodel property of uf9_poststaff to true. This seems to keep all the forms open as expected. I simply found it odd that it worked for one, but not the other under similar circumstances.

If anyone is able to foretell problems with this "solution", do tell.
 
Last edited:

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
Ark68,

When opening the userform Modeless, try using
Code:
uf9_poststaff.Hide
 

Watch MrExcel Video

Forum statistics

Threads
1,130,323
Messages
5,641,531
Members
417,215
Latest member
Diaryman

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
Top