Public Property Get ws() As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
End Property

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Dim i As Long
Dim arrList As Variant

If KeyCode = 13 Then
Me.ListBox1.Clear
If ws.Range("D" & ws.Rows.Count).End(xlUp).Row > 1 And Trim(Me.TextBox1.Value) <> vbNullString Then
arrList = ws.Range("D6:D" & ws.Range("D" & ws.Rows.Count).End(xlUp).Row).Value2
For i = LBound(arrList) To UBound(arrList)
    If InStr(1, arrList(i, 1), Trim(Me.TextBox1.Value), vbTextCompare) Then
        Me.ListBox1.AddItem arrList(i, 1)
   End If
Next i

End If
If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = True

End If
End Sub

Private Sub ListBox1_Click()

Dim t As Integer, Lastrow As Long

Dim cCont As control
For Each cCont In Me.Controls
If TypeName(cCont) = "Image" Then
cCont.Visible = False
End If
Next cCont

Me.TextBox1.Value = ListBox1.Value

Lastrow = ws.Cells(Rows.Count, ws.Range("tbl[Help]").Column).End(xlUp).Row
For t = 6 To Lastrow
If ws.Cells(t, ws.Range("tbl[Help]").Column).Value = (Me.TextBox1) Or _
ws.Cells(t, ws.Range("tbl[Help]").Column).Value = Val(Me.TextBox1) Then


If ws.Cells(t, ws.Range("tbl[Status]").Column).Value = "-7" Then Image4.Visible = True And Image3.Visible = False And Image2.Visible = False And Image1.Visible = False
If ws.Cells(t, ws.Range("tbl[Status]").Column).Value = "1" Then Image2.Visible = True And Image4.Visible = False And Image3.Visible = False And Image1.Visible = False
If ws.Cells(t, ws.Range("tbl[Status]").Column).Value = "2" Then Image3.Visible = True And Image4.Visible = False And Image2.Visible = False And Image1.Visible = False
If ws.Cells(t, ws.Range("tbl[Status]").Column).Value = "3" Then Image1.Visible = True And Image4.Visible = False And Image2.Visible = False And Image3.Visible = False


Me.TextBox2 = ws.Cells(t, ws.Range("tbl[Plant]").Column).Value
Me.TextBox8 = ws.Cells(t, ws.Range("tbl[Name]").Column).Value
Me.TextBox9 = ws.Cells(t, ws.Range("tbl[Code]").Column).Value
Me.ComboBox3 = ws.Cells(t, ws.Range("tbl[Color]").Column).Value
Me.ComboBox4 = ws.Cells(t, ws.Range("tbl[Status]").Column).Value
Me.ComboBox2.Value = ws.Cells(t, ws.Range("tbl[Department]").Column).Value
Me.ComboBox3.Value = ws.Cells(t, ws.Range("tbl[Stage]").Column).Value
Me.ComboBox18 = ws.Cells(t, ws.Range("tbl[Manager]").Column).Value

End If
Next t

End Sub

Private Sub CommandButton51_Click()

Dim lrCD As Long
Dim i As Long, Lastrow As Long


With ws
Lastrow = .Cells(Rows.Count, .Range("tbl[Help]").Column).End(xlUp).Row
For i = 6 To Lastrow
If .Cells(i, .Range("tbl[Help]").Column).Value = (Me.TextBox1) Or _
.Cells(i, .Range("tbl[Help]").Column).Value = Val(Me.TextBox1) Then

.Cells(i, .Range("tbl[Department]").Column).Value = Me.ComboBox2.Value
.Cells(i, .Range("tbl[Stage]").Column).Value = Me.ComboBox3.Value
.Cells(i, .Range("tbl[Color]").Column).Value = ComboBox4.Text
.Cells(i, .Range("tbl[Plant]").Column).Value = TextBox2.Text
.Cells(i, ws.Range("tbl[Manager]").Column).Value = Me.ComboBox18.Value

End If
Next i
End With

MsgBox "Success", , "Results"

End Sub