Search code examples
excelvbauserform

Excel Userform Run-Time Error when trying to launch a 2nd time after initial 'Cancel' or close on red 'X'

Problem: I am building a Userform that has a 'Submit' and 'Cancel' button. I want the entire form to clear any entered data and close the form if the user hits the 'Cancel' button but I also am trying to build in the same functionality if the user hits the red 'X' in the top right corner. I'm unclear where I need to unload the form. I currently have it placed within the btnCancel_Click() method and I'm able to launch the form, enter some data and hit Cancel and it will close the form down.

But when I try to re-launch the form a 2nd time I get an error (I attached a picture of that message) that says

"Run-Time error '-2177418105 (80010007): Automation Error - The Callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have executed.

If I remove Unload Me from btnCancel_Click() then the form can close and re-open just fine, but any data I entered the first time will still be on the form and isn't cleared properly. I'm wondering if this is an Unload Me error or do I need to reset all form controls when I initialize the form?

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  ' how was the form closed?
  ' vbFormControlMenu = X in corner of title bar
  If CloseMode = vbFormControlMenu Then
    ' cancel normal X button behavior
    Cancel = True        
    ' run code for click of Cancel button
    btnCancel_Click
  End If
End Sub
'******************************************************************
Private Sub btnCancel_Click()
    mbCancel = True
    Me.Hide
    Unload Me

End Sub
'*********************************************************************
Private Sub UserForm_Initialize()
'Populate values for 2 combo boxes
lastEmp = Sheets("Form_Ref").Cells(Rows.Count, 1).End(xlUp).Row
Me.cmbBoxEmpName.List = Sheets("Form_Ref").Range("A2:A" & lastEmp).Value

lastBld = Sheets("Form_Ref").Cells(Rows.Count, 2).End(xlUp).Row
Me.cmbBoxBuildingName.List = Sheets("Form_Ref").Range("B2:B" & lastBld).Value

End Sub
'******************************************************************
Public form As New CheckOutForm
Sub testFormOptions()
'Button pressed within Excel will start program and show the userform
form.Show

End Sub

enter image description here

Solution

  • This is the easiest quick and dirty solution:

    Delete Public form As New CheckOutForm from the code. Then add it in the testFormOptions():

    Sub testFormOptions()
    Dim form As New CheckOutForm
    form.Show
End Sub

    Some not-that-good VBA books/tutorials would even go a bit like this, but this is brutal:

    Sub testFormOptions()
    CheckOutForm.Show
End Sub

    Anyway, now the problem with the predefined values in the form is solved.

    For the clean and not-so-easy solution, consider writing a MVC framework around the form: