|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- '窗体代码如下:
- Private arr '窗体公共数组
- '==============初始化
- Private Sub UserForm_Initialize()
- Dim Myr&, i&
- With Sheets("物品库")
- Myr = .Range("b65536").End(xlUp).Row
- arr = .Range("b5:e" & Myr) '把物品装入数组
- End With
- With ListView1
- .ColumnHeaders.Clear '清除原设置
- .ListItems.Clear '清除列表
- .View = lvwReport ' listivew的显示格式为报表格式
- .FullRowSelect = True ' 允许整行选中
- .Gridlines = True ' 显示网格线
- .CheckBoxes = True ' 显示复选框
- End With
- For i = 2 To 5 '自动设置标题列(列宽按原始表大小)
- ListView1.ColumnHeaders.Add , , Sheets("物品库").Cells(5, i), Width:=Sheets("物品库").Cells(5, i).Width 'ListView1标题=第5行,宽=工作表列宽
- Next i
- For i = 1 To UBound(arr) '列表赋值 '
- With ListView1.ListItems.Add(, , arr(i, 1)) '数据显示的开始位置
- For y = 2 To UBound(arr, 2)
- .SubItems(y - 1) = arr(i, y) '这个1不能变
- Next y
- End With
- Next i
- OptionButton1.Value = True
- End Sub
- '=============================模糊查找
- Private Sub TextBox1_Change()
- Dim s$, i&, x&, j&
- s = Me.TextBox1.Value
- If Len(s) = 0 Then s = "*"
- With Me.ListView1
- .ListItems.Clear
- For i = 1 To UBound(arr)
- For j = 1 To UBound(arr, 2)
- If arr(i, j) Like s Or InStr(arr(i, j), s) > 0 Or InStr(arr(i, j), UCase(s)) > 0 Then '数字表示第二列
- With .ListItems.Add(, , arr(i, 1)) '开始的位置
- For x = 2 To UBound(arr, 2) ''''2
- .SubItems(x - 1) = arr(i, x)
- Next x
- End With
- Exit For
- End If
- Next
- Next i
- End With
- 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
- Cells(h, 2) = .ListItems.Item(r)
- For i = 1 To 3
- Cells(h, i + 2) = .ListItems(r).SubItems(i)
- Next
- Unload Me
- End If
- End With
- End Sub
- '==============================多选录入
- Sub 多选录入()
- With Me.ListView1
- If .ListItems.Count = 0 Then Exit Sub
- .ListItems(1).Ghosted = False
- r = Range("b65536").End(3).Row '最大行数
- For i = 1 To .ListItems.Count
- If .ListItems(i).Checked Then
- r = r + 1
- .ListItems(i).Checked = False
- With ActiveSheet
- .Cells(r, 2).Offset(0, 0) = ListView1.ListItems(i).Text
- For j = 1 To 3
- .Cells(r, 2).Offset(0, j) = ListView1.ListItems(i).SubItems(j)
- Next
- End With
- End If
- Next i
- End With
- Unload Me
- End Sub
- '===================双击启用多选录入
- Private Sub ListView1_DblClick()
- 多选录入
- Unload Me
- End Sub
- '==================按扭多选录
- Private Sub CommandButton1_Click()
- 多选录入
- Unload Me
- End Sub
- '==================退出
- Private Sub CommandButton2_Click()
- Unload Me
- End Sub
复制代码 |
|