PDA

Vollständige Version anzeigen : Userform "on the fly"


jinx
13.08.2001, 17:53
<font size="2" face="Century Gothic">Moin @ all,

den Aufbau einer Userform(kurz: UF) kann man natürlich von Hand aus in der VBE vornehmen, man könnte aber auch bei häfiger benutzten UFs ein Vorgehen per Makro nutzen. Wichtig hierbei ist, dass in der VBE unter EXTRAS / VERWEISE der Verweis auf die <i>Microsoft Visual Basic for Applications Extensibility 5.3</i> gesetzt wird.

Folgender Code wird aus einem allgemeinen Modul aufgerufen:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Option</span> <span class="TOKEN">Explicit</span>
&nbsp;
<span class="TOKEN">Sub</span> MakeForm_mod()
<span class="REM">'Excel-L, Original von Frank Thompson, &lt;Frank.Thompson@HONDA-EU.COM&gt;</span>
<span class="REM">' Anmerkungen:</span>
<span class="REM">' - Kombofeld umbenannt</span>
<span class="REM">' - Monatsdarstellung geaendert</span>
<span class="REM">' - Datum: 27.03.2004</span>
<span class="REM">'Requires Reference to VB Extensibility</span>
<span class="TOKEN">Dim</span> wkb <span class="TOKEN">As</span> Workbook
<span class="TOKEN">Dim</span> TempForm <span class="TOKEN">As</span> Object <span class="REM">'VBComponent</span>
<span class="TOKEN">Dim</span> NewButton <span class="TOKEN">As</span> MSForms.CommandButton
<span class="TOKEN">Dim</span> NewCombo <span class="TOKEN">As</span> MSForms.ComboBox
<span class="TOKEN">Dim</span> <span class="TOKEN">Line</span> <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> vb <span class="TOKEN">As</span> Object
&nbsp;
Application.SheetsInNewWorkbook = 1
Workbooks.Add
&nbsp;
<span class="REM">' Application.VBE.MainWindow.Visible = False</span>
&nbsp;
<span class="REM">' Create the UserForm</span>
<span class="TOKEN">Set</span> TempForm = ActiveWorkbook.VBProject. _
VBComponents.Add(3) <span class="REM">'vbext_ct_MSForm</span>
<span class="TOKEN">With</span> TempForm
.Properties(&quot;Caption&quot;) = &quot;Temporary Form&quot;
.Properties(&quot;Width&quot;) = 200
.Properties(&quot;Height&quot;) = 100
.Properties(&quot;Name&quot;) = &quot;frmNEWFORM&quot;
<span class="TOKEN">End</span> <span class="TOKEN">With</span>
&nbsp;
<span class="REM">' Add a CommandButton</span>
<span class="TOKEN">Set</span> NewButton = TempForm.Designer.Controls _
.Add(&quot;forms.CommandButton.1&quot;)
<span class="TOKEN">With</span> NewButton
.Caption = &quot;Ok&quot;
.Left = 100
.Top = 40
.Name = &quot;cmdStart&quot;
<span class="TOKEN">End</span> <span class="TOKEN">With</span>
<span class="TOKEN">Set</span> NewButton = TempForm.Designer.Controls _
.Add(&quot;forms.CommandButton.1&quot;)
<span class="TOKEN">With</span> NewButton
.Caption = &quot;Abbrechen&quot;
.Left = 20
.Top = 40
.Name = &quot;cmdCancel&quot;
<span class="TOKEN">End</span> <span class="TOKEN">With</span>
&nbsp;
<span class="TOKEN">Set</span> NewCombo = TempForm.Designer.Controls _
.Add(&quot;forms.combobox.1&quot;)
<span class="TOKEN">With</span> NewCombo
.Font.Bold = <span class="TOKEN">True</span>
.Left = 10
.Top = 10
.Name = &quot;cboNewTest&quot;
<span class="TOKEN">End</span> <span class="TOKEN">With</span>
&nbsp;
Application.DisplayAlerts = <span class="TOKEN">False</span>
<span class="REM">' Add an event-hander sub for the CommandButton</span>
<span class="TOKEN">With</span> TempForm.CodeModule
<span class="TOKEN">Line</span> = .CountOfLines
.InsertLines <span class="TOKEN">Line</span> + 1, &quot;Sub cmdStart_Click()&quot;
.InsertLines <span class="TOKEN">Line</span> + 2, &quot;MsgBox Me.cboNewTest.Value&quot;
.InsertLines <span class="TOKEN">Line</span> + 3, &quot;Unload Me&quot;
.InsertLines <span class="TOKEN">Line</span> + 4, &quot;End Sub&quot;
.InsertLines <span class="TOKEN">Line</span> + 5, &quot;Sub cmdCancel_Click()&quot;
.InsertLines <span class="TOKEN">Line</span> + 6, &quot;Unload Me&quot;
.InsertLines <span class="TOKEN">Line</span> + 7, &quot;End Sub&quot;
.InsertLines <span class="TOKEN">Line</span> + 8, &quot;Private <span class="TOKEN">Sub</span> UserForm_Initialize()&quot;
.InsertLines <span class="TOKEN">Line</span> + 9, &quot;Dim i&quot;
.InsertLines <span class="TOKEN">Line</span> + 10, &quot;For i = 1 <span class="TOKEN">To</span> 12&quot;
.InsertLines <span class="TOKEN">Line</span> + 11, &quot;Me.cboNewTest.AddItem Format(DateSerial(1, i , 1), &quot; &amp; Chr(34) &amp; &quot;mmmm&quot; &amp; Chr(34) &amp; &quot;)&quot;
.InsertLines <span class="TOKEN">Line</span> + 12, &quot;Next i&quot;
.InsertLines <span class="TOKEN">Line</span> + 13, &quot;End Sub&quot;
<span class="TOKEN">End</span> <span class="TOKEN">With</span>
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Die neue Mappe ist noch nicht gespeichert.</font>

