如何将列表框项添加到VBA中的列? [英] How to add listbox items to a column in 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

这篇关于如何将列表框项添加到VBA中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！