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