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 ?

 

[My Aswer Is This]

To Load values into a Combobox I do it this way:
If your list of values are in column J starting in Row 1
Put this script in your Userform

Private Sub UserForm_Initialize()
'Modified  9/19/2018  4:44:59 AM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "J").End(xlUp).Row
ComboBox1.List = Range("J1:J" & Lastrow).Value
End Sub

 

[ipbr21054]

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

OK,
Please consider what i have wrote in post #20

Looking at your post #19 here is the info you require.

Column A will always have the customers names in.
The range is A6 down the page.
They are always sorted from A-Z
The sheet is called DATABASE
When i type in the combobox i would like to see the names so when i type the list will be getting smaller and i can visually see this.

In this code at my end this is done by this code.

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

So here is an example of how it works.
In the combobox i would type AND

I would then see the names,
ANDREW SUTHERLAND
ANDY SHEPARD
IAN IRELAND
JUAN FERNANDEZ

I then continue to write so now its ANDE
So the name left is JUAN FERNANDEZ

I then click his name or hit enter and now be taken to his details in my database of which is Row 83

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[ipbr21054]

I will put a video here for you.
You will need to click to play but it’s within excel so now worry for you.

 

[ipbr21054]

@MAIT

Here is a short clip showing the codes applied to each column.

I then go to my other sheet where the Combobox is situated.

You will see that when i type in the Combobox the list gets shorter as i type.

So on my sheet if i add a new name then i must update this on the sheet where the video clip first starts.
Then when i use my Combobox this new name will be there.


http://www.theremotedoctor.co.uk/forums/MAIT.mp4

 

[ipbr21054]

@MAIT
How did you get on with this video clip,did it explain how it works for you ???

 

[My Aswer Is This]

I really like helping people here on this forum but in some cases where I'm not able to understand or if things just overload my brain I just make the comment in my previous post saying this is beyond my knowledgebase.
And there are may other people here on this forum who are much better at helping then me so I hope someone else here will be able to provide a answer.

I did not look at the video. There are several cases here every week where users want us to look at videos or upload their files.

I never do any of that. If you have found formulas and Vba code to do what you want that's great. And I never use Vba code that I do not understand.

Thanks.

I do always suggest to new users of Excel Vba to start off writing very simple scripts and learn to understand the code so as they learn more they can work up to more complicated code.

 

[ipbr21054]

OK,
I understand what you are saying.

So as ive got it working fine then i need to look into how by adding a new customers name on one sheet will then update the same list of names on another sheet.