Excel 2010 Combo box ActiveX control for the whole matrix table

Hi Everyone

I have just started on my Macro experience with Excel.

Currently I am working on standardising a Daily Flight Record (DFR).
The current DFR have been on MS Word, where contents have not been filled out in any particular format. This possesses many issues for our Finance team, resulting in delayed reports and errors in checking against invoices from air-traffic authorities.

My simple answer was "put it in Excel".
But as I go along to format everything to present it similar to how it was to MS Word, working towards the controls to establish it as a standardised format is proving to be difficult.

My current problem is to provide the body of the table with dropdown lists to provide controlled options.
Vertical axis lists Flight numbers
Horizontal axis lists Ports for various sectors.

Here's a madeup example:
Flight number: QFabcd Sectors flown: BNE-SYD-MEL-ADL
Flight number: QFdcba Sectors flown: ADL-MEL-SYD-BNE
Flight number: QF0bc0 Sectors flown: SYD-MEL
Flight number: QF0cb0 Sectors flown: MEL-SYD

Please refer to attached for the spreadsheet on the example.
The standard routes are displayed by vlookup when entering Flight No.
Please note that in reality the sectors flown changes from the standard routes due to various reasons.
This is reflected on the second sector of QF-0cb0 flight.

At the moment I have Data Validation to provide the dropdown list.
But, feedback on the first draft, the clerks have indicated that they are not comfortable with scrolling down on a very long list, especially for flight numbers and sectors.

From trial and error, I have worked out that Combo box from ActiveX Control is most suitable. This provides auto-fill and also narrows down the option as you type.
Eg
Type A and it displays anything that starts with A - ADL and ALS
Then Type D and it displays anything that starts with AD – ADL

So the issue now is, how to apply this to every cell in the DFR table?


Please, can anyone help?

Regards

David

Hi All

Worked it out!!!
With help....
Got the following link from this threat: http://www.excelforum.com/excel-prog...x-in-cell.html
www.contextures.com/xldataval10.html

Just had to change a few wordings in the VBA coding.

Hope this is helpful for you all.

Cheers.

David