如何在UserForm Excel上加快列表框值的填充 [英] How to speed up filling of listbox values on userform excel

问题描述

我有这段代码基本上是在excel中的用户窗体上的文本框中的值更改时过滤列表框中的值

I have this code which basically filters the values in listbox as the value changes in textbox on userform in excel

Private Sub TextBox1_Change()

Dim sht As Worksheet
Dim rng1 As Range
Set sht = Sheet5
Set rng1 = sht.Range("F2:F" & sht.Range("F" & sht.Rows.Count).End(xlUp).Row)

ListBox2.ColumnCount = 7

'=====
Dim i As Long
Dim arrList As Variant

Me.ListBox2.Clear
If sht.Range("F" & sht.Rows.Count).End(xlUp).Row > 1 Then
    arrList = sht.Range("F2:L" & sht.Range("F" & sht.Rows.Count).End(xlUp).Row).Value2
    For i = LBound(arrList) To UBound(arrList)
        If InStr(1, arrList(i, 1), Trim(Me.TextBox1.Value), vbTextCompare) Then
            liste = ListBox2.ListCount
            Me.ListBox2.AddItem
            Me.ListBox2.List(liste, 0) = arrList(i, 1)
            Me.ListBox2.List(liste, 1) = arrList(i, 2)
            Me.ListBox2.List(liste, 2) = arrList(i, 3)
            Me.ListBox2.List(liste, 3) = arrList(i, 4)
            Me.ListBox2.List(liste, 4) = arrList(i, 5)
            Me.ListBox2.List(liste, 5) = arrList(i, 6)
            Me.ListBox2.List(liste, 6) = arrList(i, 7)

        End If
    Next i
End If

If Me.ListBox2.ListCount = 1 Then Me.ListBox2.Selected(0) = True

End Sub

它的工作原理非常好，除非我将值从什么更改为空(即空白)，大约需要4到5秒才能完成列表框中工作表中约8000行* 7列数据的填充，这是不可取的.有什么办法可以加快这里的速度吗?

It works perfectly except when I change the value from something to nothing i.e. blank it takes about 4 to 5 seconds to finish populating about 8k rows * 7 columns of data from the sheet in the listbox, which is not desirable. Is there any way we can speed things up here?

推荐答案

将数据放入新数组后，按新数组设置列表框.

After put datas to a new array, set listbox by new array.

Private Sub TextBox1_Change()

Dim sht As Worksheet
Dim rng1 As Range
Dim vR() As Variant

Set sht = Sheet5
Set rng1 = sht.Range("F2:F" & sht.Range("F" & sht.Rows.Count).End(xlUp).Row)

ListBox2.ColumnCount = 7

'=====
Dim i As Long
Dim arrList As Variant

Me.ListBox2.Clear
If sht.Range("F" & sht.Rows.Count).End(xlUp).Row > 1 Then
    arrList = sht.Range("F2:L" & sht.Range("F" & sht.Rows.Count).End(xlUp).Row).Value2
    For i = LBound(arrList) To UBound(arrList)
        If InStr(1, arrList(i, 1), Trim(Me.TextBox1.Value), vbTextCompare) Then
            n = n + 1
            ReDim Preserve vR(1 To 7, 1 To n)
            For j = 1 To 7
                vR(j, n) = arrList(i, j)
            next j
        End If
    Next
     Me.ListBox2.List = WorksheetFunction.Transpose(vR)
End If

If Me.ListBox2.ListCount = 1 Then Me.ListBox2.Selected(0) = True

End Sub

这篇关于如何在UserForm Excel上加快列表框值的填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！