如何将列表框项添加到VBA中的列?

本文介绍了如何将列表框项添加到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