|
楼主 |
发表于 2023-11-11 09:30
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 wzs07 于 2023-11-11 10:14 编辑
有个想法,就是如何实现初始化载入全部的问题
Public what As String
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
On Error GoTo myerr
ListView1.SortKey = ColumnHeader.Index - 1
If ListView1.SortOrder = lvwDescending Then
ListView1.SortOrder = lvwAscending
Else
ListView1.SortOrder = lvwDescending
End If
ListView1.Sorted = True
myerr:
Exit Sub
End Sub
Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
Dim AA As String
Dim BB%
With Me.ListView1
sIndex = .SelectedItem.Index
AA = ListView1.ListItems(sIndex).ListSubItems(4).Text
BB = ListView1.ListItems(sIndex).ListSubItems(5).Text
'Debug.Print AA; "+"; BB; "行"
Sheets(AA).Select
Cells(BB, 1).Select
Cells.Interior.ColorIndex = 0
'Debug.Print sIndex
End With
Sheets(AA).Range(Cells(BB, 1), Cells(BB, 4)).Interior.ColorIndex = 6
End Sub
Private Sub TextBox1_Change()
ListView1.ListItems.Clear ' 清除所有内容
For Each sh In Worksheets
na = sh.Name
'Debug.Print na
'Dim what As String:
'what = TextBox1.Value
Dim rw As String: rw = Sheets(na).Range("A65536").End(xlUp).Row
Dim rng As Range
Set rng = Sheets(na).Range("A2:C" & rw) ' 关键字所在的区域
Dim dic As Object
Set dic = CreateObject("scripting.dictionary") '定义词典
For i = 2 To rw
For ii = 1 To 3
If what = "" Then what = "*"
Dim tmp As Range
Set tmp = Sheets(na).Cells(i, ii).Find(what, lookat:=xlPart, MatchCase:=False) ' 部分匹配, 不区分大小写,找到这个单元格
If Not tmp Is Nothing Then
If Not dic.exists(tmp.Row) Then ' 因为是一行多列查找,所以只要有一个单元格符合要求,要需要换行,否则listitem数据重复
dic.Add tmp.Row, ""
Set ITM = ListView1.ListItems.Add()
ITM.Text = Sheets(na).Cells(i, 1)
ITM.SubItems(1) = Sheets(na).Cells(i, 2)
ITM.SubItems(2) = Sheets(na).Cells(i, 3)
ITM.SubItems(3) = Format(Sheets(na).Cells(i, 4), "#,##0")
ITM.SubItems(4) = na
ITM.SubItems(5) = i
End If
End If
Next ii
Next i
Set dic = Nothing
Next
End Sub
Private Sub UserForm_Activate()
what = TextBox1.Value
Call TextBox1_Change
End Sub
Private Sub UserForm_Initialize()
ListView1.ColumnHeaders.Add , , "货号"
ListView1.ColumnHeaders.Add , , "货品名称", Width / 1.8
ListView1.ColumnHeaders.Add , , "分类", Width / 15, lvwColumnCenter
ListView1.ColumnHeaders.Add , , "库存数量", Width / 8, lvwColumnCenter
ListView1.ColumnHeaders.Add , , "Sheet", 0, lvwColumnCenter
ListView1.ColumnHeaders.Add , , "Row", 0, lvwColumnCenter
ListView1.View = lvwReport ' listivew的显示格式为报表格式
ListView1.Sorted = True ' listivew的排序属性为true
ListView1.SortKey = 0 ' listivew排序的索引为 '货号', 从 0 开始
ListView1.Gridlines = True ' 显示网格线
ListView1.FullRowSelect = True ' 允许整行选中
Label2.Caption = ""
Label3.Caption = ""
'设置循环,填充记录
'1
TextBox1.SetFocus
End Sub
|
|