Gruppe
Dialog
Problem
Wie kann ich über VBA-Code eine UserForm mit je einer ComboBox je Tabellenblattspalte erstellen?
StandardModule: basMain
Sub NeueUserForm()
Dim frmNew
Dim cboBox As MSForms.ComboBox
Dim cmdWeiter As MSForms.CommandButton
Dim intCounter As Integer, intTop As Integer, intRow As Integer
Dim strCode As String
Application.VBE.MainWindow.Visible = False
On Error Resume Next
Set frmNew = ThisWorkbook.VBProject.VBComponents("frmTageslisten")
If Err = 0 Then GoTo ErrorHandler
On Error GoTo 0
Set frmNew = ThisWorkbook.VBProject.VBComponents.Add(3)
intTop = 5
For intCounter = 1 To 31
If intCounter = 17 Then intTop = 5
Set cboBox = frmNew.designer.Controls.Add("forms.ComboBox.1")
With cboBox
If intCounter < 17 Then .Left = 5 Else .Left = 160
.Top = intTop
.Width = 150
.RowSource = Range( _
Cells(2, intCounter), Cells(10, intCounter)).Address
.ControlSource = Cells(intCounter, 32)
.ListIndex = 0
End With
intTop = intTop + 20
Next intCounter
Set cmdWeiter = frmNew.designer.Controls.Add("forms.CommandButton.1")
With cmdWeiter
.Top = intTop + 30
.Left = 5
.Width = 305
.Height = 30
.Caption = "Weiter"
.Name = "cmdWeiter"
End With
With frmNew
.properties("Width") = 320
.properties("Height") = 17 * 20 + 50
.properties("Caption") = "Tageslisten"
.properties("Name") = "frmTageslisten"
End With
strCode = "Private Sub cmdWeiter_Click" & vbLf
strCode = strCode & " Unload Me" & vbLf
strCode = strCode & "End Sub"
ThisWorkbook.VBProject.VBComponents("frmTageslisten") _
.CodeModule.AddFromString strCode
ErrorHandler:
VBA.UserForms.Add(frmNew.Name).Show
End Sub