Closed Thread
Results 1 to 2 of 2

can i select from list by typing first letter of word?

  1. #1
    LISAWATKIN
    Guest

    can i select from list by typing first letter of word?

    I HAVE VERY LONG LISTS OF VEHICLES/CUSTOMERS ETC, IS THERE ANY WAY THAT I CAN
    KEY IN THE FIRST LETTER OF WHAT I WANT AND THE LIST MOVES DOWN TO THAT
    SECTION?

  2. #2
    Otto Moehrbach
    Guest

    Re: can i select from list by typing first letter of word?

    Yes, but you don't need to yell (use of all caps is yelling).
    You need a Worksheet_Change event macro and a regular macro to accomplish
    this.
    You can have a cell into which you type the first letter of the section you
    want. Hit Enter and the screen will immediately jump to put the first cell
    of that section at the top left corner of the screen.
    The macros look like this:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub
    If Target.Address(0, 0) = "B1" Then Call ShiftList(Range("B1").Value)
    End Sub

    Sub ShiftList(sLetter As String)
    Dim MyRng As Range
    Dim SearchFor As String
    Set MyRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    SearchFor = sLetter & "*"
    On Error Resume Next
    MyRng.Find(What:=SearchFor, After:=MyRng(MyRng.Count), _
    LookAt:=xlWhole).Activate
    If Err <> 0 Then
    MsgBox "The letter '" & sLetter & "' cannot be found."
    Err.Clear
    Exit Sub
    End If
    On Error GoTo 0
    With ActiveWindow
    .ScrollRow = ActiveCell.Row
    .ScrollColumn = 1
    End With
    End Sub

    The first macro needs to be placed in a sheet module for the sheet that
    holds all your data. The second macro goes into a regular module.
    In the event that the letter you typed cannot be found, a message box will
    pop up telling you so.
    I assumed that B1 is the cell into which you enter the letter you want. I
    also assumed your data is in Column A starting in A2 and going down.
    If you send me a valid email address I will send you a small file that has
    these macros placed in the proper modules.
    My email address is ottokmnop@comcast.net. Remove "nop" from this address.
    HTH Otto
    "LISAWATKIN" <LISAWATKIN@discussions.microsoft.com> wrote in message
    news:1EB003F5-FE06-4149-9FDA-9DC01D53A44C@microsoft.com...
    >I HAVE VERY LONG LISTS OF VEHICLES/CUSTOMERS ETC, IS THERE ANY WAY THAT I
    >CAN
    > KEY IN THE FIRST LETTER OF WHAT I WANT AND THE LIST MOVES DOWN TO THAT
    > SECTION?




Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1