|
- Option Explicit
- Dim Sht1 As Worksheet, Myr&, arr, i&
- Dim Itm As Object 'Dim ITM As ListItem
- '==============退出
- Private Sub Label27_Click()
- Unload Me
- End Sub
- '==============删除
- Private Sub Label11_Click()
- Dim i As Long
- i = [b10000].End(xlUp).Row
- Range("b" & i & ":e" & i).ClearContents
- MsgBox "删除成功!"
- End Sub
- '==============录入
- Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
- Dim h&, y&, r&
- On Error Resume Next
- With ListView1
- h = Range("B65536").End(3).Row + 1
- y = ActiveCell.Row '选中的活动单元格行号
- r = .SelectedItem.Index
- If OptionButton1.Value Then
- ' r = .SelectedItem.Index
- [B65536].End(3).Offset(1, 0) = 1 * .ListItems.Item(r)
- For i = 1 To 3
- [B65536].End(3).Offset(0, i) = .ListItems(r).SubItems(i)
- Next
- Else
- Cells(y, 2).Offset(0, 0) = 1 * .ListItems.Item(r)
- For i = 1 To 3
- Cells(y, 2).Offset(0, i) = .ListItems(r).SubItems(i)
- Next
- End If
- Unload Me
- End With
- End Sub
- '=================模糊查找生成列表
- Private Sub TextBox1_Change()
- Dim s As String, j&
- Call zb
- If TextBox1.Value <> "" Then
- s = TextBox1.Text
- For i = 1 To UBound(arr)
- For j = 1 To 4
- If arr(i, j) Like "*" & s & "*" Then
- Set Itm = ListView1.ListItems.Add()
- Itm.Text = arr(i, 1)
- Itm.SubItems(1) = arr(i, 2)
- Itm.SubItems(2) = arr(i, 3)
- Itm.SubItems(3) = arr(i, 4)
- End If
- Next
- Next
- Else
- For i = 1 To UBound(arr)
- Set Itm = ListView1.ListItems.Add
- Itm.Text = arr(i, 1)
- Itm.SubItems(1) = arr(i, 2)
- Itm.SubItems(2) = arr(i, 3)
- Itm.SubItems(3) = arr(i, 4)
- Next
- End If
- Label1.Caption = "共找到 " & ListView1.ListItems.Count & " 条记录"
- End Sub
- '==============初始化
- Private Sub UserForm_Initialize()
- Dim k, d
- Set d = CreateObject("Scripting.Dictionary")
- Set Sht1 = Worksheets("产品明细表")
- Myr = Sht1.[B65536].End(xlUp).Row
- arr = Sht1.Range("b3:e" & Myr)
- Call zb
- Call tgse 'zb
- For i = 1 To UBound(arr)
- d(arr(i, 2)) = ""
- Next
- k = d.keys
- With Me.ListBox1
- .ColumnCount = 1
- .ColumnWidths = "50"
- ' .ColumnHeads = True
- .BoundColumn = 1
- .List = k
- End With
- OptionButton1.Value = True
- Set d = Nothing
- End Sub
- '==============设置表格式填表头项目
- Sub zb()
- With ListView1
- .ColumnHeaders.Clear
- .ListItems.Clear
- .ColumnHeaders.Add , , "产品代码", Width / 6
- .ColumnHeaders.Add , , "产品类别", Width / 6
- .ColumnHeaders.Add , , "产品名称", Width / 7
- .ColumnHeaders.Add , , "产品型号", Width / 7
- .View = lvwReport ' listivew的显示格式为报表格式
- .FullRowSelect = True ' 允许整行选中
- .Gridlines = True ' 显示网格线
- End With
- End Sub
- '==============填表
- Sub tgse()
- For i = 1 To UBound(arr)
- If TextBox1.Text <> "" And TextBox1.Text = arr(i, 2) Then
- Set Itm = ListView1.ListItems.Add
- Itm.Text = arr(i, 1)
- Itm.SubItems(1) = arr(i, 2)
- Itm.SubItems(2) = arr(i, 3)
- Itm.SubItems(3) = arr(i, 4)
- Else
- Set Itm = ListView1.ListItems.Add
- Itm.Text = arr(i, 1)
- Itm.SubItems(1) = arr(i, 2)
- Itm.SubItems(2) = arr(i, 3)
- Itm.SubItems(3) = arr(i, 4)
- End If
- Next i
- End Sub
- '===================分类查找生成列表
- Private Sub ListBox1_Click()
- TextBox1.Text = ListBox1.List(ListBox1.ListIndex, 0)
- Call zb
- For i = 1 To UBound(arr)
- If TextBox1.Text = arr(i, 2) Then
- Set Itm = ListView1.ListItems.Add
- Itm.Text = arr(i, 1)
- Itm.SubItems(1) = arr(i, 2)
- Itm.SubItems(2) = arr(i, 3)
- Itm.SubItems(2) = arr(i, 4)
- End If
- Next i
- End Sub
复制代码
|
|