Excel VBA - 如何从变量范围填充 ListBox 的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21997533/
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
Excel VBA - How do I populate the values of a ListBox from a variable range?
提问by Petay87
I have a list of names in Column A in a worksheet named "Email"
我在名为“电子邮件”的工作表的 A 列中有一个姓名列表
I want to populate a userform ListBox with the names Column A. However, I can't specify a fixed range as this list will grown and shrink. So how do I get the userform to populate the list with the correct number of items?
我想用名称列 A 填充用户表单 ListBox。但是，我无法指定固定范围，因为此列表会增长和缩小。那么如何让用户表单用正确数量的项目填充列表呢？
This is what I am currently trying but is not working (I'm sure it will be obvious to some people on here as to why not), I also saw another example using a simple For loop but I am unable to find the example again to show you.
这是我目前正在尝试但不起作用（我相信这里的某些人很明显为什么不这样做），我还看到了另一个使用简单 For 循环的示例，但我无法再次找到该示例给你看。
Private Sub UserForm_Initialize()
Dim rngName As Range
Dim rng1 As Range
Dim rng2 As Range
Dim ws As Worksheet
Set ws = Worksheets("Email")
Set rngName = ws.Range("A:A").Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Set rng1 = ws.Range("A1")
On Error GoTo ErrorHandle
Me.lbUsed.List = Range(rng1 & ":" & rngName).Value
ErrorHandle:
End Sub
EDIT:
编辑：
I now have the following code but it is failing to work when I load the userform:
我现在有以下代码，但是当我加载用户表单时它无法工作：
Private Sub UserForm_Initialize()
Dim rngName As Range
Dim rng1 As Range
Set rngName = Worksheets("Email").Range("A:A").Cells.Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Set rng1 = Worksheets("Email").Range("A1:" & rngName.Address)
Me.lbUsed.List = Worksheets("Email").Range(rng1).Value
End Sub
Can anyone point me in the correct direction?
任何人都可以指出我正确的方向吗？
回答by Netloh
If you want to populate your listbox with all of the items in column A (assuming that these are in a continuous range), you could do this simply by modifying you code like this:
如果你想用 A 列中的所有项目填充你的列表框（假设它们在一个连续的范围内），你可以简单地通过像这样修改你的代码来做到这一点：
Private Sub UserForm_Initialize()
Dim rngName As Range
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Email")
For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
If ws.Cells(i, 1).Value <> vbNullString Then Me.lbUsed.AddItem ws.Cells(i, 1).Value
Next i
End Sub
回答by Joe-in-VT
Point the RowSource property of the ListBox to dynamic Named Range in your spreadsheet. As you add or remove items to the range, the list will automatically pull in new items to the listbox. There's no need to write any code implement this requirement.
将 ListBox 的 RowSource 属性指向电子表格中的动态命名范围。当您向范围添加或删除项目时，列表将自动将新项目拉入列表框。无需编写任何代码来实现此要求。
