Daten select für Userform und einlesen

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Richi,
teste mal:

Option Explicit

Dim mlngFirstRow As Long, mlngCount As Long

Private Sub ComboBox1_Change()
    Dim dtmDate As Date
    Dim lngRow As Long
    dtmDate = CDate(ComboBox1.Value)
    mlngCount = 0
    Call ClearTextBoxes
    With ThisWorkbook.Worksheets("Quelle")
        For lngRow = 2 To .Cells(.Rows.Count, 2).End(xlUp).Row
            If .Cells(lngRow, 1).Value = dtmDate Then
                If mlngCount = 0 Then mlngFirstRow = lngRow
                mlngCount = mlngCount + 1
                Controls("TextBox" & CStr(mlngCount)).Text = .Cells(lngRow, 2).Value
                Controls("TextBox" & CStr(mlngCount + 7)).Text = .Cells(lngRow, 3).Value
            End If
            If .Cells(lngRow, 1).Value > dtmDate Then Exit For
        Next
    End With
End Sub

Private Sub CommandButton1_Click()
    Dim lngRow As Long, lngIndex As Long
    With ThisWorkbook.Worksheets("Quelle")
        For lngRow = mlngFirstRow To mlngFirstRow + mlngCount - 1
            lngIndex = lngIndex + 1
            .Cells(lngRow, 2).Value = Controls("TextBox" & CStr(lngIndex)).Text
            .Cells(lngRow, 3).Value = Controls("TextBox" & CStr(lngIndex + 7)).Text
        Next
    End With
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub TextBox14_Change()

End Sub

Private Sub UserForm_Initialize()
    Dim lngRow As Long
    With ThisWorkbook.Worksheets("Quelle")
        For lngRow = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If Application.CountIf(.Range(.Cells(2, 1), .Cells(lngRow, 1)), .Cells(lngRow, 1).Value) = 1 Then
                ComboBox1.AddItem .Cells(lngRow, 1).Text
            End If
        Next
    End With
End Sub

Private Sub ClearTextBoxes()
    Dim lngIndex As Long
    For lngIndex = 1 To 14
        Controls("TextBox" & CStr(lngIndex)).Text = vbNullString
    Next
End Sub

Gruß
Nepumuk

Hallo Richi,
kein Problem:

Private Sub CommandButton1_Click()
    Dim lngRow As Long, lngIndex As Long
    With ThisWorkbook.Worksheets("Quelle")
        For lngRow = mlngFirstRow To mlngFirstRow + mlngCount - 1
            lngIndex = lngIndex + 1
            .Cells(lngRow, 2).Value = Controls("TextBox" & CStr(lngIndex)).Text
            .Cells(lngRow, 3).Value = Controls("TextBox" & CStr(lngIndex + 7)).Text
        Next
        Do
            lngIndex = lngIndex + 1
            If lngIndex = 8 Then Exit Do
            If Controls("TextBox" & CStr(lngIndex)).Text <> vbNullString Then
                lngRow = mlngFirstRow + lngIndex - 1
                .Rows(lngRow).Insert
                .Range(.Cells(lngRow, 1), .Cells(lngRow, 3)).Value = Array( _
                    CDate(ComboBox1.Value), _
                    Controls("TextBox" & CStr(lngIndex)).Text, _
                    Controls("TextBox" & CStr(lngIndex + 7)).Text)
            Else
                Exit Do
            End If
        Loop
    End With
End Sub

Gruß
Nepumuk

Beliebteste Forumthreads (12 Monate)

Daten select für Userform und einlesen

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Daten select für Userform und einlesen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread