Scan combo box for value

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

How can I loop through a combo box's list to determine if a particular value
is present?

Chris
 
How can I loop through a combo box's list to determine if a particular value
is present?
Click to expand...

Here's a quick example of searching through *all* the rows and columns of a
combo box:

'*****EXAMPLE START
Private Sub txtComboFind_AfterUpdate()
' Comments :
' Parameters:
' Created : 10/29/03 18:04 BMT
' Modified :
'
' --------------------------------------------------

On Error GoTo txtComboFind_AfterUpdate_ERR

Dim strCompare As String
Dim iListCount As Integer
Dim iRow As Integer 'The Row
Dim iColumn As Integer 'The Column

'The search value
strCompare = Me.txtComboFind.Value

With Me.cboNames
'Get count of "data" rows
iListCount = .ListCount - 1 + Abs(.ColumnHeads)
'Cycle through Rows
For iRow = Abs(.ColumnHeads) To iListCount
'Cycle through Columns
For iColumn = 0 To .ColumnCount - 1
'Compare values in combo's list to string variable
If .Column(iColumn, iRow) = strCompare Then
Me.txtFindStat.Value = "Yes"
'Select first Row containing value and then
' exit sub
.Value = .ItemData(iRow)
Exit Sub
Else
Me.txtFindStat.Value = "No"
End If
Next iColumn
Next iRow
End With

txtComboFind_AfterUpdate_EXIT:
Exit Sub

txtComboFind_AfterUpdate_ERR:
MsgBox "Error " & Err.Number & _
" occurred in txtComboFind_AfterUpdate: " & Err.Description
Resume txtComboFind_AfterUpdate_EXIT

End Sub
'*****EXAMPLE END

You can modify the code to search only a specific column by replacing "iColumn"
in ".Column(iColumn, iRow)" with the column number and removing the lines ...

'Cycle through Columns
For iColumn = 0 To .ColumnCount - 1

.... and ...

Next iColumn

.... along with any other lines that don't suit your purpose. Obviously, all
control names would need to be changed to match those in your project.
Hopefully, this example will help you understand the technique I used.
 
Back
Top