Collection of UserForms at Runtime - Opening and Closing?

[ClimoC]

Hello

I've been around the woodwork with this one, so hopefully I haven't gone totally off track.

Got the feeling I'm straying into the OO problems of VBA/VB6...

Desired Outcome: Creating (no more than 2 at any one time) multiple instances of the same Userform, at runTime, and then referring to them (to open/close etc) by a key.

I created a basic Collection, and add the forms to that, no problem. I even had it check before creating a new userform and adding it whether there were already 2, in which case close the older one (and remove it from the Collection). This worked seamlessly, using the following code:

'rls is the unique "key" I would refer to it as, if I could get it to work

Public Function ShowMTPViewForm(ByVal rls As Long)
Dim mForm As Object, oldmForm As Object
SpMTPsRlsID = rls  'This is a Public Long used to pass the Userform the 'key', so when it loads its data, it can scan the recordset for this key


If MTPVFormColl Is Nothing Then Set MTPVFormColl = New Collection


If MTPVFormColl.Count > 1 Then
        Set oldmForm = MTPVFormColl.Item(1)
        Unload oldmForm
        MTPVFormColl.Remove (1)
End If


Set mForm = New MTPView  'MTPView is the name of the Userform
MTPVFormColl.Add mForm, CStr(rls)
mForm.Show
End Function

...So that works great. The forms load their own data, etc. It's fine.

Problem is when you manually close the userform (CloseMode = 0).

The Userform isn't removed from the Collection - so the count in the collection will be wrong (and it could remove the wrong userform) - and if the next Userform it tries to load is one of the two already in the collection, of course you get the error "This key is already associated..."

SO, I tried to create a class, and then I could explicitly refer to the Userform at runTime by its key.

So I made the class:

'Class name cMTPViewForm
Private myForm As MSForms.UserForm
Private myRelease As Long


Public Property Get UForm() As MSForms.UserForm
        Set UForm = myForm
End Property


Public Property Let UForm(f As MSForms.UserForm)
        Set myForm = f
End Property


Public Property Get Release_ID() As Long
        Release_ID = myRelease
End Property
Public Property Let Release_ID(rls As Long)
        myRelease = rls
End Property

...and changed the Collection code stuff to this:

Public Function ShowMTPViewForm(ByVal rls As Long)
Dim mForm As cMTPViewForm, oldmForm As cMTPViewForm
Dim actForm As Object
SpMTPsRlsID = rls


If MTPVFormColl Is Nothing Then Set MTPVFormColl = New Collection


If MTPVFormColl.Count > 1 Then
        Set oldmForm = MTPVFormColl.Item(CStr(rls))
        Unload oldmForm
        MTPVFormColl.Remove (CStr(rls))
End If


Set mForm = New cMTPViewForm
MTPVFormColl.Add mForm, CStr(rls)
Set actForm = mForm.UForm
actForm.Show
End Function

It all seems to work - right up until

actform.Show

At which point I just get 'Object variable or With block variable not set' - the form is never instantiated (at least, the initialize code never runs)

Am I barking up the wrong tree? Have I missed something simple?

Thanks

 

[ClimoC]

First major problem - a'doi... is that I was never actually 'Let' -ting the object to the class, so that wasn't going to work...

But ultimately, What was a much simpler (if less elegant) solution, was simply to put a label into each userform, and at the point of creating, that label inherits the value of the key (which also goes to the basic collection).

Then, since there aren't ever more than two, it's no arduous to simply loop through them, and if the keys match of the form being closed, then remove it from the collecton.

Working solution below:

Collection Sorter:

Public Function ShowMTPViewForm(ByVal rls As Long)
Dim mForm As Object, oldmForm As Object, myMVF As Variant
SpMTPsRlsID = rls


If MTPVFormColl Is Nothing Then Set MTPVFormColl = New Collection


For Each myMVF In MTPVFormColl
        If myMVF.Release_ID = CStr(rls) Then Exit Function
Next


If MTPVFormColl.Count > 1 Then
        Set oldmForm = MTPVFormColl.Item(1)
        Unload oldmForm
        MTPVFormColl.Remove (1)
End If


Set mForm = New MTPView
mForm.Release_ID = CStr(rls)
MTPVFormColl.Add mForm, CStr(rls)
mForm.Show
End Function

and there's no more class for this one, so straight in the QueryClose event of the userform:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


If CloseMode = 0 Then


        MTPVFormColl.Remove (CStr(Me.Release_ID))


End If


End Sub

Whole thing works as intended now.