Excel Forum

Code: Select all

Private Sub UserForm_Initialize()
    Dim cl As Range
     
     'Load the combobox with a variety of household pets
    With Me.ListBox1
         'Clear the rowsource in case it has been set
        .RowSource = ""
         
         'Add the items from Cell A10 to the last used row in column A
    [b]    For Each cl In Worksheets("Sheet2").Range("B2:B" & _
            Worksheets("sheet2").Range("B65536").End(xlUp).Row)
[/b]            .AddItem cl.Value
        Next cl
    End With
End Sub

Code: Select all

Private Sub BTN_moveAllRight_Click_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

Code: Select all

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

Code: Select all

Private Sub UserForm_Initialize()
    Dim r As Range, i As Byte
    With Sheets("Sheet2")
        For Each r In .Range("b3", .Range("b" & .Rows.count).End(xlUp))
            Me.ListBox1.AddItem
            Me.ListBox1.List(i, 0) = r.Value
            Me.ListBox1.List(i, 1) = r.Offset(0, 1).Value
            Me.ListBox1.List(i, 2) = r.Offset(0, 2).Value
            i = i + 1
        Next r
    End With
    ListBox1.ColumnCount = 3
    ListBox1.BoundColumn = 0
End Sub