List Box with Prompt Function

[christan]

Hi All,

Please find attached.

ListBox.xls

How do I make the drop down box work in the way that when I type lets say "D" in the field, it will automatically go to the first word with the letter "D".

It should also support multiple letter such as when I input "DUR", it should go to the word "Durian" in the list.

Any help is greatly appreciated.

Thanks!

Regards,
Chris

[6StringJazzer]

You can use a combobox instead of list validation, see attached. The combobox is a control rather than a cell, but it is linked to the cell where it is located so you can refer to its contents by referring to B5.

[christan]

Hi,

Thanks for the reply.

I added a control box in but it isn't performing as what you did.

Can you guide me on how to create one?

Regards,
Chris
ListBox (2).xls

[nickh1981]

Hi Christan,

You should create a combo box with active x control to get the result you want. Once created, use design mode and chose propertise. Find column ListFillRange to enter the list range. Example for your file will be $D$1:$D$42.

Hope this help

[6StringJazzer]

To elaborate, there are two families of controls: Form controls and ActiveX controls. To add a control to a worksheet, you need the Controls Toolbar visible. Right-click somewhere on the menu bar to bring up a list of available toolbars. It has been a while since I've had Excel 2003 so I don't remember the exact menu sequence. On the Controls Toolbar, click on the "Design Mode" icon. Then make sure you are selecting the ActiveX combobox, not the Forms combobox.

Then right-click the control, and select Properties from the menu. As nickh1981 says, then set the property called ListFillRange with the list of values. Then set LinkedCell with the cell where you would like the user's selected value to go.