Combo Box

[APP123]

Hello everyone,

I have attached a Work Book for your reference and guidance.

I wish to have a combo box that I will place in H6 to H60, I want the combo box to display the words in the INFO sheet starting with accounts payable and ending with Misc / other,

I can do it as a normal combo box BUT, is there a way to have it so that they can select the arrow to make it use the drop down feature OR start typing the word and have it auto recognize??

in G6 and below I was hoping to have the Code auto populate depending on what is selected from the Combo box e.g. accounts payable as AP. All the Abreviations are also on the "info" sheet in the workbook

thank you very much for your time

[ajryan88]

Hi,

Have you considered using data validation for this?

Highlight H6:H60, then on the "Data" tab, select "Data Validation", and choose to allow "List". Then make the source of the list

Please Login or Register  to view this content.

Then to populate the code, use an INDEX/MATCH function. So in cell G6, the formula would be

Please Login or Register  to view this content.

which can then be dragged down to G60.

Hope this helps

EDIT: It will only auto-recognise if the same value is already present above or below the current cell

[APP123]

thanks for your reply, I did try data validation and I have come across a few issues that I probably should have mentioned in my original post,

with data validation you cannot control how big the list is and you cannot use the mouse wheel to scroll down the list. My user would prefer that either you can scroll down the list or make the list show more options, which I believe is possible through a combo box

[john55]

Hi,
just for fun, see if it helps you!

[APP123]

sorry what actually changed?

[john55]

Hi,
ajryan provided a very good solution with data validation, you said that the user would prefer to scroll down the list...so I created a small UserForm with a Textbox and a Combobox inside, you need to dbl click in your cells h2:h60 and the UserForm shows up, type/choose your data and then send it to the desire sheet (depends on what sheet are you in, see the OptionButton!)

have a nice day!

[APP123]

thank you very much john I didn't notice that when I first downloaded it, quick question, is it possible for you to make it work on all the sheets? and to make the size of the drop down list show all options? I don't know how to work the code to make it do that thanks again

p.s is it possible to make it if we hit enter or something to insert it rather than having to hit okay with the mouse??

[john55]

see if it helps you!
once the item is selected from yr combobox, it's sent to the active cell (there you dbl click to open the userform!), you can use the button (for sending data) also.

Cheers