Save code-generated elements of form

Question

VBA, Excel 2010.

Given:

1. MultiPage Userform
2. Script, which makes copies of a page (based on this question)

Process:

1. Open form (1 page in multipage, screenshot 1);
2. Copy a page by running script (2 pages in multipage, screenshot 2);
3. Close form;
4. Re-open form (again only 1 page, same as on Step 1);

Script code:

Private Sub CommandButton1_Click()
Dim l As Double, r As Double
Dim ctl As Control
Dim totalPageNum As Integer

'Get number of existing pages
totalPageNum = MultiPage1.Pages.Count

'Add new one
MultiPage1.Pages.Add

'Copy elements
MultiPage1.Pages(0).Controls.Copy
MultiPage1.Pages(totalPageNum).Paste

MultiPage1.Pages(totalPageNum).Caption = "Page" & MultiPage1.Pages.Count

'Copying frame coordinates
For Each ctl In MultiPage1.Pages(0).Controls
    If TypeOf ctl Is MSForms.Frame Then
        l = ctl.Left
        r = ctl.Top
        Exit For
    End If
Next
For Each ctl In MultiPage1.Pages(totalPageNum).Controls
    If TypeOf ctl Is MSForms.Frame Then
        ctl.Left = l
        ctl.Top = r
        Exit For
    End If
Next
End Sub

Question: How to make pages to be saved on re-open of form/file (so I could get 2 pages on Step 4)

P.S. Unfortunately, I can't post pictures yet to explain the question in a better way.

Thanks for help!

Answer 1

The only way to do this is to simply hide the UserForm instead of unloading it if it is within the same session of Excel. If you have to close Excel and reopen it then there is not a way to save.

I would suggest saving the info you need to create your pages on a VeryHidden Worksheet and dynamically create the UserForm each time.