VBA Automation Error - "server [not server application]" upon closing form

Question

I've tried creating a form in the object-oriented manner as shown in this answer: https://stackoverflow.com/a/38382104/4460023. Upon closing the form, I'd like to refer to the object property IsCancelled to check if the calling subroutine should continue executing. However, when I check this property outside of the form, I run into the following error:

"Run-time error '-2147418105': Automation error. The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have executed."

I'm guessing this has something to do with the form being closed. As an alternative solution, I simply write to a global variable stored within the calling sub's module. Ideally though, I'd like to use the property within this form object. My code is included below:

Within the form:

Private cancelling As Boolean

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelling
End Property

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        cancelling = True
    End If
End Sub

And then within the calling subroutine:

Set frm = New ViewByWorkerForm
frm.Show

If frm.IsCancelled Then 'error happens here
    Exit Sub
End If

Note that I have other string properties within the class that I can use when I do not close the form - it's only the form closing that triggers this problem.

Have a look [here](https://excelmacromastery.com/vba-user-forms-1/#Cancelling_the_UserForm) how to do it — Storax, Jul 04 '18 at 19:54

score 3 · Accepted Answer · answered Jul 04 '18 at 19:59

3

To fix your code

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
    End If
    Hide
    cancelling = True
End Sub

answered Jul 04 '18 at 19:59

Storax

11,158
3
16
33

Fantastic Answer! – Andy Nov 11 '21 at 19:35

score 0 · Answer 2 · answered Feb 11 '21 at 08:00

For me, I don't have a "red x" (I hide it) and I didn't want to use Booleans. I call my UserForms like so via an Event:

Public Sub UserForm_Sub()

'Application.ScreenUpdating = False

Set UserFormUserForm = New UserFormUserForm
On Error GoTo ErrorHandler
UserFormUserForm.ShowUserFormUserForm

GoTo Continue
ErrorHandler:
ModelessFormShowing = False
Continue:

End Sub

It was a simple matter of utilizing Excel's Error Handler in my case. You might be find w/ On Error Resume Next, but in my case, I needed to set a ModelessFormShowing boolean to false if form errored during launch.

VBA Automation Error - "server [not server application]" upon closing form

2 Answers2

Linked