Ulukaii1983
09.06.2010, 10:32
Shazy Crit!
Das ist genau das, was ich gesucht habe! Vielen Dank (mal wieder) und bis bald. Habe das jetzt so umgesetzt:

Sub UserForm2_Machen()
Dim wkb As Workbook
Dim Form As Object
Dim NewLabel As MSForms.Label
Dim NewTextBox As MSForms.TextBox
Dim Legnummer As Integer
Dim iZeile As Integer
Dim iSpalte As Integer
Dim line As Integer
Dim i As Integer
Dim arrS() As String


ReDim arrS(0 To 12)
arrS(0) = "lblHunde"
arrS(1) = "lblPferde"
arrS(2) = "lblSchweine"
arrS(3) = "lblGänse"
arrS(4) = "lblHühner"
arrS(5) = "lblOzelots"
arrS(6) = "lblKamele"
arrS(7) = "lblKühe"
arrS(8) = "lblFüchse"
arrS(9) = "lblGoldfische"
arrS(10) = "lblWale"
arrS(11) = "lblFasane"
arrS(12) = "lblNilpferde"

Set Form = ThisWorkbook.VBProject.VBComponents("frmEreignis")
Legnummer = 1


For iZeile = 200 To 466 Step 14

iSpalte = 100
For i = 0 To UBound(arrS)
'Labels einfügen
Set NewLabel = Form.Designer.Controls.Add("Forms.Label.1")
With NewLabel
.Caption = ""
.Top = iZeile
.Left = iSpalte
.width = 32
.height = 14
If i > 2 Then .BorderStyle = fmBorderStyleSingle
.Name = arrS(i) & Legnummer
End With
If i > 2 Then
With Form.CodeModule
line = .CountOfLines
.InsertLines line + 1, "Sub " & NewLabel.Name & "_Click()"
.InsertLines line + 2, " Call FarbeÄndern(" & NewLabel.Name & ")"
.InsertLines line + 3, "End Sub"
End With
End If
iSpalte = iSpalte + 32
Next i
Legnummer = Legnummer + 1

Next iZeile


End Sub

... und generiere mir damit mit 30 schlankenCode-Zeilen 200 richtig benannte Steuerelemente. Super!