How to find item in listbox in VBA

1 2 3 4 5 6 7 8 9 10 11 12

Private Sub CommandButton1_Click() Dim i As Long If TextBox1.Text = "" Then Exit Sub For i = 0 To ListBox1.ListCount - 1 ''(1) If ListBox1.List(i) = TextBox1.Text Then ''(2) ListBox1.ListIndex = i ''(3) MsgBox "found in index " & ListBox1.ListIndex Exit Sub End If Next i MsgBox "not found" End Sub

Download File

How to get multi selected item in Listbox VBA

Set Listbox property, MultiSelect : fmMultiSelectMulti

1 2 3 4 5 6 7 8 9

Private Sub CommandButton1_Click() Dim i As Long, msg As String For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then msg = msg & ListBox1.List(i) & vbCrLf End If Next i MsgBox msg End Sub

Download File

Selected Item in Listbox (VBA)

1 2 3 4 5

Private Sub CommandButton1_Click() MsgBox ListBox1.Value End Sub

Get last item from listbox:

1 2 3 4 5 6 7 8 9 10

Sub LastItem() Dim Msg As String Msg = "Last item:" & vbNewLine Msg = Msg & ListBox1.List(ListBox1.ListCount - 1) & vbNewLine MsgBox Msg End Sub

Select last item of list box:

1 2 3 4 5 6 7 8 9 10 11 12

Sub LastItem() Dim Msg As String Msg = "Last item:" & vbNewLine Msg = Msg & ListBox1.List(ListBox1.ListCount - 1) & vbNewLine ListBox1.Selected(ListBox1.ListCount - 1) = True MsgBox Msg End Sub

Download File

How to Add Item to ListBox in VBA (3)

Now, we will add item from Range(A1:A1000000) to listbox

1 2 3 4 5 6

Private Sub CommandButton1_Click() ListBox1.List = Range("A1:A1000000").Value MsgBox "Count of items:" & ListBox1.ListCount End Sub

Now, we will try to chek 'how fast the process is"

1 2 3 4 5 6 7 8 9 10 11 12

Private Declare Function GetTickCount Lib "Kernel32" () As Long Private Sub CommandButton1_Click() Dim S As Long S = GetTickCount ListBox1.List = Range("A1:A1000000").Value MsgBox "Count of items:" & ListBox1.ListCount & vbCrLf & _ (GetTickCount - S) / 1000 & "seconds" End Sub

In my PC, it takes abut 2.203 seconds for 1000000 data.

Download File

Before (1)
Before (2)

How to Add Item to ListBox in VBA (2)

Add item from RowSource

Items want to add

With Listbox properties : RowSource

When we change data in Range("A1:A10"),

1 2 3 4 5

Private Sub CommandButton1_Click() Range("A5") = "DataChange" 'change data End Sub

and data in listbox also change

1 2 3 4 5

Private Sub CommandButton2_Click() ListBox1.RowSource = "Sheet1!B1:B10" 'change data End Sub

Download File

Before (1)
Next (3)

How to Add Item to ListBox in VBA (1)

CommandButon1 click:

1 2 3 4 5

Private Sub CommandButton1_Click() ListBox1.AddItem "Sample Data" 'add item to list box End Sub

1 2 3 4 5 6 7 8

Private Sub CommandButton1_Click() Dim i As Integer For i = 1 To 10 ListBox1.AddItem "Sample Data" & i 'add item to list box Next i End Sub

Add data from cells

1 2 3 4 5 6 7 8

Private Sub CommandButton1_Click() Dim i As Long For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row ListBox1.AddItem Cells(i, 1) 'add item to list box Next i End Sub

Download File

Next (2)
Next (3)