Maybe you want to select the first or last item in a list box through the generation of code. It might be that when the sheet is selected the first item in the list box is selected. Or you may wish the last item to be selected. Whatever the case the coding is very similar. Then all you need to do is decide the trigger for the list box itself.
The first procedure will select the first item in a list box by clicking on the command button (a macro button).
Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
For i = ListBox1.ListCount - 1 To 0 Step -1
ListBox1.ListIndex = i
Next i
End Sub
The following procedure will select the last item in a list box.
Private Sub CommandButton2_Click()
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
ListBox1.ListIndex = i
Next i
End Sub
How do each of the procedures work? Well they work on the premise of counting all of the list items in the list box. In the first example a simple loop is used to start at the bottom and work up to the top of the list box 'tree'.
For i = Listbox1.Listcount - 1
Which is equal to saying
For i = 6 to 0
It is important to remember that the combo box list count starts at zero (0).
The list is 7 items in total (the length of the combo box). The Step - 1 tells the loop to take 1 off i for each iteration of the loop.
For the second procedure the opposite is called inside the loop. Start at the top and work down to the bottom then stop.
The following Excel file will show how the above procedure works.
