Some problems with drop down list

[ipbr21054]

Hi.
I have my code on a sheet called INFO
I have the drop down box on a sheet called DATABASE

In the name manager i have named it DATABASELIST & put the cell reference etc.
When i click on the properties for the combobox2 i have entered under LinkedCell INFO!CG2
Under ListFillRange i have put DATABASELIST

I then save and click on the drop down arrow of the combobox and i see all the names.
I start to type in the box but see nothing ?
I make the box bolank by deleting the currently typed in letters but now there are no names at all ?
If i go back to the properties the ListFillRange is now blank where 2 minutes ago it had DATABASELIST saved there ?

 

[ipbr21054]

You started the post in post 1 talking about one issue but then seemed to say that issue had been resolved.
Then in post 2 you had a completely different question it seemed to me.
So my precious post was in reference to your question in Post 2
Normally when we use Comboboxes it because we want to choose a value from the combobox not enter a value to the combobox.
My previous post was to show you how to do what you asked.


The script below shows you how to load all the values from Column A into the Combobox and then when you choose a value from the combobox the script will run.


The Commandbutton1 script loads the Combobox2 with the values in Column A

Private Sub ComboBox2_Click()
'Modified  9/15/2018  4:15:35 PM  EDT
Dim SearchRange As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Range("A6:A" & Lastrow).Find(ComboBox2.Value, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
End Sub
Private Sub CommandButton1_Click()
'Modified  9/15/2018  4:15:35 PM  EDT
ComboBox2.Clear
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ComboBox2.List = Range("A6:A" & Lastrow).Value
End Sub

@MAIT Can the code you advised have an edit where when i start to type the combobox drops down so i can see the list.

I tried to implement the following but didnt work.

Me.ComboBox2.DropDown


Thanks

 

[ipbr21054]

@MAIT
I have tried to use your code as shown in post 7 but the search part will not work for me.

My command button 1 has this code.

Private Sub CommandButton1_Click()'Modified  9/15/2018  4:15:35 PM  EDT
ComboBox2.Clear
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ComboBox2.List = Range("A6:A" & LastRow).Value
End Sub

My ComboBox2 has this code.

Private Sub ComboBox2_Click()'Modified  9/15/2018  4:15:35 PM  EDT
Dim SearchRange As Range
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Range("A6:A" & LastRow).Find(ComboBox2.Value, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
End Sub

If i click the ComboBox2 drop down arrow the box is empty.
So i then click command button 1 to have all the names in column A entered into the ComboBox2

Clicking the drop down box all the names are present.

So i decide to type TOM, then i click on the drop down arrow expecting to see a list of possible customers with TOM within their name.

What i actually see is no change & just all the same names as before i started typing.

Where did i go wrong or do you see an error ?

Many thanks.

 

[My Aswer Is This]

I know of no way to type Bill into a combobox and have the list jump to Bill and allow you to select Which Bill you want.

You can scroll down with the arrow or scroll down using the Key Board arrow keys Up Down.

Sorry I have not provided a answer you want. I have never used what you refer to as:

Me.ComboBox2.DropDown​

 

[ipbr21054]

@MAIS
I found a code on the internet which allows me to type in a combobox,it then drops down so you can see what is happening & as you type the results in the list narrow down.
I am not sure if i am allowed to put the link here to show you,maybe you or another member reading this can advise ??

Basically there is a list of customers names in one column.

In the next column this code would be used =--ISNUMBER(IFERROR(SEARCH($B$3,E3,1),""))

In the next column this code would be used =IF(F3=1,COUNTIF($F$3:F3,1),"")

In the last column this code would be used =IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")

You then create a dynamic named range.

Then some vba for the combobox.

Now i have done this mentioned above and all works fine.

BUT

My list of customer names are on one sheet called DATABASE in column A,also my combobox is on this sheet.

The code mentioned above would be on another sheet out of the way called INFO.

The issue i had was getting the list updated on the INFO sheet when i add a new customer to the sheet DATABASE.

Not only did you need to add the name to the list,but also extending the range code row for the ISNUMBER, IF, IFERROR, mentioned above.

 

[My Aswer Is This]

Well normally when using Vba you do not need formulas in cells.
But if your happy and things are working for you that's great.

You said:

Then some vba for the combobox.​

Show me this code maybe I can learn something new.

I would need to go back and reread your original post to see what your original question was.

 

[ipbr21054]

Hi,
Here is the page in question, https://trumpexcel.com/excel-drop-down-list-with-search-suggestions

I can advise my cell/sheet info as obviously different from above.

Thanks

 

[My Aswer Is This]

I never like to click on links.

I thought you said you already had the needed code for the Combobox and I just wanted to see the code you put in your combobox to get it to do what you wanted.

 

[ipbr21054]

This is the code for the Combobox.

Private Sub ComboBox1_Change()ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub

The database has a dynamic named range called DropDownList.

So in the properties of the combobox under ListFillRnage i have put DropDownList

This works fine.
BUT
Like i say when i add another customers name to my list on sheet called DATABASE it then needs to add that name to the list on my database so its kept up to date.
Then the columns to this name also need to be updated in respect of the code being incremated by 1 row.

 

[My Aswer Is This]

When I look in the properties window for the listbox I see no ListFillRange.

I know how to load values into a Combobox.
And this is not how I do it.

I'm not sure where we are going with this question.

You earlier said you wanted to enter John into the combobox and then if you had:

A list of names in your combobox like:

Alan
Bob
John
Peter
Betty
Carl
Mike
John James
Betty Smith
Mark
John Alan


You wanted all the John's to pop to the top of the list.

And then I don't remember what you wanted.
I said I did not know how to do this.

But it now seems your saying you know how to do this but want to know how to add more values to the range of values in the Combobox as a range expands.

I want to see how you get all the Johns to jump to the top of the list.

Now if the list is sorted by name this would work.

If you want to see how I load values into a Combobox when the range changes.
Tell me where is your list of values now that you want loaded into the combobox

Say something like this J1 to last row with data in column J

 

[ipbr21054]

Not being rude here but if you dont click the link then you wont be able to see the code working.
Clicking the link is the same as clicking the link in your email for coming back to this page.

I can share a file for you to see but do you then not downloaded from my sites sharing link ?