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]

Not sure why but after a pc restart this issue has gone & works it its supposed two.

Two other issue that i have a question about please.

The Combobox is called Combobox2,how can i clear what i typed when i close down the sheet etc.
Current if i type Steve then close down the sheet Steve is still show when the sheet is next opened/used.

Secondly once i have found the person in the list by typing say Steve Strong when i then hit return i would like to be taken to Steve Strong.
The range where all the name are shown in A6 to all the way down the list.

Thanks

 

[KUYJS]

U may use macro in workbook (not in sheet) when u close workbook it will clear combobox2 ans then save and close workbook.

Use combobox change event so that when u change its value it will search its value in your list and activate the desired cell

 

[ipbr21054]

Hi,
can you explain or show me what you mean etc for the combobox change event text

 

[ipbr21054]

I also dont see a way for a Macro to clear the combobox

 

[My Aswer Is This]

Try this script in your Combobox
Enter the value to search for click outside the Combobox and script will run and then clear combobox.

Private Sub ComboBox2_LostFocus()
'Modified  9/15/2018  3:06:07 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
ComboBox2.Value = ""
End Sub

 

[My Aswer Is This]

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

 

[ipbr21054]

Try this script in your Combobox
Enter the value to search for click outside the Combobox and script will run and then clear combobox.

Private Sub ComboBox2_LostFocus()
'Modified  9/15/2018  3:06:07 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
ComboBox2.Value = ""
End Sub

Thanks,

This works well but i need the drop down box to expand down so i can see the names.
Currently if i type Alan but i cant remember surname then im stuck but having the list of Alan`s shown then thats the way to go.
Must you click outside,can it be run to enter the full name etc then hit enter ?

 

[My Aswer Is This]

In your original post you mentioned nothing about having already loaded values in the combobox.
Now I added that feature in my second posting 7

With the script in post 7 you select the value you want and the script runs you do not have to click outside the box.

 

[ipbr21054]

My code for the combobox is shown below.

It needs to extend whilst i am typing.
If i then start typing and select the customers name i then need to be shown that row on the sheet.

Trying to move away from what i have makes me then trying to decode replies.
I would like my existing code edited for the request or i will just have to leave it as is.

My brain cant cope with these issues.

Private Sub ComboBox2_Change()ComboBox2.ListFillRange = "DropDownList"
Me.ComboBox2.DropDown
End Sub