AW: UserForm ComboBox zur Laufzeit Items unsichtbar
06.05.2014 13:48:12
Rudi
Hallo,
die Items kannst du erst hinzufügen, wenn die UF existiert.
Private Sub CommandButton1_Click()
Dim rngData As Range
Dim strArray() As String, strName As String
Dim i As Long
Set rngData = ThisWorkbook.Sheets(1).Range("J7:J13")
strName = "frmUserForm"
ReDim strArray(0 To rngData.Rows.Count - 1)
For i = 1 To rngData.Rows.Count
strArray(i - 1) = rngData.Cells(i, 1).Value
Next i
If ModuleExists(strName) Then
Call DeleteUserForm(strName)
ThisWorkbook.Save
End If
Call ErzeugeUserForm(strName, strArray)
End Sub
Sub ErzeugeUserForm(strName As String, strArray)
Dim frmCommandButton As MSForms.CommandButton
Dim frmLabel As MSForms.Label
Dim frmComboBox As MSForms.ComboBox
Dim VBComp As VBComponent
Dim intRow As Integer
Dim strText As String
Dim myUF As Object
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
With VBComp
.Properties("Caption") = "Setze Parameter des Meldetemplates"
.Properties("Width") = 400
.Properties("Height") = 150
.Properties("BackColor") = RGB(255, 255, 255)
.Properties("BorderColor") = RGB(51, 102, 255)
.Properties("ScrollBars") = fmScrollBarsVertical
.Properties("Name") = strName
End With
' Ein Bezeichnungsfeld einfügen
Set frmLabel = VBComp.Designer.Controls.Add("Forms.label.1")
With frmLabel
.Caption = "ComboBox"
.Font.Size = 12
.Font.Name = "Arial Black"
.BackColor = RGB(51, 102, 255)
.ForeColor = RGB(255, 255, 255)
.BorderColor = RGB(0, 0, 0)
.BorderStyle = fmBorderStyleSingle
.Left = 35
.Top = 10
.AutoSize = True
.WordWrap = False
End With
' Listenfeld einfügen
Set frmComboBox = VBComp.Designer.Controls.Add("Forms.ComboBox.1")
With frmComboBox
.Name = "Combobox1"
.Left = 35
.Top = 35
.Font.Size = 11
.Width = 150
.ListWidth = 250
.Style = fmStyleDropDownList
End With
' Eine Schaltfläche einfügen
Set frmCommandButton = VBComp.Designer.Controls.Add("Forms.CommandButton.1")
With frmCommandButton
.Caption = "Zeige Auswahl"
.Left = 200
.Top = 25
.AutoSize = True
.Font.Bold = True
.Font.Size = 12
End With
With VBComp.CodeModule
intRow = .CreateEventProc("Click", "CommandButton1")
.InsertLines intRow + 1, " Dim myStr as String, ctr: myStr = """""
.InsertLines intRow + 2, " For Each ctr In Me.Controls"
.InsertLines intRow + 3, " If ctr = True Then"
.InsertLines intRow + 4, " myStr = myStr & ctr.Text"
.InsertLines intRow + 5, " End If"
.InsertLines intRow + 6, " Next ctr"
.InsertLines intRow + 7, " MsgBox myStr"
.InsertLines intRow + 8, ""
.InsertLines intRow + 9, " Me.Hide"
End With
Set myUF = VBA.UserForms.Add(strName)
With myUF
With .ComboBox1
For i = LBound(strArray) To UBound(strArray) Step 1
If strArray(i) "" Then
.AddItem strArray(i)
End If
Next i
.ListIndex = 0
End With
.Show
End With
End Sub
Gruß
Rudi