如何将列表框项添加到VBA中的列?
问题描述:
我有2个列表框,如图所示.因此,在完成列表框2并单击``确定''后,应该将列表框2中的数据添加到范围A2向下.使用.AddItem
将数据填充到列表框1中
.
I have 2 list boxes like in image.So when the listbox 2 is finalised and I click 'OK', the data in listbox2 should be added to range A2 to down. Data is populated in listbox 1 using .AddItem
.
我尝试过这样:
Option Explicit
Private Sub BTN_moveAllLeft_Click()
Dim iCtr As Long
For iCtr = 0 To Me.ListBox2.ListCount - 1
Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
Next iCtr
Me.ListBox2.Clear
End Sub
Private Sub BTN_moveAllRight_Click()
Dim iCtr As Long
For iCtr = 0 To Me.ListBox1.ListCount - 1
Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
Next iCtr
Me.ListBox1.Clear
End Sub
Private Sub BTN_MoveSelectedLeft_Click()
Dim iCtr As Long
For iCtr = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(iCtr) = True Then
Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
End If
Next iCtr
For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1
If Me.ListBox2.Selected(iCtr) = True Then
Me.ListBox2.RemoveItem iCtr
End If
Next iCtr
End Sub
Private Sub BTN_MoveSelectedRight_Click()
Dim iCtr As Long
For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
End If
Next iCtr
For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox1.RemoveItem iCtr
End If
Next iCtr
End Sub
Private Sub cmdOK_Click()
Dim lngLastRow As Long
Dim lngCol As Long
Dim lngIndex As Long
lngLastRow = Range("D" & Rows.Count).End(xlUp).Row + 1
lngCol = 4
For lngIndex = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lngIndex) Then
Cells(lngLastRow, lngCol) = ListBox2.List(lngIndex)
lngCol = lngCol + 1
End If
Next
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 1 To 10
.AddItem "This is a test" & iCtr
Next iCtr
End With
Me.ListBox1.MultiSelect = fmMultiSelectMulti
Me.ListBox2.MultiSelect = fmMultiSelectMulti
End Sub
答
如果您要填充范围A2中Listbox2中的数据,并在按下按钮OK
时向下填充,请尝试以下操作:
If you want to populate data from Listbox2 in Range A2 and down once button OK
is pressed, try this:
Private Sub cmdOK_Click()
Dim lngLastRow As Long
Dim lngCol As Long
Dim lngIndex As Long
For lngIndex = 0 To ListBox2.ListCount - 1
Cells(lngIndex + 2, 1).Value = ListBox2.List(lngIndex)
Next
End Sub