Save a userform that has been created programmatically at runtime?

[IanKR]

Is it possible to save a userform that has been created programmatically at
runtime, so that it is available for use at the next session, as if it were
created at design time?

This may sound like a strange query, but I have a userform with 133 text
boxes and 133 check boxes spread across two pages of a multipage control.
The boxes are added at runtime with the following code included in the
UserForm_Initialize event (the multipage control was added at design time -
only the text boxes and check boxes are added at runtime):

Private Sub UserForm_Initialize()

CountPagesReqd = Round(colCount / 72, 0)

CtrlLeftCoeff = 170
BoxWidth = 130
CheckLeftCoeff = 148

White = &H80000005
Pink = &H8080FF

If CountPagesReqd > 2 Then
For p = 1 To CountPagesReqd - 2
Me.Controls("MultiPage1").Pages.Add
Next p
End If

For PageOnForm = 0 To CountPagesReqd - 1
For ColOnPage = 1 To 4
For a = 1 To 18
Ind = a + (ColOnPage - 1) * 18 + PageOnForm * 72
If Ind <= colCount Then
Set MyTextBox =
Me.Controls("MultiPage1").Pages(PageOnForm).Controls.Add _
("Forms.TextBox.1", "TextBox" & Format(Ind,
"00#"), True)
With MyTextBox
.Height = 18
.Left = 12 + (ColOnPage - 1) * CtrlLeftCoeff
.Top = 12 + (a - 1) * 30
.Width = BoxWidth
.Text = arrColHead(Ind)
If arrColHidden(Ind) Then
.BackColor = Pink
Else: .BackColor = White
End If
End With
Set MyChkBox =
Me.Controls("MultiPage1").Pages(PageOnForm).Controls.Add _
("Forms.CheckBox.1", "CheckBox" & Format(Ind,
"00#"), True)
With MyChkBox
.Height = 18
.Left = CheckLeftCoeff + (ColOnPage - 1) *
CtrlLeftCoeff
.Top = 12 + (a - 1) * 30
.Width = 12
If arrColHidden(Ind) Then .Value = True
End With
End If
Next a
Next ColOnPage
Next PageOnForm

Me.Controls("MultiPage1").Pages(1).Caption = "Columns 73 - " & colCount

Me.Controls("MultiPage1").Value = 0

End Sub

The problem with this is that if I want to make use of change events
associated with the text and check boxes I have to get down and dirty with
creating change events in code, as described by Chip Pearson in
http://www.cpearson.com/excel/vbe.aspx, which I'd rather not do!

I know I could create the form manually (at design time), which would
immediately make available their change events, but having to create, name
and align 266 boxes is rather daunting (laziness). What I'm hoping is that
there is some way of generating it largely through code at runtime and then
saving it as if it were designed manually. Hope this makes sense.

Thanks in advance

 

[Harald Staff]

What happens if you programmatically just save the file after creation?

 

[IanKR]

"Harald Staff" wrote in message
What happens if you programmatically just save the file after creation?

It just reverts to how it was at design time - i.e. without the text boxes
and check boxes. That's the crux of the problem - the text and check boxes
that are created at runtime are never saved.