Adding Next And Previous Buttons to a 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.

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

Comments