davesexcel.com

Excel information

Most Popular Excel Questions

Add items to a ListBox

Posted by davesexcel on April 1, 2012 at 7:05 PM

These codes will populate ListBoxes from the controls toolbar.

Populate a ListBox with Unique Items from Column A

Private Sub CommandButton1_Click()

'Written by Dave Morrison

'November 17, 2010

'This code will populate ListBox1 with items from Column1, with no duplicates

    Dim rng As Range, c As Range, r As Range

    Dim rws As Long, y As Integer

    rws = Me.UsedRange.Columns(1).Rows.Count

    ListBox1.Clear

    Set r = Range(Cells(2, 1), Cells(rws, 1))

    For Each c In r.Cells

        y = Application.WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(c.Row, 1)), c)

        If y = 1 Then ListBox1.AddItem c

    Next c

End Sub


 

 

Populate a ListBox with 3 columns


Private Sub CommandButton2_Click()

    Dim rng As Range, c As Range, r As Range

    Dim rws As Long, y As Integer

    rws = Me.UsedRange.Columns(1).Rows.Count

    ListBox2.Clear

    Set r = Range(Cells(2, 1), Cells(rws, 3))

    ListBox2.ColumnCount = 3

    With ListBox2

        .List = r.Value

    End With

End Sub

 


Populate a ComboBox with Unique Items


Private Sub CommandButton3_Click()

'Written by Dave Morrison

'November 17, 2010

'This code will populate ComboBox1 with items from Column1, with no duplicates

    Dim rng As Range, c As Range, r As Range

    Dim rws As Long, y As Integer

    rws = Me.UsedRange.Columns(1).Rows.Count

    ComboBox1.Clear

    Set r = Range(Cells(2, 1), Cells(rws, 1))

    For Each c In r.Cells

        y = Application.WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(c.Row, 1)), c)

        If y = 1 Then ComboBox1.AddItem c.Value

    Next c

End Sub

 

 

When you make a selection in the ComboBox the listbox beside it will Populate only with the Items you selected from the ComboBox.


Private Sub ComboBox1_click()

    Dim rng As Range, c As Range, r As Range

    Dim rws As Long, y As Integer

    Dim Frng As Range, Frws As Long

    Application.ScreenUpdating = False

    rws = Cells(Rows.Count, "A").End(xlUp).Row

    Columns("A:A").AutoFilter Field:=1, Criteria1:=ComboBox1

    Set r = Range(Cells(2, 1), Cells(rws, 3))

    r.SpecialCells(xlCellTypeVisible).Copy Destination:=Range("A100")    'Sends filtered range to A1

    Columns("A:A").AutoFilter

    Frws = Cells(Rows.Count, "A").End(xlUp).Row

    Set Frng = Range(Cells(100, 1), Cells(Frws, 3))

    ListBox3.ColumnCount = 3

    With ListBox3

        .Clear

        .List = Frng.Value

    End With

    Frng.Clear

    Application.Goto Reference:="R8C1", Scroll:=True

End Sub

 

 

The last code is the most difficult, the filtered range has to be moved to a new location, that new location is then used to populate the list box.


Check out the example

http://www.davesexcel.com/ListBoxCodes.xlsm

Categories: None

Post a Comment

Oops!

Oops, you forgot something.

Oops!

The words you entered did not match the given text. Please try again.

You must be a member to comment on this page. Sign In or Register

0 Comments