+ Reply to Thread
Results 1 to 2 of 2

Excel 2010 Combo box ActiveX control for the whole matrix table

  1. #1
    Registered User
    Join Date
    03-27-2012
    Location
    Queensland, Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question 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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-27-2012
    Location
    Queensland, Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Excel 2010 Combo box ActiveX control for the whole matrix table

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1