如何启用 Excel vba 组合框自动更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28631892/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to enable Excel vba combobox to update automatically
提问by HOA
I am creating a user form that deals with inventory for a school project.
我正在创建一个处理学校项目库存的用户表单。
I created a combo box to delete selected items but i have no idea how to update the list after deleting a certain item. I am using the following code to perform the delete and refresh functionionality.
我创建了一个组合框来删除所选项目，但我不知道如何在删除某个项目后更新列表。我正在使用以下代码来执行删除和刷新功能。
Private Sub cmdDelete_Click()
Dim row As Long
row = cbPCodeIM.ListIndex + 2
Sheets("Inventory").Select
Sheets("Inventory".Range("A" & row & ":E" & row).Select
Selection.Delete shift:=x1Up
'the following line does not seem to work when uncommented
'cbPCodeIM.ListFillRange = "=Inventory!$A:index(Inventory!$A:$A;CountA(Inventory!$A:$A))"
MsgBox "Item has been removed.", vbOKOnly
End Sub
采纳答案by user3561813
In my opinion, it's best to create a separate method for filling the comboboxthat you can then call from the Initializeevent, and also whenever the comboboxshould be updated.
在我看来，最好创建一个单独的方法来填充combobox，然后您可以从Initialize事件中调用该方法，也可以在combobox应该更新时调用。
The code behind the userformwould look like the following, with code to capture the cmdDelete-Click()event, the Userform_Initialize()event, and finally the custom method.
背后的代码userform如下所示，其中包含捕获cmdDelete-Click()事件的代码、Userform_Initialize()事件，最后是自定义方法。
Let me know of any questions.
让我知道任何问题。
Private Sub cmdDelete_Click()
Dim nRow As Long
nRow = Me.cbPCodeIM.ListIndex + 2
Worksheets("Inventory").Rows(nRow).Delete 'NOTE, this will delete the entire row
Fill_My_Combo Me.cbPCodeIM
End Sub
Private Sub UserForm_Initialize()
Fill_My_Combo Me.cbPCodeIM
End Sub
Private Sub Fill_My_Combo(cbo As ComboBox)
Dim wsInventory As Worksheet
Dim nLastRow As Long
Dim i as Long
Set wsInventory = Worksheets("Inventory")
nLastRow = wsInventory.Cells(Rows.Count, 1).End(xlUp).Row ' Finds last row in Column 1
cbo.clear
For i = 2 To nLastRow 'start at row 2, assuming a header
cbo.AddItem wsInventory.Cells(i, 1)
Next i
End Sub
