|
楼主 |
发表于 2018-2-19 11:09
|
显示全部楼层
- Private Sub CommandButton1_Click()
- On Error Resume Next
- If OptionButton1 = True Then lh = 3 '列号
- If OptionButton2 = True Then lh = 4
- If OptionButton3 = True Then lh = 5
- If lh = "" Then MsgBox "请选择查询类型!": GoTo 100
- a = Sheets("备件清单").Columns(2).Find("*", , , , , searchdirection:=xlPrevious).Row
- arr = Sheets("备件清单").Range("a2:e" & a)
- ReDim arr1(1 To UBound(arr), 1 To 5)
- arr1(1, 1) = "位置"
- arr1(1, 2) = "编号"
- arr1(1, 3) = "名称"
- arr1(1, 4) = "型号"
- arr1(1, 5) = "供货商"
- t = UCase(TextBox1)
- For i = 3 To UBound(arr)
- If InStr(UCase(arr(i, lh)), UCase(TextBox1)) Then
- n = n + 1
- For x = 1 To 5
- arr1(n + 1, x) = Sheets("备件清单").Cells(i, x)
- Next
- End If
- Next
- With ListBox1
- .ColumnCount = 5
- .TextAlign = 2
- .List = arr1
- .ColumnWidths = "45;40;110;60;100"
- End With
- If n = "" Then
- MsgBox "没匹配成功,请查正后输入!"
- Else
- Me.ListBox2.Visible = False
- Me.ListBox1.Visible = True
- End If
- 100:
- End Sub
- Private Sub ListBox1_Click()
- End Sub
- Private Sub ListBox2_Click()
- Me.ListBox2.Visible = False
- TextBox1 = ListBox2
- End Sub
- Private Sub OptionButton1_Click()
- TextBox1 = ""
- End Sub
- Private Sub OptionButton2_Click()
- TextBox1 = ""
- End Sub
- Private Sub OptionButton3_Click()
- TextBox1 = ""
- End Sub
- Private Sub TextBox1_Change()
- On Error Resume Next
- If OptionButton1 = True Then lh = 3 '列号
- If OptionButton2 = True Then lh = 4
- If OptionButton3 = True Then lh = 5
- If lh = "" Then MsgBox "请选择查询类型!": GoTo 100
- arr = Sheets("备件清单").Range("a2:e" & Sheets("备件清单").[e65536].End(xlUp).Row)
- ReDim arr1(1 To UBound(arr), 1 To 5)
- ReDim arr1(1 To UBound(arr), 1 To 5)
- For i = 3 To UBound(arr)
- If InStr(UCase(arr(i, lh)), UCase(TextBox1)) Then
- n = n + 1
- arr1(n, 1) = Sheets("备件清单").Cells(i, lh)
- End If
- Next
- If TextBox1.Value <> "" And n <> "" Then
- Me.ListBox2.Visible = True
- Else
- Me.ListBox2.Visible = False
- End If
- Me.ListBox2.List = arr1
- 100:
- End Sub
- Private Sub UserForm_Click()
- End Sub
- Private Sub UserForm_Initialize()
- Me.ListBox2.Visible = False
- End Sub
复制代码 |
|