vba 从两列列表框中提取数据到工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18401423/
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
Extract data from a two columned listbox to a sheet
提问by
I have a two-columned listbox, which I've manually added entries to using
我有一个两列的列表框，我手动添加了条目以使用
.AddItem (potato)
.List(.ListCount - 1, 1) = bananaTbx.Text
When the user closes the userform all of the data is lost, so I want to have a save & exit button which saves the data to a sheet. However, it can't be saved to specific cells as the size of the list is dynamic and they will continually be adding to the master list in the sheet.
当用户关闭用户窗体时，所有数据都丢失了，所以我想要一个保存和退出按钮，将数据保存到工作表中。但是，它无法保存到特定单元格，因为列表的大小是动态的，并且它们将不断添加到工作表中的主列表中。
I tried to do something like this to extract the data:
我试图做这样的事情来提取数据：
Dim i As Integer
'loop through each row number in the list
For i = 0 To Userform1.Listbox1.ListCount - 1
'create sequence 1,1,2,2,3,3,4,4 ... to reference the current list row
j = Application.WorksheetFunction.RoundDown(i + 0.5, 0)
'create sequence 0,1,0,1,0,1,0,1 ... to reference current column in list
If Len(CStr(i / 2)) > 1 Then
k = 0
Else
k = 1
Sheets("Data").Range("A1" & ":" & "A" & i).Value = Userform1.ListBox1.List(j, k)
End If
Error:
错误：
1004 Object defined error
How can I do this properly or in a more efficient manner?
我怎样才能正确或更有效地做到这一点？
采纳答案by
I have created a simple userform to demonstrate how to Extract Values / Data from a multi-column Listbox on a Userform
我创建了一个简单的用户表单来演示如何从用户表单上的多列列表框中提取值/数据
Start by creating a simple userform with a few controls
首先创建一个带有几个控件的简单用户表单
add a new Module1to your Project and stick the below code in it
Module1向您的项目添加一个新项目并将以下代码粘贴在其中
Sub TestUserForm()
UserForm1.Show
Unload UserForm1
End Sub
in Project Explorer (VBE) Right-click on the UserForm1and hit View Code
在项目资源管理器 (VBE) 中右键单击UserForm1并点击View Code
Copy and paste the below code
复制并粘贴以下代码
Private Sub CommandButton1_Click()
With ListBox1
.AddItem TextBox1.Value
.List(.ListCount - 1, 1) = TextBox2.Value
End With
End Sub
Private Sub CommandButton2_Click()
Dim ws As Worksheet
' create a results sheets if you do not already have one
Set ws = Sheets("Results")
Dim nextAvailableRow As Long
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
nextAvailableRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Range("A" & nextAvailableRow) = ListBox1.Column(0, i)
ws.Range("B" & nextAvailableRow) = ListBox1.Column(1, i)
Next i
Me.Hide
End Sub
Create a new spreadsheet and name it Results
创建一个新的电子表格并命名 Results
Run the TestUserFormmacro
运行TestUserForm宏
Add sample data to the list and click Savebutton
将示例数据添加到列表中并单击Save按钮
