Can't close userform

Question

Let me set up the environment.

This is VBA code running in Excel.

I have a userform that contains a mxflexgrid. This flexgrid shows a list of customers and the customer', salesperson, csr, mfg rep, and territories, assignments. When you click in a column, let's say under the Territory column, another userform opens to show a list of Territories. You then click on the territory of your choice, the userform disappears and the new territory takes the place of the old territory.

This all works great until you click on the territory of your choice the 'Territory' userform does not disappear (it flickers) and the new territory does not transfer the underlying userform.

I should mention that when I'm stepping through the code it works great.

I'm assuming it has something do to with the flexgrid as all the other userform (that don't have flexgrids) that open userform work just fine.

Following is the some code sample:

** Click event from flexgrid that shows Territory userform and assignment of new territory when territory userform is closed.

Private Sub FlexGrid_Customers_Click()

With FlexGrid_Customers

    Select Case .Col
        Case 0
        Case 2
        Case 4
        Case 6
            UserForm_Territories.Show
        Case Else
    End Select

    If Len(Trim(Misc1)) > 0 Then
        .TextMatrix(.Row, .Col) = Trim(Misc1)
        .TextMatrix(.Row, .Col + 1) = Trim(Misc2)
    End If

End With

End Sub

** The following Subs are used in the Territory userform

Private Sub UserForm_Activate()

Misc1 = ""
Misc2 = ""

ListBox_Territory.Clear
Module_Get.Territories

End Sub

Private Sub UserForm_Terminate()

Set UserForm_Territories = Nothing

End Sub

Private Sub ListBox_Territory_Click()

With ListBox_Territory
    Misc1 = Trim(.List(.ListIndex, 0))
    Misc2 = Trim(.List(.ListIndex, 1))
End With

Hide
UserForm_Terminate

End Sub

I know this a long winded explaination but I'm a fairly decent VBA programmer and this has me stumped.

Any help would be greatly appreciated.

Shawn IT Manager

Answer 1

I'm not going to say what you're doing is wrong (in that it won't ever work), but it scares the heck out of me. This is not the way I'd deal with forms.

Firstly, you're using UserForm_Territories (the class/form name) to refer to an implicitly-created instance of the form. This is something I've always avoided doing. I would always create an instance of a form explicitly, so instead of:

UserForm_Territories.Show

I would do:

Dim oTerritoriesForm As UserForm_Territories
Set oTerritoriesForm = New UserForm_Territories

oTerritoriesForm.Show vbModal

' get the values from the form here

Unload oTerritoriesForm

Next, and much more worryingly, you're subverting the UserForm_Terminate behaviour by calling it explicitly. Why you're doing this I can't imagine, unless you thought that it would work around your stated problem. My advice: don't do that.

Worse, you're attempting to assign to the implicitly-created instance of the form within that Terminate method. You shouldn't be doing that, either. I'm surprised that even compiles.

It seems like you're trying to force the implicitly-created instance of the form to mimic an explicitly-created one. In which case, create it explicitly, as shown above.