Type in field to jump to first instance of what was typed from a drop down list

[ipbr21054]

Hi,
On my worksheet called INV i have a drop down in cell G13
Cell G13 is linked to another worksshet called DATABASE & looks at the customers names in column A

When i create an invoice i use the drop down & then scroll to the customer in question & make the selection.
Once done all my other cells on the INV worksheet get populated with the data on the customers row on the DATABASE sheet.

The above works fine.

Now my list of customers on the worksheet DATABASE is getting longer i am spending longer scrolling through the list of names on INV cell G13
So i need to look at this.

Any suggestions ?
Only one i can think of is if i type T in G13 when i then click the drop down arrown i see the customer selection list start at T as opposed at the begining with A

The info for G13 drop down i have found is shown in screenshot

 

[ipbr21054]

Still get the animation of the cursur but now things work & no Dll message.
Still having to click like many times but no difference.
See attched images.

Screen shot shows 3 of the test files have invoice numbers.
Looking at next screen shot they are stil being loaded into the G13 list.

Ive clicked many cells before drop down arrow but still an issue.

Can this be done another way maybe ?

Example do away with drop down list in cell G13
In last screen shot see the empty cells row 19 & down.
Have the customers name copied here as opposed the drop down list.
User selects a name from this column of customers & the selected name is copied to cell G13

Just a thought ?

 

[mumps]

Replace the current Worksheet_SelectionChange macro in the INV sheet with this version:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rName As Range, Val As String, lRow As Long
    lRow = Sheets("DATABASE").Range("A" & Rows.Count).End(xlUp).Row
    For Each rName In Sheets("DATABASE").Range("A6:A" & lRow)
        If Val = "" Then Val = rName Else Val = Val & "," & rName
    Next rName
    If Val <> "" Then
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Val
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

So i have done the above.

On the DATABASE sheet i have added TEST 001 002 003 004
I then go to INV sheet & expect to click the G13 drop dwon arrow BUT i dont even see it.
I click in G13 anyway & see a RTE 1004 Application-Defined or Object-Defind erroe.
I debug & i see the below where the red is the issue.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rName As Range, Val As String, lRow As Long
    lRow = Sheets("DATABASE").Range("A" & Rows.Count).End(xlUp).Row
    For Each rName In Sheets("DATABASE").Range("A6:A" & lRow)
        If Val = "" Then Val = rName Else Val = Val & "," & rName
    Next rName
    If Val <> "" Then
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Val
        End With
    End If
    Application.ScreenUpdating = True
End Sub

See screenshots

 

[mumps]

Try this version:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rName As Range, Val As String, lRow As Long
    lRow = Sheets("DATABASE").Range("A" & Rows.Count).End(xlUp).Row
    For Each rName In Sheets("DATABASE").Range("A6:A" & lRow)
        If rName.Offset(, 15) = "" Then
            If Val = "" Then Val = rName Else Val = Val & "," & rName
        End If
    Next rName
    If Val <> "" Then
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Val
        End With
    Else
        Target.Validation.Delete
    End If
    Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

Its kind of better but im still having to click quite a few times to see the correct list

 

[mumps]

Try replacing the current Worksheet_Change macro in the INV sheet with this one:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
    If Not Intersect(Target, Range("L14:L18,G13:G18,G27:M51")) Is Nothing Then
        Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("G13")) Is Nothing Then
        Range("G1").Select
    End If
End Sub

 

[ipbr21054]

Still lots of clicks.

Shall we just look at another way ?

 

[mumps]

I honestly don't see why you need so many clicks. When I do some tests with your file, I only have to click once in G13 in the INV sheet to generate the drop down. Similarly, when I enter a value in the database sheet, the drop down in the INV sheet is updated automatically.

 

[ipbr21054]

Well it is strange as I sent you the file but when I try it does what it does.
???