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]

Iff all customer have an invoice number then i wouldnt be printing an invoice now so not sure what is then going on.

Lets say 1 customer has no invoice number.
I select them from the drop down & my cells on INV are now populated.
The code takes the invoice number on sheet INV at cell L4 & puts it against the customer in column P

Does that help.

 

[mumps]

You have deleted the file that you previously uploaded. Please upload it again and post the link here. Explain in detail, referring to specific cells, rows, columns and sheets, what you are doing that generates the error.

 

[ipbr21054]

I see with that line deleted the drop down arrow is no longer there so i have now put it back.

I have just tried it again & this time it didnt happen.
I will monitor it & advise

 

[ipbr21054]

@mumps
Today i have completed the outstanding job.
This means the drop down list will be empty.

This morning sometimes when i come to the worksheet in question i get an error pop up,please see screenshot.

The error does not always happen BUT if i click in cell G13 when we know the listbox is going to be empty it also pops up again.

When i debug i am taken to the code below & the text in Red is highlighted.

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
    For Each rName In Sheets("DATABASE").Range("A6", Sheets("DATABASE").Range("A" & Rows.Count).End(xlUp))
        If rName.Offset(, 15) = "" Then
            If Val = "" Then Val = rName Else Val = Val & "," & rName
        End If
    Next rName
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Val
    End With
    Application.ScreenUpdating = True
End Sub

Here is a file to take a look at where nothing is shown in the drop down so you can then see what happens.

DOWNLOAD HERE

DATABASE is as is,you will see that an invoice number is present for the customers shown.

Go to sheet INV & click the cell G13 to see the issue.
Its only doing this as its trying to add a name to list when it doesnt need to or at least thats what i think

 

[mumps]

You will see the list contains all the customers from my sheet DATABASE but there is no need to show these customers in this list as they have an invoice number assigned to them.

Since all your customers have an invoice number, the drop down list will not be generated. This version will eliminate the error.

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
    For Each rName In Sheets("DATABASE").Range("A6", Sheets("DATABASE").Range("A" & Rows.Count).End(xlUp))
        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
    End If
    Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

That did it thanks.
I see there is no option to click the drop down arrow as it isnt there at present.

 

[mumps]

The drop down arrow is not there because:

all your customers have an invoice number

You requested that the drop down include only those customers with no invoice number.

 

[ipbr21054]

Yes.
I was just saying with that new code that is what now happens.

 

[ipbr21054]

Still have 1 issue.

For your reference,
Yesterday there was 1 customer with no invoice by his name, lets say "TOM JONES"

Later the job for TOM JONES was completed & an invoice number was assigned to him.

I then continued to add a new customer lets say "CLIFF RICHARD"

So looking at the above last night the only customer that should be in the drop down list cell G13 is CLIFF RICHARD.

Today i opened up my worksheet called INV & selected the drop down in cell G13
The only name shown is TOM JONES.
I clicked in another ce ll then back to G13
TOM jones has now gone & CLIFF RICHARD is there.

This is what i mentioned yesterday.
Im having to click G13 twice to say clear it then load the correct names

Thanks

 

[ipbr21054]

@mumps
Sorry i forgot to tag you in the above post

Thanks