源更改时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

一开始显示的数据很好，但是当我尝试点击运行日期"按钮时

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

它与我的 Range 中的值不匹配，该值被设置为我的排序键

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

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

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..

谢谢

推荐答案

您可以使用 代替使用 var 并将数据从 var 分配给 List工作表中数据的命名范围并分配属性
ListBox1.RowSource = "范围名称"

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屋！