如何获取列表框以将其结果返回到Excel VBA中的下一个空行 [英] How to get a listbox to return it's results into the next empty row in excel vba
本文介绍了如何获取列表框以将其结果返回到Excel VBA中的下一个空行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个包含40个项目的列表框.我想要实现的是让每个被选择返回的内容在其单独的行中返回，而在两者之间缺少一行，因此为.Row + 2.
I have a listbox with 40 items. What I would like to achieve is to get each one that is chosen to be returned each on its separate line, with a line missing between, hence the .Row + 2.
示例代码为:
Dim LastRow As Long
Dim lItem As Long
LastRow = Worksheets(3).Range("B" & Rows.Count).End(xlUp).Row + 2
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) And lItem = 0 Then
Worksheets(3).Range("B" & LastRow).Value = "Example1abc"
ElseIf ListBox1.Selected(lItem) And lItem = 1 Then
Worksheets(3).Range("B" & LastRow).Value = "Example2def"
ElseIf ListBox1.Selected(lItem) And lItem = 2 Then
Worksheets(3).Range("B" & LastRow).Value = "Example3ghi"
ElseIf ListBox1.Selected(lItem) And lItem = 3 Then
Worksheets(3).Range("B" & LastRow).Value = "Example4jkl"
ElseIf ListBox1.Selected(lItem) And lItem = 4 Then
Worksheets(3).Range("B" & LastRow).Value = "Example5mno"
ElseIf ListBox1.Selected(lItem) And lItem = 5 Then
Worksheets(3).Range("B" & LastRow).Value = "Example6pqr"
ElseIf ListBox1.Selected(lItem) And lItem = 6 Then
Worksheets(3).Range("B" & LastRow).Value = "Example7stu"
ElseIf ListBox1.Selected(lItem) And lItem = 7 Then
Worksheets(3).Range("B" & LastRow).Value = "Example8vwx"
ElseIf ListBox1.Selected(lItem) And lItem = 8 Then
Worksheets(3).Range("B" & LastRow).Value = "Example9yza"
ElseIf ListBox1.Selected(lItem) And lItem = 9 Then
Worksheets(3).Range("B" & LastRow).Value = "Example10bcd"
ElseIf ListBox1.Selected(lItem) And lItem = 10 Then
Worksheets(3).Range("B" & LastRow).Value = "Example11efg"
ElseIf ListBox1.Selected(lItem) And lItem = 11 Then
Worksheets(3).Range("B" & LastRow).Value = "Example12hij"
ElseIf ListBox1.Selected(lItem) And lItem = 12 Then
Worksheets(3).Range("B" & LastRow).Value = "Example13klm"
ElseIf ListBox1.Selected(lItem) And lItem = 13 Then
Worksheets(3).Range("B" & LastRow).Value = "Example14nop"
ElseIf ListBox1.Selected(lItem) And lItem = 14 Then
Worksheets(3).Range("B" & LastRow).Value = "Example15qrs"
ElseIf ListBox1.Selected(lItem) And lItem = 15 Then
Worksheets(3).Range("B" & LastRow).Value = "Example16tuv"
ElseIf ListBox1.Selected(lItem) And lItem = 16 Then
Worksheets(3).Range("B" & LastRow).Value = "Example17wxy"
ElseIf ListBox1.Selected(lItem) And lItem = 17 Then
Worksheets(3).Range("B" & LastRow).Value = "Example18zab"
ElseIf ListBox1.Selected(lItem) And lItem = 18 Then
Worksheets(3).Range("B" & LastRow).Value = "Example19cde"
ElseIf ListBox1.Selected(lItem) And lItem = 19 Then
Worksheets(3).Range("B" & LastRow).Value = "Example20fgh"
ElseIf ListBox1.Selected(lItem) And lItem = 20 Then
Worksheets(3).Range("B" & LastRow).Value = "Example21ijk"
ElseIf ListBox1.Selected(lItem) And lItem = 21 Then
Worksheets(3).Range("B" & LastRow).Value = "Example22lmn"
ElseIf ListBox1.Selected(lItem) And lItem = 22 Then
Worksheets(3).Range("B" & LastRow).Value = "Example23opq"
ElseIf ListBox1.Selected(lItem) And lItem = 23 Then
Worksheets(3).Range("B" & LastRow).Value = "Example24rst"
ElseIf ListBox1.Selected(lItem) And lItem = 24 Then
Worksheets(3).Range("B" & LastRow).Value = "Example25uvw"
ElseIf ListBox1.Selected(lItem) And lItem = 25 Then
Worksheets(3).Range("B" & LastRow).Value = "Example26xyz"
ElseIf ListBox1.Selected(lItem) And lItem = 26 Then
Worksheets(3).Range("B" & LastRow).Value = "Example27abc"
ElseIf ListBox1.Selected(lItem) And lItem = 27 Then
Worksheets(3).Range("B" & LastRow).Value = "Example28def"
ElseIf ListBox1.Selected(lItem) And lItem = 28 Then
Worksheets(3).Range("B" & LastRow).Value = "Example29ghi"
ElseIf ListBox1.Selected(lItem) And lItem = 29 Then
Worksheets(3).Range("B" & LastRow).Value = "Example30jkl"
ElseIf ListBox1.Selected(lItem) And lItem = 30 Then
Worksheets(3).Range("B" & LastRow).Value = "Example31mno"
ElseIf ListBox1.Selected(lItem) And lItem = 31 Then
Worksheets(3).Range("B" & LastRow).Value = "Example32pqr"
ElseIf ListBox1.Selected(lItem) And lItem = 32 Then
Worksheets(3).Range("B" & LastRow).Value = "Example33stu"
ElseIf ListBox1.Selected(lItem) And lItem = 33 Then
Worksheets(3).Range("B" & LastRow).Value = "Example34vwx"
ElseIf ListBox1.Selected(lItem) And lItem = 34 Then
Worksheets(3).Range("B" & LastRow).Value = "Example35yza"
ElseIf ListBox1.Selected(lItem) And lItem = 35 Then
Worksheets(3).Range("B" & LastRow).Value = "Example36bcd"
ElseIf ListBox1.Selected(lItem) And lItem = 36 Then
Worksheets(3).Range("B" & LastRow).Value = "Example37efg"
ElseIf ListBox1.Selected(lItem) And lItem = 37 Then
Worksheets(3).Range("B" & LastRow).Value = "Example38hij"
ElseIf ListBox1.Selected(lItem) And lItem = 38 Then
Worksheets(3).Range("B" & LastRow).Value = "Example39klm"
ElseIf ListBox1.Selected(lItem) And lItem = 39 Then
Worksheets(3).Range("B" & LastRow).Value = "Example40nop"
End If
Next
这显然只会返回选择的最后一个，而不返回其他.
在不使代码变得非常长的情况下，我需要输入什么，如以下示例所示?
This clearly only returns the last one that is chosen and not the others.
What do I need to put in without making the code extremely long, like the following example??
Dim LastRow As Long
Dim lItem As Long
LastRow = Worksheets(3).Range("B" & Rows.Count).End(xlUp).Row + 2
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) And lItem = 0 Then
Worksheets(3).Range("B" & LastRow).Value = "Example1abc"
ElseIf ListBox1.Selected(lItem) And lItem = 1 Then
Worksheets(3).Range("B" & LastRow).Value = "Example2def"
ElseIf ListBox1.Selected(lItem) And lItem = 2 Then
Worksheets(3).Range("B" & LastRow).Value = "Example3ghi"
ElseIf ListBox1.Selected(lItem) And lItem = 3 Then
Worksheets(3).Range("B" & LastRow).Value = "Example4jkl"
''''''''''''''''''''''''''''''''''''''''
''' etc as above... '''
''' then for more than one selected: '''
''''''''''''''''''''''''''''''''''''''''
ElseIf ListBox1.Selected(lItem) And lItem = 1 & 2 Then
Worksheets(3).Range("B" & LastRow).Value = "Example1abc"
Worksheets(3).Range("B" & LastRow +2).Value = "Example2def"
ElseIf ListBox1.Selected(lItem) And lItem = 1 & 3 Then
Worksheets(3).Range("B" & LastRow).Value = "Example1abc"
Worksheets(3).Range("B" & LastRow +2).Value = "Example3ghi"
'''''''''''''''''
''' etcetc... '''
'''''''''''''''''
End If
Next
这显然是一种非常冗长而乏味的方法，尤其是对于40!(8.1591528324789773434561126959612e + 47)选项而言！
我希望这很清楚.
This would obviously be a very long and tedious way of doing it, especially with 40! (8.1591528324789773434561126959612e+47) options!
I hope this is clear.
我已经搜索过，但在其他地方似乎找不到类似的情况.
I have searched, but can't seem to find a similar situation elsewhere.
推荐答案
如果"Example1abc"，"Example2def"，....与列表本身中的值不同，则可以如下所示:
if "Example1abc" , "Example2def", .... are different then the values in the list themselves then you can go like follows:
Option Explicit
Private Sub CommandButton1_Click()
Dim lItem As Long
Dim sResultArr As Variant
sResultArr = Array("Example1abc", "Example2def", "Example3ghi", "Example4jkl", ...) '<--| hard code the values to be associated with list items
With Worksheets(3)
For lItem = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(lItem) Then .Cells(.Rows.Count, "E").End(xlUp).Offset(2) = sResultArr(lItem)
Next
End With
End Sub
否则，即"Example1abc"，"Example2def"，....是列表中的值，那么您可以像下面这样进行操作:
otherwise, i.e. "Example1abc" , "Example2def", .... are the values in the list themselves then you can go like follows:
Private Sub CommandButton1_Click()
Dim lItem As Long
With Worksheets(3)
For lItem = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(lItem) Then .Cells(.Rows.Count, "E").End(xlUp).Offset(2) = Me.ListBox1.List(lItem)
Next
End With
End Sub
这篇关于如何获取列表框以将其结果返回到Excel VBA中的下一个空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
