VBA Excel Run-time error Automation error

Question

I had a Run-time error

'-2147418105 (800100007)': Automation error The object invoked has disconnected from its clients.

which is raised once in a while. I can't relate it to a specific context for this error. The only clue I have is that before using ADO code I never had that error. The implemented pattern was used many times.

I use Excel 2016 32 bits on windows 7 with a vba code.

Private mForm As frmCfgPrjctTm
Public Sub U_CfgPrjctTm_OnOpen()
    If (mForm Is Nothing) Then
        Call U_UnlockTeam
        Set mForm = New frmCfgPrjctTm
    End If
    '>>>>>> the error occurs after this comment
    mForm.Show vbModeless
End Sub

The code to "close" the form is as following

Public Sub U_CfgPrjctTm_OnClose()
    If (Not mForm Is Nothing) Then
        mForm.Hide
        Dim tmp As frmCfgPrjctTm
        Set tmp = mForm
        Set mForm = Nothing
        Unload tmp
    End If
End Sub

and in the form code (childCfgPrjctTmSettings and childCfgPrjctTmSettings are defined in an enum to falg a user action before closing the form)

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Call U_UnlockTeam
    Select Case CloseMode
        Case vbAppWindows, vbAppTaskManager
            Call U_CfgPrjctTm_OnClose
        Case vbFormControlMenu, vbFormCode
            Call Save
            Select Case mbOpenForm
                Case childCfgPrjctTmSettings
                    ' this opens another form
                    Call U_Sttngs_OnOpen(delUsr)
                Case childCfgPrjctTmUsrId
                    ' this opens another form
                    Call U_UsrLggd_OnOpen(dpyUsrLggdCfgPrjctTeam)
            End Select
    End Select
    Cancel = False
End Sub

and in the form code

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call U_UnlockTeam
Select Case CloseMode
    Case vbAppWindows, vbAppTaskManager
        Call U_CfgPrjctTm_OnClose
    Case vbFormControlMenu, vbFormCode
        Call Save
        Select Case mbOpenForm
            Case childCfgPrjctTmSettings

                Call U_Sttngs_OnOpen(delUsr)
            Case childCfgPrjctTmUsrId
                Call U_UsrLggd_OnOpen(dpyUsrLggdCfgPrjctTeam)
        End Select
End Select
Cancel = False

End Sub

This error is raised after creating a form and at the very moment to show it up. The call U_UnlockTeam involves some ADO code called inside to retreive data from a data base. The form has no Activate event handler. Did some one have the same issue and how did you cope with ?

Suggestions online are that this might be activeX related (perhaps the form or something on it) and that ActiveX.exe is running out of memory which would explain the intermittent behavior you are experiencing. I've not run into this problem myself, but this feels like the most likely cause. — JNevill
– JNevill, Commented Mar 2, 2018 at 15:05
I am able to reproduce the error. Could you show your code of the form when you close it or click on the x. I am quite sure you have an unload in your userform — Storax
– Storax, Commented Mar 2, 2018 at 16:02

Storax · Accepted Answer · 2018-03-02 16:11:30Z

I am able to reproduce the error. The issue is that you unload the form inside the form. Take just an empty userform and the following code in a module. Run the code and close the form by clicking on X. There should be no code behind the form! If you run the code a second time you will get the error mentioned.

Option Explicit

Private mForm As UserForm1
Public Sub U_CfgPrjctTm_OnOpen()
    If mForm Is Nothing Then
        'Call U_UnlockTeam
        Set mForm = New UserForm1
    End If
    '>>>>>> the error occurs after this comment
    mForm.Show vbModeless
End Sub

Reason of the behavior is that the class destroyed itself and mForm is a module wide variable which does not know it was destroyed when calling the code the second time.

Solution would be to avoid a self-destroing class/userform or as a workaround make mForm a local variable.

Here is a better explanation https://excelmacromastery.com/vba-user-forms-1/#Cancelling_the_UserForm

Do you mean that it is not necessary to use unload if UserForm_QueryClose is used ?
Yes, you should not use unload in the form code itself. Always do that in the calling code of the form. Also have a look here rubberduckvba.wordpress.com/2017/10/25/userform1-show

Collectives™ on Stack Overflow

VBA Excel Run-time error Automation error

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related