Combo Boxes

Hi everyone. In excel, is there any way to link the data in a combo box to a cell, For example if you have
a
b
c
d
e...

in the combo box, can you make it so when you click on c, it goes to the first record in Colum B starting with C? thankyou all very much, Andy

Hello Andy,

Yes you can. I assume you already know how to load the ComboBox with data. So, the next step is to examine what properties we need to use to extract information from the ComboBox. There are 3 key properties.

The Count porperty which tells us how many entries there are in the box.

The ListIndex property whichs points to the currently selected line. Let me say that again because it is important. ListIndex points to the Currently Selected Line. If nothing has been selected in the ComboBox, it returns -1. The ListIndex line numbers start at zero.

Finally, the List property. This is used to reference a ComboBox line entry by line number, and is often used with ListIndex.


Walking through the Process...

You have loaded the ComboBox with your data the letters:
a
b
c
d
e
...etc.

The user can choose a letter in 2 different ways either by clicking on the Drop Down Arrow or Typing in the letter. Fortunately, this has no impact on what we are doing.

The next step is determine what the user has chosen. This is where the ListIndex Property comes in. The ListIndex tells us the line number of the selection. If nothing was selected, ListIndex = -1. This is important. By checking the value of ListIndex we can avoid an error that results from nothing being selected.

Code to Return the Selected Entry:

LineNumber = ComboBox1.ListIndex
If LineNumber = -1 Then
<Display Error Message and Exit>
End If
With ComboBox1
Letter = .List(.ListIndex)
End With

Selecting the Worksheet Cell:

We will assume the Worksheet is named Sheet1.
Next we want to search Column B on the worksheet for the first occurrance of the letter the user has selected.

Code for Searching Column B:

LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Letter = Asc(LCase(Letter))
For I = 1 To LastRow
If Asc(LCase(Cells(I, 2).Value)) = Letter Then
ActiveSheeet.Cells(I, 2).Select
Exit Sub
End If
Next I

The Final Code Assembled...

Private Sub ComboBox1_Click()

Dim I As Long
Dim LastRow As Long
Dim LineNumber As Integer

LineNumber = ComboBox1.ListIndex

If LineNumber = -1 Then
MsgBox "No Entry Selected", vbExclamation + vbOKOnly
Exit Sub
End If

With ComboBox1
Letter = .List(.ListIndex)
End With

LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Letter = Asc(LCase(Letter))

For I = 1 To LastRow
If Asc(LCase(Cells(I, 2).Value)) = Letter Then
ActiveSheeet.Cells(I, 2).Select
Exit Sub
End If
Next I

End Sub
_________________________________________________________________

Copy and Paste this code into your ComboBox's Click Event. Be sure to change the ComboBox's name in the code if your ComboBox's name is different. I know this is long, but I wanted to make sure you have a solid understanding of how the ComboBox works. It is a very flexible and powerful programming tool. If you have any problems you can contact me here at the forum or e-mail me at [email protected].

Have fun!
Leith Ross