Userform Searches by Date, transferring the data to another worksheet

[Darlie247]

Hello,

I feel very confused by this because I am still learning and trying to understand VBA with Userform.

I have a worksheet where I would like specific data (Lab #) to be posted into another worksheet based on the Date entered. I will attach the an image (unfortunately my work does not allow installation of add-ins to Excel) of the worksheets, and a copy of the userform I am using.

Any help would be appreciated!

 

[MikeVol]

Hello @Darlie247.
I didn't quite understand what exactly needs to be entered on the worksheet in addition to LAB #, but I hope this will encourage you to take further independent actions.

Option Explicit
Dim SelectedRow     As Variant

Private Sub TextBox1_AfterUpdate()
    Dim cell        As Range

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim Search      As Variant
    Search = Me.TextBox1.Value
    If Search = "" Then Exit Sub

    Dim isDateSearch As Boolean
    isDateSearch = IsDate(Search)

    If isDateSearch Then
        Search = CDate(Search)

        Dim SearchNumber As Double
        SearchNumber = CDbl(Search)
    End If

    ListBox1.Clear

    Dim iFound      As Boolean
    iFound = False

    With ws

        For Each cell In .Range("C3:C" & .Cells(.Rows.Count, "C").End(xlUp).Row)

            If (isDateSearch And (cell.Value = Search Or cell.Value = SearchNumber)) Or _
                    (Not isDateSearch And cell.Value = Search) Then
                iFound = True
                AddToListBox cell, ws
            End If

        Next cell

    End With

    If Not iFound Then MsgBox "Nothing found.", vbExclamation
End Sub

Private Sub AddToListBox(cell As Range, ws As Worksheet)

    With ListBox1
        .AddItem
        .List(.ListCount - 1, 0) = ws.Cells(cell.Row, 1).Value
        .List(.ListCount - 1, 1) = ws.Cells(cell.Row, 2).Value
        .List(.ListCount - 1, 2) = ws.Cells(cell.Row, 3).Value
    End With

End Sub

Private Sub ListBox1_Click()

    If ListBox1.ListIndex <> -1 Then
        SelectedRow = Array( _
                ListBox1.List(ListBox1.ListIndex, 0), _
                ListBox1.List(ListBox1.ListIndex, 1), _
                ListBox1.List(ListBox1.ListIndex, 2))
    Else
        SelectedRow = Empty
    End If

End Sub

Private Sub CommandButton1_Click()
    Dim sh          As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet2")

    If IsEmpty(SelectedRow) Then
        MsgBox "Select a data from the list!", vbExclamation
        Exit Sub
    End If

    Dim NextRow     As Long
    NextRow = Application.Max(5, sh.Cells(sh.Rows.Count, "B").End(xlUp).Row + 1)

    sh.Cells(NextRow, 2).Value = SelectedRow(0)
    MsgBox "Data successfully added to Sheet2!", vbInformation
End Sub

Private Sub CommandButton2_Click()
    Me.TextBox1.Value = ""
    Me.ListBox1.Clear
    SelectedRow = Empty
End Sub

Private Sub CommandButton3_Click()
    SelectedRow = Empty
    Unload Me
End Sub

Good luck.