Contohnya seperti berikut, saat masukan keyword di kotak pencarian makan listBox langsung menampilkan data yang di cari.
Berikut scriptnya :
Private Sub TextBox1_Change()
Me.TextBox1 = Format(StrConv(Me.TextBox1, vbLowerCase))
Dim sh As Worksheet
Set sh = Sheet5
Dim i As Long
Dim X As Long
Dim p As Long
Me.ListBox1.Clear
Dim sh As Worksheet
Set sh = Sheet5
Dim i As Long
Dim X As Long
Dim p As Long
Me.ListBox1.Clear
'for header menampilkan judul
Me.ListBox1.AddItem "BIN"
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "BARCODE"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "DATE"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "KODE"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "NAMA MATERIAL"
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "QTY"
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = "OUT DATE"
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = "NO ORDER"
Me.ListBox1.List(ListBox1.ListCount - 1, 8) = "STATUS"
Me.ListBox1.ColumnWidths = 50 & ";" & 100 & ";" & 60 & ";" & 50 & ";" & 90 & ";" & 60 & ";" & 60 & ";" & 60
Me.ListBox1.Selected(0) = True
For i = 14 To sh.Range("N" & Rows.Count).End(xlUp).Row
For X = 1 To Len(sh.Cells(i, 14))
p = Me.TextBox1.TextLength
If LCase(Mid(sh.Cells(i, 14), X, p)) = TextBox1 And Me.TextBox1 <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 14)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 3) 'BARCODE
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 4) 'TGL
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 6) 'KODE
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 7) 'NAM MAT
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 11) ' QTY
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 18) ' OUT DATE
.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 19) ' NO ORDER
.List(ListBox1.ListCount - 1, 8) = sh.Cells(i, 20) ' STATUS
End With
End If
Next X
Next i
Lcount.Caption = ListBox1.ListCount - 1
Call SUMQTY
End Sub
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "BARCODE"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "DATE"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "KODE"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "NAMA MATERIAL"
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "QTY"
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = "OUT DATE"
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = "NO ORDER"
Me.ListBox1.List(ListBox1.ListCount - 1, 8) = "STATUS"
Me.ListBox1.ColumnWidths = 50 & ";" & 100 & ";" & 60 & ";" & 50 & ";" & 90 & ";" & 60 & ";" & 60 & ";" & 60
Me.ListBox1.Selected(0) = True
For i = 14 To sh.Range("N" & Rows.Count).End(xlUp).Row
For X = 1 To Len(sh.Cells(i, 14))
p = Me.TextBox1.TextLength
If LCase(Mid(sh.Cells(i, 14), X, p)) = TextBox1 And Me.TextBox1 <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 14)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 3) 'BARCODE
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 4) 'TGL
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 6) 'KODE
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 7) 'NAM MAT
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 11) ' QTY
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 18) ' OUT DATE
.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 19) ' NO ORDER
.List(ListBox1.ListCount - 1, 8) = sh.Cells(i, 20) ' STATUS
End With
End If
Next X
Next i
Lcount.Caption = ListBox1.ListCount - 1
Call SUMQTY
End Sub

0 comments:
Post a Comment
Silahkan Untuk komentarnya