Alpha searching in lists.

[Poltageist]

How do i turn on the alpha search function in lists? Press the n key and you
jump to the first and siccessive entry that begins with N etc...?
--
POLTAGEIST

[Anne Troy]

That's not a feature of Microsoft Excel. The best you can do, I believe, is
a combobox. See:
Deb Dalgleish tells us how, here:
http://www.contextures.com/xlDataVal10.html
************
Anne Troy
www.OfficeArticles.com

"Poltageist" <[email protected]> wrote in message
news:[email protected]...
> How do i turn on the alpha search function in lists? Press the n key and
> you
> jump to the first and siccessive entry that begins with N etc...?
> --
> POLTAGEIST

[Piranha]

Hi,
This is not pressing a key, but close, and it works:

In this example you enter a letter in "B1" it will jump to the
location of the letter in Col "A"

Code is from Otto Moehrbach.

Put in worksheet module

Please Login or Register  to view this content.

Put in general module

Please Login or Register  to view this content.

Dave

How do i turn on the alpha search function in lists? Press the n key and you
jump to the first and siccessive entry that begins with N etc...?
--
POLTAGEIST

[Jim May]

I was unable to get this going:
All attempts ended with Message box:

The letter 'F' could not be found. (just 1 example)

??????????


"Piranha" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> This is not pressing a key, but close, and it works:
>
> In this example you enter a letter in "B1" it will jump to the
> location of the letter in Col "A"
>
> Code is from Otto Moehrbach.
>
> Put in worksheet module
>
> Code:
> --------------------
> 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
> --------------------
>
>
> Put in general module
>
> Code:
> --------------------
> 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
> --------------------
>
> Dave
>
>
> Poltageist Wrote:
>> How do i turn on the alpha search function in lists? Press the n key and
>> you
>> jump to the first and siccessive entry that begins with N etc...?
>> --
>> POLTAGEIST
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=476702
>

[Piranha]

Hi Jim,
I don't know how you are connecting to these Forums,
but if you are able to get attachments here is a sample
workbook where the code is working.
If you can't get it and still want it, you can e-mail me and
i will send it to you.
Its the same code for anyone else following this thread.
piranha488ATaolDOTcom
Change the AT and the obvious.
Dave

I was unable to get this going:
All attempts ended with Message box:

The letter 'F' could not be found. (just 1 example)

??????????


"Piranha" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> This is not pressing a key, but close, and it works:
>
> In this example you enter a letter in "B1" it will jump to the
> location of the letter in Col "A"
>
> Code is from Otto Moehrbach.
>
> Put in worksheet module
>
> Code:
> --------------------
> 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
> --------------------
>
>
> Put in general module
>
> Code:
> --------------------
> 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
> --------------------
>
> Dave
>
>
> Poltageist Wrote:
>> How do i turn on the alpha search function in lists? Press the n key and
>> you
>> jump to the first and siccessive entry that begins with N etc...?
>> --
>> POLTAGEIST
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=476702
>