Workbook Becomes Unstable When Working WIth UserForm

[Ark68]

I have this code that calls a userform (uf_new_eqt) when the user selects "Other" from the cell's validation list.

'EQT CHANGE
'validation is coded based on work centre and does not change

    If Not Intersect(Target, Range("$M$4")) Is Nothing Then
        Application.ScreenUpdating = False
        mbevents = False
        Unprotect
       
        vEqt = Target.Value
       
    'invalid entries (not trapped by validation)
        'null value
            'If Range("$M$4") = "" Then
            'MsgBox "Please select the equipment from the dropdown." & Chr(13) & "(Select 'Other' to add equipment not in the list.)", vbCritical, "Invalid Entry [EQUIPMENT]"
            'Range("M4") = "!"
            'With Range("M4").Font
            '    .Color = vbRed
            '    .Italic = False
            'End With
            'mbevents = True
            'Protect
            'Application.ScreenUpdating = True
            'Exit Sub
        'End If

        'seperator selected
        If vEqt = "- - -" Then
            With Range("M4")
                .Value = ""
                .Color = RGB(19, 65, 98)
                .Italic = False
            End With
            mbevents = True
            Protect
            Application.ScreenUpdating = True
            Exit Sub
        End If
       
        'other selected
        If vEqt = "Other" Then
            reset_svr_buttons 'serices buttons reset until they can be defined
            mbevents = True
            uf_new_eqt.show 'equipment entry form
            Protect
            Application.ScreenUpdating = True
            Exit Sub
        End If
       
    'valid entry
        If vEqt = "" Then
            mbevents = True
            Protect
            Application.ScreenUpdating = True
            Exit Sub
        End If
        Range("M4:N4").Font.Color = vbBlack
       
        seqt = Application.WorksheetFunction.VLookup(vEqt, ws_lists.Range("E2:G36"), 2, False) 'selected equipment text
        Range("O4") = seqt
       
        'prepare start temp
        Range("F7:G7").Locked = False 'unlock start temp
        Range("F7:G7").Interior.Color = RGB(216, 241, 234)
       
        'populate data range
        Range("R" & dcomprow) = Range("M4")
        bdr_eqt 'reset border after missing data flag
       
    'service button determination
        reset_svr_buttons       'clear all service buttons
        determine_service_buttons vEqt 'activate appropriate service buttons based on equipment selection
       
        Range("F7").Select
        Protect
        mbevents = True
        Application.ScreenUpdating = True
    End If

The userforms open, and the user enters a value in textbox1. An AfterUpdate event does some error checking on the value entered, including checking the existing database to see if the value entered is already in the database. If it is, there is no need to continue with the usedform. The value entered in textbox1 is moved to the userfield in the worksheet which in turn reruns the worksheet change event for that cell.

Private Sub TextBox1_AfterUpdate()
    'Stop
    If Len(TextBox1.Value) < 2 Then
        MsgBox "Enter a valid (2+ numeric characters) equipment number.", vbExclamation, "Invalid Entry [EQUIPMENT]"
        TextBox1.Value = ""
        If tb_tally > 0 Then tb_tally = tb_tally - 1
    ElseIf IsNumeric(Me.TextBox1.Value) = False Then
        MsgBox "Enter a valid (2+ numeric characters) equipment number.", vbExclamation, "Invalid Entry [EQUIPMENT]"
        TextBox1.Value = ""
        If tb_tally > 0 Then tb_tally = tb_tally - 1
    ElseIf Application.WorksheetFunction.CountIf(ws_lists.Columns("AQ"), Me.TextBox1.Value) > 0 Then
        Stop
        srow = Application.WorksheetFunction.Match(CDbl(Me.TextBox1.Value), ws_lists.Columns("E"), 0)
        MsgBox "Unit " & Me.TextBox1.Value & ", " & ws_lists.Cells(srow, 6) & ", exists in the database already." & Chr(13) _
            & "Please select the unit from the dropdown selection.", vbInformation, "Duplication"
        mbevents = True
        ws_gui.Range("M4") = Me.TextBox1.Value
        Unload Me
        'mbevents = True
        Exit Sub
    Else
        tb_tally = tb_tally + 1
    End If
    tally = cb_tally + tb_tally + hb_tally
    If tb_tally = 2 And hb_tally = 1 And cb_tally > 0 Then Me.uf_eqt_submit.Enabled = True
    Me.TextBox2.Locked = False
End Sub

I run into big issues though after the userform is closed.

