how to create a user form programmatically
 

Hello,
is there a way to create programmatically a userform with all the
"metadata" stored into a spreadsheet table?

For example:

col1 col2 col3
-------------- ---------- ------
CommandButton; PickList1; ;
ComboBox; SelCombo; 1,2,3

where col1 is the control type, col2 the control name e col3 the
controlsource (a string with item like "1,2,3")


The following procedure, based on a John Walkenbach article
http://j-walk.com/ss/excel/tips/tip76.htm,
work fine but only for a "one-dimensional" array


Private Sub ParamUserForm_Click()
Dim STArray() As Variant
Dim Cnt As Integer, i As Integer
Dim UserChoice As Variant
....


Cnt = ActiveSheet.Range("controls").Count
ReDim STArray(1 To Cnt)
For i = 1 To Cnt
STArray(i) =
ActiveSheet.Range("controls").Range("A1").Offset(i - 1, 0)
Next i
UserChoice = DynamicInputForm(STArray)
If UserChoice = False Then
'some code
End Sub


Function DynamicInputForm(OpArray)

Dim TempForm 'As VBComponent
Dim NewUserForm As Object
Dim CtlComboBox As Object
Dim CtlCommandButton As Object
....
Dim x As Integer, i As Integer, TopPos As Integer
....



Set NewUserForm = _
Application.VBE.ActiveVBProject.VBComponents.Add(v bext_ct_MSForm)

For i = LBound(OpArray) To UBound(OpArray)
If Not IsEmpty(OpArray(i)) Then
Select Case OpArray(i)

Case "ComboBox"
Set CtlComboBox =
NewUserForm.Designer.Controls.Add("Forms.ComboBox. 1")

With CtlComboBox
.Width = 60
.Height = 15
.Left = 8
.Top = TopPos
.Tag = i
.AutoSize = True
TopPos = TopPos + 15
End with

Case "CommandButton"
Set CtlCommandButton =
NewUserForm.Designer.Controls.Add("Forms.CommandBu tton.1")

With CtlCommandButton
'some properties
End with

End Select
End If
Next i

With NewUserForm
.name = "report trend line"
End With

' Add event-hander subs for the CommandButtons
'some code

' Pass the selected option back to the calling procedure
'some code

End Function

The problem come when, adding dinamically the .name (or the .caption)
properties of the ComboBox (commandbutton) or trying to populate the
combo, I need to reference a 3D array in the loop section (since
control type, control name and control source are stored in 3
different columns).

Thank you so much

Alberto Piazza