Adding Next and Previous buttons to your Combo Box or List Box

I often build forms with a combo box to select an item (by item I mean whatever the form's about- a project, a person, a product...) and subforms that show the related record(s). In a form I did recently I wanted to be able to browse from product to product. So I decided to add "Next" and "Previous" buttons.

It turns out that moving to the next or previous item in a combo box or list box is as simple as adding one to, or subtracting one from, the ListIndex of the combo box or list box.

There are only a few things to watch out for:

1. you have to SetFocus to the list box or combo box before you change it's ListIndex
2. if you try to move past the end or beginning of the list you'll get an error
3. when you want to check whether or not you're at the end of the list, remember that the ListIndex is zero-based; this means you're at the end when the ListIndex is equal to one less than the ListCount (the ListCount is the number of items in the list

The code below makes the Next and Previous buttons work. The basic code is pretty simple. There are a few niceties added, such as moving to the first item if you click the button and nothing is selected.

Then there's some fancy stuff I did for fun. The buttons have their tool tip changed to the list text for the item they'll move to- it's a kind of preview. To do that I had to write a routine to find the text for the next (or previous) item- it includes as many of the columns from the list box as are shown in the list. It does that by reading through the column widths to figure out which are not zero.

Download the
demo database

One more thing I found- when you change the ListIndex the control's AfterUpdate will fire. This works out fine, I think, it's just different than what would have happened if you had assigned a value to the control

Here's the code- give it a try!

Code:

Private Sub cmbProduct_AfterUpdate()
    SetToolTips ' update product preview tool tips
End Sub

Private Sub cmdNextItem_Click()
    ' advance to next item in combo box
    
    With Me.cmbProduct
        .SetFocus ' the combobox has to have the focus to set the ListIndex
        If IsNull(.Value) Then ' if combo box is null set it to the first item
            .ListIndex = 0
        ElseIf .ListIndex < .ListCount - 1 Then ' make sure it's not at the last item already
            .ListIndex = .ListIndex + 1 ' select the next item
        End If
    
    End With
End Sub

Private Sub cmdPreviousItem_Click()
    ' move to the previous item in the combo box

    With Me.cmbProduct
        .SetFocus
        If IsNull(.Value) Then ' if combo box is null set it to the first item
            .ListIndex = 0
        ElseIf .ListIndex > 0 Then ' make sure we're not already at the first item
            .ListIndex = .ListIndex - 1 ' select the previous item
        End If
                
    End With
    
End Sub

Private Function SetToolTips()
    ' this function sets the tool tips for the next and previous buttons to a
    ' preview of the product each will move the list to
    
    With Me.cmbProduct
        '  next item button
        If .ListIndex < .ListCount - 1 Then ' if not at the end of the list
            Me.cmdNextItem.ControlTipText = ListItemText(Me.cmbProduct, .ListIndex + 1)
        Else
            Me.cmdNextItem.ControlTipText = "(At end of list)"
        End If
        
        ' previous product button
        If .ListIndex > 0 Then ' if not now at the beginning of the list
            Me.cmdPreviousItem.ControlTipText = ListItemText(Me.cmbProduct, .ListIndex - 1)
        Else
            Me.cmdPreviousItem.ControlTipText = "(At beginning of list)"
        End If
    End With
End Function

Private Function ListItemText(ByVal ctlList As Control, ByVal lngRowNumber As Long) As String
    ' this function returns a string made up of all the visible columns for the
    ' selected row of a listbox or column box
    
    
    Dim varItems As Variant ' to hold array of column widths
    Dim lngCounter As Long ' counter for loop
    
    varItems = Split(ctlList.ColumnWidths, ";") ' split the list of column widths for reading
    For lngCounter = LBound(varItems) To UBound(varItems) ' read through the list of column widths
        If Val(CStr(varItems(lngCounter))) > 0 Then ' if columns is shown (width >0)
            If ListItemText <> vbNullString Then ' if list not empty add a separator
                ListItemText = ListItemText & " | "
            End If
            ListItemText = ListItemText & ctlList.Column(lngCounter, lngRowNumber)
        End If
    Next lngCounter
End Function

Private Sub Form_Load()
    SetToolTips ' inititalize product preview tool tips
End Sub