Following the Unload Me command, I get this error:
"Automation error. The object invoked has disconnected from it's clients"

When I press Debug, it takes me to the worksheet change code where the userform was originally called (the orange line). It wants to reload the form.
If I stop the code, Excel closes and reopens with a recovered version of the workbook.

Can someone identfy the issue and help me avoid this problem.

 

[RasGhul]

I'm no VBA expert, and I'm not sure about your code overall. But the use of application.screenupdating=true seems to be too many?

 

[Zyndstoff]

I tend to think that you unload the UF but there still is some code that should have been executed: the "exit sub" command. This in turn can't be done since the UF, where the code is originated, has been unloaded.
Why don't you just use "Me.Hide" instead of "Unload Me"?

 

[Ark68]

Hi RasGhul, thank you for your observation. Eventually, I will go through and remove any redundant instances of application.screenupdating, but for now I just have them in to rule out any potential loose ends of instances that may cause issues. I'm not fantastic at code.

Zundstoff ... thank you. But I will need to close the userform eventually. I'm not quite sure where that would be. I did remove the exit sub as really, it's redundant. Closing the form does that, and should return to the code that called the userform open. Right now, the userform closes itself either by reacting to an improper entry (as in this example), or when the user presses the submit button which process the data on the userform and then closes it as it's no longer needed, or when the user cancels the action with an exit button on the form. The latter two workout issue when the userform close is included in their respective procedures.

I find it odd (only my novice understanding of VBA) that when the userform is closed it returns to the line in the standard module that called to have the userform opened. Why would't it proceed to the next line?

I'm not sure how it's any different than:

Private Sub uf_eqt_exit_Click()
    Stop
    Application.ScreenUpdating = False
    mbevents = False
    With ws_gui
        .Unprotect
        .Range("M4").Value = "- Select -"
        .Protect
    End With
    mbevents = True
    Application.ScreenUpdating = True
    Unload Me

End Sub

 

[RoryA]

A userform should really never unload itself. The code that called it should take care of that. I'd also recommend that the calling code deal with changing the cell value as appropriate. I'm not sure why you want to deliberately trigger the change event again?

 

[Ark68]

Thanks Rory ... I'll tinker away an my interpretation of your advice.

 

[Ark68]

I assume I'm not catching on to the advice.

I stripped my textbox change event so that no error checking is done within the userform. I even took out changing the cell value to avoid triggering the change event (which I needed to do to populate another field - textbox2 - that relied on the valid data entered into textbox1). I still get the same phenomenon happening. This is what I tried ... it may be functionally no different, just a different place.

Private Sub TextBox1_AfterUpdate()
    Dim v2Eqt As String
    v2Eqt = TextBox1.Value
    eqt_err_chk v2Eqt
End Sub

Sub eqt_err_chk(ByRef v2Eqt As String)

    If Len(v2Eqt) < 2 Then
        MsgBox "Enter a valid (2+ numeric characters) equipment number.", vbExclamation, "Invalid Entry [EQUIPMENT]"
        uf_new_eqt.TextBox1.Value = ""
        If tb_tally > 0 Then tb_tally = tb_tally - 1
    ElseIf IsNumeric(v2Eqt) = False Then
        MsgBox "Enter a valid (2+ numeric characters) equipment number.", vbExclamation, "Invalid Entry [EQUIPMENT]"
        uf_new_eqt.TextBox1.Value = ""
        If tb_tally > 0 Then tb_tally = tb_tally - 1
    ElseIf Application.WorksheetFunction.CountIf(ws_lists.Columns("AQ"), v2Eqt) > 0 Then
        Stop
        srow = Application.WorksheetFunction.Match(CDbl(uf_new_eqt.TextBox1.Value), ws_lists.Columns("E"), 0)
        MsgBox "Unit " & v2Eqt & ", " & ws_lists.Cells(srow, 6) & ", exists in the database already." & Chr(13) _
            & "Please select the unit from the dropdown selection.", vbInformation, "Duplication"

        Unload uf_new_eqt
    Else
        tb_tally = tb_tally + 1
    End If
End Sub

Does any of this help reveal the deeper cause?

 

[RoryA]

That is indeed functionally no different.

You need to just hide the form in the textbox event (after validating the input). Unloading the form should be done by the Worksheet_Change code since that is what loaded the form.

I'm not saying that is necessarily the cause of your particular issue (it's often hard to diagnose userform issues without the actual workbook) but it is good practice.