Active X Combo Box - VBA - List on different sheet - AutoComplete - Option to Hide Rows

[MixedUpExcel]

Hi All,

Although this is my first post on here.. Thank You to the Forum as I've been following and learning on here for over a year now.

As per my subject title, I have quite a few things I'm trying to work on and they are all linked to the Active X Combo Box.

I'm running Windows 7 and Excel 2007 (at home) 2010 (at work)

I have a project which is completed but have had suggestions to make it more user friendly and I am stuck on the Combo-box.

I currently have a Combo-box (Form Control) which produces a drop-down list (data is on a different data sheet)
When the user selects the option from the list, it populates a cell on the data sheet with a number (the position of the selection in the list) - this uses Cell Link under the Format Control>Control options.

I have formula's on Sheet 1 which use that number (populated on the data sheet) as part of a vlookup - this is important.. I need to keep the numbers in that cell on the second data sheet as they are not just for the vlookups - I have VBA code which check if that cell changes and unhide or hide rows based on the number on Sheet 1

This is the bit I'm stuck on and if there is a similar answer within this forum, please point me in the right direction.

• I need to be able to change the size and style of the font in the Combo-box (looking at using Active X Combo-box for this)
• I need to make it auto-complete (similar to Google search) where the first letters input aren't necessarily the first two in the words in the list eg. UNITED STATES - the user inputs ST and it still shows this as an option.
• I need to have the options which are remaining if the user inputs ST to all be showing and narrow down until just the one is left if the user continues to type in the name or to allow them to select the one they want (ie. with mouse or scroll down and press enter)

I have found a few different sites / youtube videos which guide me some of the way but then I can't make them work together.. so wondered if anyone had been able to do the above?

These are the Resources I've looked at:
http://www.contextures.com/xlDataVal14.html
Create an Excel Drop Down List with Search Suggestions

Is all the info on those 2 sites and I'm just not quite getting it?

One of the issues I was coming across is that I couldn't get them to post the list number to my cell on Data Sheet 2 and the other issue was that obviously when you start to type, it will populate something. That something may be the first option but not necessarily the correct option - at this point, that first option may end up populating the number cell and I only want some action taking when the user has pressed 'TAB' or 'ENTER'

Thank you in advance for any help you might be able to provide here.

Simon

 

[MixedUpExcel]

In case the above was a bit too much to ask for.. what's the best way to break it down so it's clearer what I need to do?

I read the rules and they said to put everything in the first post so I wasn't drip feeding the questions...

Thanks.

Simon