Some problems with drop down list

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
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

New Member
Joined
Sep 3, 2018
Messages
43
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
Hi,
can you explain or show me what you mean etc for the combobox change event text
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
I also dont see a way for a Macro to clear the combobox
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,287
Try this script in your Combobox
Enter the value to search for click outside the Combobox and script will run and then clear combobox.

Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,287
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

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
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
Try this script in your Combobox
Enter the value to search for click outside the Combobox and script will run and then clear combobox.

Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,287
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
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.


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

Forum statistics

Threads
1,082,323
Messages
5,364,590
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top