Sending plural values to another list box

When you send a value to a Listbox to another Listbox, the code is very simple. Only you have to write like

ChosenListBox.AddItem SourceListBox.List(SourceListBox.ListIndex)

But when you'd like to add plural values in the selected column of the ListBox, the code would be complicated a little.

In this case I use an array for sending values. Have a look at the sample code.

- Userform image -

Place following in the Userform module. To test this code, you need the following controls on the Userform.
  1. a ListBox named SourceListBox
  2. a ListBox named ChosenListBox
  3. a commandButton named AddButton

This sample code make writes sample data on the worksheet as follows.

- Worksheet image -

Private Sub UserForm_Initialize()
'// This code for just this test
    [A1:E10].Value = "=ADDRESS(Row(),Column())"
    SourceListBox.List = [A1:E10].Value
    SourceListBox.BoundColumn = 1
    ChosenListBox.BoundColumn = 1
    SourceListBox.ColumnCount = 5
    ChosenListBox.ColumnCount = 5
    SourceListBox.ColumnWidths = "30,30,30,30,30"
    ChosenListBox.ColumnWidths = "30,30,30,30,30"
End Sub

Private Sub AddButton_Click()
    Dim i As Integer, ub As Long
    Dim b0, b1, arr

    If SourceListBox.ListIndex = -1 Then Exit Sub
    ' See if item already exists
    For i = 0 To ChosenListBox.ListCount - 1
        If SourceListBox.Value = ChosenListBox.List(i) Then
            Exit Sub
        End If
    Next i
    b0 = SourceListBox.Column
    b1 = ChosenListBox.Column
    If Not IsNull(b1) Then
        arr = b1
        ub = UBound(b1, 2) + 1
        ReDim Preserve arr(LBound(b1, 1) To UBound(b1, 1), LBound(b1, 2) To ub)
        ReDim arr(0 To 4, 0 To 0)
        ub = 0
    End If
    For i = 0 To 4
        arr(i, ub) = b0(i, SourceListBox.ListIndex)
    ChosenListBox.Column = arr
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo