Forcing paged forms from memory

Question

I am writing a solution in Excel that uses several related forms of data entry. To move between the sequence of forms, the user can click the Previous or Next button. The current form is unloaded, and the new one is loaded and opened.

Sub NextForm(curForm As MSForms.UserForm, strFormName As String)
    Dim intCurPos             As Integer
    Dim strNewForm            As String
    Dim newForm               As Object

    intCurPos = WorksheetFunction.Match(strFormName, Range("SYS.formlist"), 0)
    If intCurPos = WorksheetFunction.CountA(Range("SYS.formlist")) Then
        Debug.Print "No"
    Else
        Unload curForm
        strNewForm = WorksheetFunction.Index(Range("SYS.formlist"), intCurPos + 1)
        Set newForm = VBA.UserForms.Add(strNewForm)
        newForm.Show
End Sub

The code as is allows you to add new forms to the sequence at any time through editing the "SYS.formlist" range.

One of the problems I noticed is that even after unloading the current form, it still remains in the VBA.Userforms collection. I assume this is because this code was called from this user form.

VBA.Userforms? , , , , excel .

Cheers,

Answer 1

( ) bugtussle.

curForm MSForms.Userform, . (, Set form = new formName)

, paramor curForm Variant, , . Unload , .

bugtussle!

, :

Sub NextForm(curForm As Variant, strFormName As String)
    Dim intCurPos             As Integer
    Dim strNewForm            As String
    Dim newForm               As Object

    intCurPos = WorksheetFunction.Match(strFormName, Range("SYS.formlist"), 0)
    If intCurPos = WorksheetFunction.CountA(Range("SYS.formlist")) Then
        Debug.Print "No"
    Else
        Unload curForm
        strNewForm = WorksheetFunction.Index(Range("SYS.formlist"), intCurPos + 1)
        Set newForm = VBA.UserForms.Add(strNewForm)
        newForm.Show
End Sub

Answer 2

, . - :

For i = VBA.UserForms.Count - 1 To 0 Step -1
    if VBA.UserForms(i).Name = curForm.name
        Unload VBA.UserForms(i)
    end if
Next i