What is the syntax to clear a listbox?

<listbox name>.Clear

'if clearing a userform's listbox from outside the userform
<userform name>.<listbox name>.Clear
'example
formHome.lstOutput.Clear

 

Advertisements

What is the syntax to add to a listbox?

<listbox name>.AddItem <value to be added>
'example - userform called formHome exists
formHome.lstGuests.AddItem "Tristan Smith"

 

What is the syntax to return the index of the a listbox’s selected value?

#'i' is an integer variable that holds the index
 For i = 0 To <list box name>.ListCount - 1
     If <list box name>.Selected(i) = True Then Exit For
 Next i
#example
 For i = 0 To lstGuestList.ListCount - 1
     If lstGuestList.Selected(i) = True Then Exit For
 Next i

 

What is the syntax to read rows in to a list box?

<listbox name>.AddItem Sheets("<sheet name>").Cells(<row>, <column num>).Value & ...etc
#example - reads a list of food entries from a sheet
Private Sub loadListbox()
     lstFoods.Clear
     lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

     For i = 2 To lastEntryRow 'adds each food entry row into the listbox
        lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
        lstFoods.AddItem lineDisplay 
     Next
End Sub

 

 

What is the syntax to read a sheet into a listbox?

lastEntryRow = Sheets("<sheet name>").Cells(Sheets("<sheet name>").Rows.Count, "<column letter>").End(xlUp).Row 
For i = 2 To lastEntryRow 'adds each row to listbox
       lineDisplay = Sheets("<sheet name>").Cells(i, <column you want to show in listbox>).Value & ...etc 
       <listbox name>.AddItem lineDisplay
Next
#example - reads a list of food entries from a sheet
Private Sub loadListbox()
     lstFoods.Clear
     lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

     For i = 2 To lastEntryRow 'adds each food entry row into the listbox
        lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
        lstFoods.AddItem lineDisplay 
     Next
End Sub

What is the syntax to create a tab in a listbox?

Chr(9)
#example - reads a list of food entries from a sheet
Private Sub loadListbox()
 lstFoods.Clear
 lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

 For i = 2 To lastEntryRow 'adds each food entry row into the listbox
 lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
 lstFoods.AddItem lineDisplay 
 Next
End Sub