Userform Searches by Date, transferring the data to another worksheet

Darlie247

New Member
Joined
Apr 2, 2022
Messages
14
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!

List. PNG.PNG
Microarray. PNG.PNG
Userform.PNG
 
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.
VBA Code:
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
Userform Searches by Date, transferring the data to another worksheet_Form.png
Userform Searches by Date, transferring the data to another worksheet_Before.png
Userform Searches by Date, transferring the data to another worksheet_OnRun.png
Userform Searches by Date, transferring the data to another worksheet_After.png
Good luck.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top