源更改时 VBA 刷新用户窗体列表框数据

VBA Refresh UserForm ListBox Data when source changes

提问by Brandon Jake Sullano

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

起初它显示了良好的数据，但是当我尝试单击 RUN DATE 按钮时

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

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

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

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

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

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

有没有办法刷新我的列表框？Listbox1.refresh 之类的？

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

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

Thanks

谢谢

采纳答案by Prasad

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

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

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

每次您想刷新时，listbox只需在代码中使用上述分配即可。如果您发现任何困难，请告诉我。

回答by CBRF23

You could add a refresh procedure, then call it in your OnClick event procedure for the button. Note, I haven't tested this code, but it should do what your original question asked.

您可以添加一个刷新过程，然后在按钮的 OnClick 事件过程中调用它。请注意，我尚未测试此代码，但它应该可以满足您最初的问题。

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

    RefreshListbox 
End Sub

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
    RefreshListbox
End Sub

Private Sub RefreshListbox()
    Me.ListBox1.Clear
    'Load Worksheet Range directly to a ListBox:
    Dim ListRange As Range
    ListRange = Sheets("combobox_value").Range("I2:L" & lastRow_combobox_column)
    Me.ListBox1.List = ListRange
End Sub