Code for jump to specific cell

[ipbr21054]

Morning all,

How would this be possible please.
I have a worksheet where in column B is the customers name.
The first cell is B8 and continues down the page for however long.

My request would be to jump to a specific first letter of there christian name.
Not sure how it would work but lets say a drop down list of which has the letters of the alphabet A-Z
If i select letter S then i would be taken to the first cell for where the names with S start.

Thanks.

 

[nemmi69]

I can do something like this for you

https://sites.google.com/site/sdmfltd/stuff/FilterList.jpg

As you type the name the list reduces

 

[ipbr21054]

Hi,
That would be ok if i knew the customers full name.
I thought by just selecting say S then once taken to first cell i could then manually use my down arrow etc.

Thanks

 

[nemmi69]

Well the idea of this is that if you type "S" you get names with "S" in if you then type "M" getting "SM" you will get only names with "SM" in and so on. You can go back and forth to get the name. Then when you see the name below , click on it and it will take yo to that name.

 

[ipbr21054]

OK
Lets go for it then please

 

[nemmi69]

This is the main module code

Option Explicit


Public A2LArr1
Public A2LArrFilter
Public LastRowNo As Long
Public Tbox1Active As Boolean


Sub PrimeList()
LastRowNo = Worksheets("Sheet1").Range("G65536").End(xlUp).Row 'Just to set list of names
A2LArr1 = Application.Transpose(Worksheets("Sheet1").Range("G1:G" & LastRowNo).Value)
Worksheets("Sheet1").LBox_Add.List = A2LArr1
'clear filter text box
'stop the update for the listbox
Tbox1Active = False
Worksheets("Sheet1").TBox_Add.Text = ""
'start the update for the listbox
Tbox1Active = True
'reset index of listbox
Worksheets("Sheet1").LBox_Add.ListIndex = -1
End Sub

Code from the sheet with the Textbox (called "TBox_Add") and Listbox (called "LBox_Add")

'Button only needed if no other code primes the textbox and listbox
Private Sub CButtonPrime_Click()
PrimeList
End Sub


Private Sub LBox_Add_Click()
Dim Ploop  As Long


For Ploop = 1 To LastRowNo
    If Me.LBox_Add.Value = Worksheets("Sheet1").Range("G" & Ploop).Value Then
        Worksheets("Sheet1").Range("G" & Ploop).Select
        Exit For
    End If
Next Ploop


End Sub


Private Sub TBox_Add_Change()
A2LArrFilter = A2LArr1
'Case sensitive
'If Tbox1Active = True Then Me.LBox_Add.List = Filter(A2LArrFilter, Me.TBox_Add.Text)
'Not case sensitive
If Tbox1Active = True Then Me.LBox_Add.List = Filter(A2LArrFilter, Me.TBox_Add.Text, , vbTextCompare)
End Sub

Hope this helps. You can always get the list box to direct you to some code to do something other than just go to the name.

 

[ipbr21054]

I will take a look,many thanks