|
小KS 发表于 2012-10-21 23:00
模糊查询窗体中输入的内容必须是弹出窗体的第一列的内容才行的 请用2007上版本打开
把窗体UserForm6代码改成这样的,不管是输入哪行的关键字都可筛选出来。
Option Explicit
Dim strText$, strLen%, i%, ii%, IItext$
Dim Sht1 As Worksheet
Dim ASHEET As String
Dim ITM As ListItem, n%, M%, nn%, n1%
Dim Arrb, Myr%, n2%, n3%, n4%, n5%, n6%, n7%, n8%, n9%, n10%
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ListView1_DblClick()
'取回列表值
Dim CurrIndex As Integer
nn = 3
CurrIndex = ListView1.SelectedItem.Index
If CurrIndex < 0 Then Exit Sub
ASHEET = ActiveSheet.Name
With Sheets(ASHEET)
Range(Selection.Address) = ListView1.ListItems(CurrIndex).Text
Range(Selection.Address).Offset(0, 1) = ListView1.ListItems(CurrIndex).SubItems(1)
Range(Selection.Address).Offset(0, 2) = ListView1.ListItems(CurrIndex).SubItems(2)
Range(Selection.Address).Offset(0, 3) = ListView1.ListItems(CurrIndex).SubItems(3)
Range(Selection.Address).Offset(0, 4) = ListView1.ListItems(CurrIndex).SubItems(4)
Range(Selection.Address).Offset(0, 5) = ListView1.ListItems(CurrIndex).SubItems(5)
Range(Selection.Address).Offset(0, 6) = ListView1.ListItems(CurrIndex).SubItems(6)
Range(Selection.Address).Offset(0, 7) = ListView1.ListItems(CurrIndex).SubItems(7)
' Range(Selection.Address).Offset(0, 8) = ListView1.ListItems(CurrIndex).SubItems(8)
'.Cells(9, nn) = ListView1.ListItems(CurrIndex).Text
Range("C15").Select
End With
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim ITM As ListItem
Me.Caption = "模糊查询 " & "今天是: " & Date & "--" & Format(Date, "[$-804]aaaa;@")
Call zb
Set Sht1 = Sheet3
Myr = Sht1.[A65536].End(xlUp).Row
Arrb = Sht1.Range("A2:K" & Myr)
For i = 1 To UBound(Arrb)
Set ITM = ListView1.ListItems.Add()
ITM.Text = Arrb(i, 1)
ITM.SubItems(1) = Arrb(i, 2)
ITM.SubItems(2) = Arrb(i, 3)
ITM.SubItems(3) = Arrb(i, 4)
ITM.SubItems(4) = Arrb(i, 5)
ITM.SubItems(5) = Arrb(i, 6)
ITM.SubItems(6) = Arrb(i, 7)
ITM.SubItems(7) = Arrb(i, 8)
ITM.SubItems(8) = Arrb(i, 9)
ITM.SubItems(9) = Arrb(i, 10)
Next
With ListView1
.View = lvwReport
.FullRowSelect = True
.Gridlines = True
.LabelEdit = lvwManual
End With
End Sub
Sub tb()
'填表
Set ITM = ListView1.ListItems.Add()
ITM.Text = Sht1.Cells(ii, 1)
ITM.SubItems(1) = Sht1.Cells(ii, 2)
ITM.SubItems(2) = Sht1.Cells(ii, 3)
ITM.SubItems(3) = Sht1.Cells(ii, 4)
ITM.SubItems(4) = Sht1.Cells(ii, 5)
ITM.SubItems(5) = Sht1.Cells(ii, 6)
ITM.SubItems(6) = Sht1.Cells(ii, 7)
ITM.SubItems(7) = Sht1.Cells(ii, 8)
ITM.SubItems(8) = Sht1.Cells(ii, 9)
ITM.SubItems(9) = Sht1.Cells(ii, 10)
End Sub
Sub zb()
'制表
With ListView1
.ColumnHeaders.Clear
.ListItems.Clear
.ColumnHeaders.Add 1, , "货号", ListView1.Width * 0.2
.ColumnHeaders.Add 2, , "名称", ListView1.Width * 0.3
.ColumnHeaders.Add 3, , "型号", ListView1.Width * 0.2
.ColumnHeaders.Add 4, , "单位", ListView1.Width * 0.2
.ColumnHeaders.Add 5, , "类别", ListView1.Width * 0.2
.ColumnHeaders.Add 6, , "供应商", ListView1.Width * 0.2
.ColumnHeaders.Add 7, , "库位", ListView1.Width * 0.2
.ColumnHeaders.Add 8, , "单价", ListView1.Width * 0.2
.ColumnHeaders.Add 9, , "库存数量", ListView1.Width * 0.2
.ColumnHeaders.Add 10, , "数量", ListView1.Width * 0.2
.View = lvwReport
.Gridlines = True
End With
End Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Application.ScreenUpdating = False
strText = TextBox1
strLen = Len(strText)
If strText = "" Then
Call zb
For ii = 2 To Myr
Call tb
Next ii
Exit Sub
End If
Call zb
For i = 2 To Myr
n1 = InStr(Sht1.Cells(i, 1), TextBox1.Text)
n2 = InStr(Sht1.Cells(i, 2), TextBox1.Text)
n3 = InStr(Sht1.Cells(i, 3), TextBox1.Text)
n4 = InStr(Sht1.Cells(i, 4), TextBox1.Text)
n5 = InStr(Sht1.Cells(i, 5), TextBox1.Text)
n6 = InStr(Sht1.Cells(i, 6), TextBox1.Text)
n7 = InStr(Sht1.Cells(i, 7), TextBox1.Text)
n8 = InStr(Sht1.Cells(i, 8), TextBox1.Text)
n9 = InStr(Sht1.Cells(i, 9), TextBox1.Text)
n10 = InStr(Sht1.Cells(i, 10), TextBox1.Text)
If n1 > 0 Or n2 > 0 Or n3 > 0 Or n4 > 0 Or n5 > 0 Or n6 > 0 Or n7 > 0 Or n8 > 0 Or n9 > 0 Or n10 > 0 Then
ii = i
Call tb
End If
Next i
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Activate()
Me.TextBox1.SetFocus
UserForm6.StartUpPosition = 0
UserForm6.Top = 50
UserForm6.Left = 150
End Sub |
|