vba 将列表框项目导出到 Excel 电子表格中的命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27751041/
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
Exporting listbox items to a named range in Excel spreadsheet
提问by Anthony
I have a listbox named listBox1 on a user form in Excel VBA and a button named submit also on the form. The listbox is populated from a dynamic range starting on cell A2 of sheet 2. I want to export the contents of this listbox to a named range named dataCells on sheet 1. The code I am using currently is close but somehow exports the listbox data to cell A1 of sheet 1 instead of starting in the first cell of the named range "data cells". What am I doing wrong?
我在 Excel VBA 中的用户表单上有一个名为 listBox1 的列表框，表单上还有一个名为 submit 的按钮。列表框从工作表 2 的单元格 A2 开始的动态范围填充。我想将此列表框的内容导出到工作表 1 上名为 dataCells 的命名范围。我当前使用的代码很接近，但不知何故将列表框数据导出到工作表 1 的单元格 A1，而不是从命名范围“数据单元格”的第一个单元格开始。我究竟做错了什么？
//Code to populate listBox 1
Private Sub Userform1_initialize()
Dim dataItems as Range
Dim item as Range
worksheets("sheet2").Activate
Set dataItems = Range("A2" , Range("A2").end(xlDown))
for each item in dataItems
listbox1.addItem(item)
Next item
End sub
//Code to export the listbox contents to named range in sheet 1
Private Sub Submit_Click()
Dim dataCells as Range
Dim dataCount as Integer
Dim i as integer
worksheets("sheet1").Activate
dataCount = listBox1.ListCount - 1
set dataCells = Range("B2" , Range("B2").offset(0, dataCount))
for i = 0 to listBox1.ListCount - 1
dataCells(0, i) = listBox1.list(i , 0) // exports to A1 of sheet 1??
next i
End sub
采纳答案by Patrick Lepelletier
in my code example i want to show a fast way to populate the listbox without using additem,
在我的代码示例中，我想展示一种无需使用 additem 即可快速填充列表框的方法，
also fo exporting to sheet1, i used a VBA Array but listbox1.list also works (i added comments)
也用于导出到 sheet1，我使用了 VBA 数组，但 listbox1.list 也有效（我添加了注释）
this works :
这有效：
Option Explicit
Private Sub UserForm_Activate()
Dim i&
Dim dataItems As Range
With Me
With .ListBox1
.Clear 'not needed in userform_initialize, but i did it in a _activate sub
With Worksheets("sheet2")
Set dataItems = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp)) 'i modified this because if your code hits a blank, it will think its the last line...
End With
.List = dataItems.Value
Dim Data()
ReDim Data(1 To .ListCount, 1 To 1)
Data = .List
'this section goes to Submit_Click()
With Worksheets("sheet1")
Set dataItems = .Range("B2", .Range("B2").Offset(Me.ListBox1.ListCount - 1, 0))
End With
With dataItems
.Value = Data '.value2=me.listbox1.list , works too
End With
End With 'listbox1
End With 'me
End Sub
回答by Edward Bagby
Give this a try and let me know whether it works for you. Note that if the items are not strings, you can change dataArray to a Variant (if you are using VB). Basically, I placed the listbox items into an array and then stuffed it into a Range:
试试这个，让我知道它是否适合你。请注意，如果项目不是字符串，您可以将 dataArray 更改为 Variant（如果您使用的是 VB）。基本上，我将列表框项目放入一个数组中，然后将其填充到一个范围中：
Private Sub Submit_Click()
worksheets("sheet1").Activate
Dim i as integer
dim dataArray(listBox1.ListCount-1) as String
for i = 0 to listBox1.ListCount - 1
dataArray(i) = listBox1.list(i , 0)
next i
Range("B2").Resize(listBox1.ListCount -1,1) = dataArray
End sub
