Userform Unintentionally Closing

[Ark68]

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"

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.

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.

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.

 

[ranman256]

dont use : Unload uf9d_cupe1ot
until you want the form to close.

 

[Ark68]

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.

 

[CalcSux78]

Ark68,

When opening the userform Modeless, try using

uf9_poststaff.Hide