Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create an user form to run macros in excel? | Excel Discussion (Misc queries) | |||
Can I create an Excel User Form without VB install on my PC? | Excel Discussion (Misc queries) | |||
Looking to create a simple user form with lookup | Excel Discussion (Misc queries) | |||
Excel VBA Programmatically delete a form? | Excel Programming | |||
How to create picklists programmatically | Excel Programming |