Jump to item in list based on first (and subsequent) letter (Excel 2003)

[Bob88]

Is there an option to jump through a list on a worksheet alphabetically in Excel 2003?

 

[pbornemeier]

By 'list' do you mean a sorted range of cells?
If yes:
  Is the size of the range fixed?
  Are there any blank lines in the range?
If no:
  Do you mean a listbox? or something else?

 

[Bob88]

By 'list' do you mean a sorted range of cells?

A list such as below (but mabe 200 or 300 rows).

[B]Name  Age[/B]
Alex      31
John     29
Rose     28
Sara     27

So I want to press the "R" key and jump to the first name beginning with "R" on the list.
If I then press "J" then jump to the first "J" on the list and so on.

Rgds

 

[pbornemeier]

The code to jump to the first cell that contains the desired letter is not difficult.
The difficulty comes from how to trigger that code.
Excel has several events that could be used, but they would make it hard to use the worksheet for reasons other than finding that first matching name.
Also I don't know of a way to make Excel start processing after a single letter is pressed.

With those limitations, place the following code in the codepage of the sheet that contains the list.

The code currently will examine column A if you click on any cell in column A then show an input box that asks you to enter a letter and press return to find the first item in column A that contains the letter.

Creating the list with this code active will be hard since the code will trigger each time a different cell is selected

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Columns(1)) Is Nothing Then
    
        Dim sInput As String
        
        sInput = InputBox("Enter letter to search for")
        If sInput = vbNullString Then GoTo End_Sub
        
        On Error Resume Next 'In case desired letter is not present
        Application.EnableEvents = False 'So code won't trigger itself
        With Columns(1) '1 for column A, change number for other letters (26 = Z, etc.)
            .Find(What:=sInput & "*", LookIn:=xlFormulas, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Select
        End With
        Application.EnableEvents = True
        On Error GoTo 0
        
    End If

End_Sub:

End Sub

Another option for triggering would be to enter a single character into another cell.