CODEWITHSUNDEEP
excelvbauserform
arooney88
arooney88

Reputation: 305

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

Upvotes: 1

Views: 1481

Answers (2)

FunThomas
FunThomas

Reputation: 29181

If you execute Unload, you destroy the form object. With other words, your (global) variable form gets invalid and if you issue a new form.show, you get the runtime error.
When, on the other hand, you just unhide the form, the form-object stays valid (it's just currently not visible) and all controls keep their value.

Either you do some housekeeping by resetting all controls when a form is displayed (use the UserForm_Activate-event), or you have to create a new form-object every time you want to display it (I would strongly advice not to use the name form as variable name to avoid confusion).

Sub testFormOptions()
    dim myForm as CheckOutForm
    myForm.Show
End Sub

Upvotes: 1

Vityata
Vityata

Reputation: 43585

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:

Upvotes: 1

Related Questions