Searchable Drop Down List

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
97
Hi Guys
I have found this very cool sheet that will ‘almost’ fit my needs perfectly from trump excel. I have attached a link as I am at work and cannot attach an extract of the downloaded example from here.

Searchable Drop Down List

1575621399525.png



If you start typing in the combo box the names from the list appear in the selection. The thing I like about it is if you type ‘x’ then any words with x such as mexico appear. It is basically like a google search bar.
The word you select then appears in cell B3.
What I would actually like is to be able to have the same sort of search but to be able to put into B3, B4, B5, B6 etc….
I have a large list of data and to save people scrolling down to select what they want or typing in exactly what is required the above is perfect…except it only does it in one cell.
Is this adaptable for my needs or do you have a better alternative I could use.
Thanks for any help with this.
 

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
97
Unfortunately we cannot attach files in this forum. But I guess you can download it from your computer at home?
Hi
Firstly, this is a great little code. I got this to work on my spreadsheet now and it is awsome.
I have tried to make a few tweaks for my purposes but I cant seem to get it.
Is it possible to:
  1. Have the combobox transparent. I have removed the offset so the box appears in the cell you select but you cant see what is in the cell if it already contains data.
  2. Populate the cell with a single mouse click if selecting from the list
  3. Remove the wrong input code and allow someone to type a new item in list. My list is dynamic and ever expanding as new things are added.
  4. When I select a cell in column B the combobox appears in that cell and I would like to be able to move up or down the cells without the combobox scrolling. I can do that with the up and down arrows.
Thanks very much
Rory
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Have the combobox transparent. I have removed the offset so the box appears in the cell you select but you cant see what is in the cell if it already contains data.
Sorry, I don't know how to do that.

Populate the cell with a single mouse click if selecting from the list.
Sorry, I don't know how to do that.

Remove the wrong input code and allow someone to type a new item in list.
Try replacing the Private Sub ComboBox1_KeyDown with this:

VBA Code:
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Select Case KeyCode
        Case 13 'Enter
                ActiveCell = ComboBox1.Value
                ActiveCell.Offset(, ofs).Activate
            
        Case 27, 9 'esc 'tab
                ComboBox1.Clear
                ActiveCell.Offset(, ofs).Activate
        Case Else
            'do nothing
    End Select

End Sub
My list is dynamic and ever expanding as new things are added.
The list is dynamic but only if you add the item in the "deList" sheet, not by using the combobox.

When I select a cell in column B the combobox appears in that cell and I would like to be able to move up or down the cells without the combobox scrolling. I can do that with the up and down arrows.
Sorry, I don't know how to do that.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,215
Messages
5,623,431
Members
415,974
Latest member
ZorroOP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top