填充多个组合框会使VBA用户窗体变慢 [英] Populate Multiple combobox's makes VBA userform slow

问题描述

目前，我正在使用40个组合框都具有相同列表的用户表单进行工作.我的问题是填充所有组合框会使userform.show变慢.在这些组合框中填充的列表是一个非常长的列表(46542行，列表长度可以变化)，该列表有3列.

At the moment I'm working with making a userform with 40 combobox's all which have the same list. My problem is filling all those combobox's is making the userform.show slow. The list that gets populated in those combobox's is a very long list (46542 rows and list length can vary) the list is with 3 columns.

我一直在鬼混CONCATENATE整个列表，但这并没有多大改变.另外，因为在选择行号时，我需要在组合框中选择时具有要与组合框中的所有3列保持一致的值.组合框中的1而不是只在comboxbox文本字段中写入第1列，它将返回所有3列，这意味着我实际上有4列，其中第一个是CONCATENATE并隐藏在下拉列表中.

I have been fooling around with CONCATENATE the whole list but that doesn't make much of a change. Also because I need to have the value when selected in the combobox to be CONCATENATE with all 3 columns in the combobox etc. when selecting row no. 1 in the combobox instead of writing only column 1 in the comboxbox textfield it will return all 3 columns so that means I'm actually having 4 columns where the first is CONCATENATE and hidden in the dropdown.

所以我的问题是，有没有办法使这一过程更加轻松?

So my question is, is there a way to do the process more light?

下面是代码:

Private Sub UserForm_Initialize()
Set tsheet = ThisWorkbook.Sheets("Players")
Dim v As Variant, i As Long
v = tsheet.Range("A2:l" & Worksheets("Players").Cells(Rows.Count, 
1).End(xlUp).Row).Value
With Me.ComboBox1
.RowSource = ""
.ColumnCount = 4
.BoundColumn = 2
.ColumnWidths = "1;50;50;50" 'Hide first column in dropdown
For i = LBound(v) To UBound(v)
.AddItem v(i, 1) & " " & v(i, 2) & " " & v(i, 3)
.List(.ListCount - 1, 1) = v(i, 1)
.List(.ListCount - 1, 2) = v(i, 2)
.List(.ListCount - 1, 3) = v(i, 3)
Next i
End With
With Me.ComboBox2
.RowSource = ""
.ColumnCount = 4
.BoundColumn = 2
.ColumnWidths = "1;50;50;50" 'Hide first column in dropdown
For i = LBound(v) To UBound(v)
.AddItem v(i, 1) & " " & v(i, 2) & " " & v(i, 3)
.List(.ListCount - 1, 1) = v(i, 1)
.List(.ListCount - 1, 2) = v(i, 2)
.List(.ListCount - 1, 3) = v(i, 3)
Next i
End With

此代码一直持续到命中combox40

This code goes on until it hit combox40

我的旧代码运行很快，但是没有连接的列

My old code was working pretty fast but it didn't have the column that was concatenated

ComboBox3.ColumnWidths = "50;50;50"         'COLUMN WITH OF LISTBOX
ComboBox3.ColumnCount = 3                                                 
'COLUMN NUMBER OF LISTBOX
ComboBox3.List = tsheet.Range("A2:l" & 
Worksheets("Players").Cells(Rows.Count, 1).End(xlUp).Row).Value

推荐答案

代替

ComboBox3.List = tsheet.Range("A2:l" & 
Worksheets("Players").Cells(Rows.Count, 1).End(xlUp).Row).Value

使用类似这样的内容(将Arr声明为Variant):-

use something like this (declare Arr as Variant):-

Arr = tsheet.Range("A2:l" & 
Worksheets("Players").Cells(Rows.Count, 1).End(xlUp).Row).Value
' add your extra rows to the array here, followed by
ComboBox3.List = Arr

创建一个循环，而不是重复相同的代码40次.

Instead of repeating the same code 40 times, create a loop.

For i = 1 to 40
    Cbx = Me.Controls("ComboBox" & Cstr(i))
    ' then manipulate Cbx as you have done.
Next I

最后，既然您的40个组合框都相同，为什么不只用1个组合框呢?您可以逐行移动它，让用户进行选择，然后将选择转移到出现在退出"时Cbx位置的文本框.再次单击Tbx时，它会被Cbx代替，以便您可以再次访问该列表.

Finally, since your 40 comboboxes are all the same, why not make do with only 1? You can move it around from row to row, let the user make his selection and transfer that selection to a textbox that appears in the place of the Cbx on Exit. When you click on the Tbx again it is substituted by the Cbx so that you have access to the list again.

这篇关于填充多个组合框会使VBA用户窗体变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！