源更改时，VBA刷新用户窗体列表框数据 [英] VBA Refresh UserForm ListBox Data when source changes

问题描述

我在用户窗体中的列表框数据遇到了问题 当我尝试更改列表框连接的源文件时，它似乎没有改变

Hi I have encountered problem with my listbox data in my Userform When I try to change the source file where my listbox connected it doesn't seems to change

一开始显示的数据不错，但是当我尝试单击RUN DATE按钮时

It was showing good data at first but when I try to click RUN DATE button

与我的范围"中的值"设置为我的排序键"不符

It doesn't go with the Value in my Range that is being set as My key for sorting

这是我用于RUN DATE按钮的代码，用于对升序和降序进行排序

Here is my code for RUN DATE BUTTON for sorting Ascending and Descending

Private Sub CommandButton1_Click()

Application.EnableEvents = False

Worksheets("combobox_value").Activate
Dim strDataRange As Range
Dim keyRange As Range
Set strDataRange = Range("I2:L4")
Set keyRange = Range("I2:I4")

If Range("M2").Value = "D" Then

strDataRange.Sort Key1:=keyRange, Order1:=xlDescending
Range("M2").Value = "A"
Else
strDataRange.Sort Key1:=keyRange, Order1:=xlAscending
Range("M2").Value = "D"
End If
Application.EnableEvents = True
End Sub

这就是我在列表框中初始化值的方式

And this is how I initialize the value in my listbox

Private Sub UserForm_Initialize()
'set ListBox properties on initialization of UserForm
Set sht = ThisWorkbook.Worksheets("combobox_value")
lastRow_combobox_column = sht.Cells(sht.Rows.Count, "I").End(xlUp).Row


With ListBox1
.ColumnCount = 4
.ColumnWidths = "100"
.ColumnHeads = False
.ControlTipText = True
End With

'Load Worksheet Range directly to a ListBox:
Dim var As Variant


var = Sheets("combobox_value").Range("I2:L" & lastRow_combobox_column)


Me.ListBox1.List = var


End Sub

有没有一种方法可以刷新我的列表框? Listbox1.refresh像这样吗?

Is there a way to refresh my listbox? Listbox1.refresh something like that?

注意:我不需要关闭我的用户窗体并再次打开以查看更新的列表框 因此，当用户窗体处于活动模式(打开)时，我可以直接更新列表框值.

Note: I don't need to close my Userform and open again to see the updated listbox so while the Userform is in active mode(Open) I can directly update the listbox value..

谢谢

推荐答案

您可以使用工作表中的数据的Named Range并分配属性<，而不是使用var并将数据从var分配给List. br> ListBox1.RowSource = "Name of the Range"

Instead of using var and assigning the data to List from var, you can use Named Range of data in the sheet and assign the property
ListBox1.RowSource = "Name of the Range"

每次您要刷新listbox时，只需在代码中使用上述赋值即可，它将起作用.如果您发现任何困难，请告诉我.

Every time you want to refresh the listbox just use the above assignment in your code and it will work. If you find any difficulty please let me know.

这篇关于源更改时，VBA刷新用户窗体列表框数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！