• Bound combo-boxes

    Author
    Topic
    #470415

    Hi,

    (WinXP, O2003 – UK SP3)

    For starters – though I’m aware this would be ideal for a database, that is not an option //

    Got a workbook with 3 main sheets:
    #1. Companies (unique company names – currently about 50 rows in 12 columns, loaded into arrCompanyName)
    #2. Transactions (unique transactions but lots of values are repeated – currently about 2000+ rows in 10 columns)
    #3. Keywords (one column per keyword, each column has a defined name, eg. “dnCurrencies”, “dnCountries”, “dnCategories”)

    In sheet 1, for each company, I’ve also got registered its main currency, related country etc.

    I have a form with 4 comboboxes (1-4)

    #1 is the main combo holding company names (eg combo1 is fed by combo1.list = arrCompanyName,1 )
    #2 holds currencies (eg combo2 is fed by combo2.list = dnCurrencies)
    #3 holds countries …combo3.list = dnCountries
    etc.

    Each combobox is fed with a single column “defined name” from the keywords sheet (except combo1).
    Combo1 is fed from the company master sheet containing approx 50 rows in 12 columns (each company name is unique).

    If I feed all controls from the arrCompanyName and uses “.BoundColumn”, I get same currency represented multiple times in the dropdown (50 companies gives 50 currencies – though there may only be 5 different ones).

    So how do I??
    Make sure that the currencies are only shown once in the currencies list. Countries are only shown once in the countries list etc…

    Basically what I want is to remove duplicates from the linked combos and when Company name changes change all corresponding (combo 2-4).

    Any good idea is very welcome…

    TIA
    RD

    Viewing 5 reply threads
    Author
    Replies
    • #1235001

      Don’t bind the controls to the sheet. Instead loop through the ranges adding each item to a Collection object (using On Error Resume Next to suppress errors if you try and add an item that already exists) to get a unique list then loop through the collection adding each item to the combobox. You can also use a Dictionary object, which has an Exists method and also allows you to access the Items directly as an array.

    • #1235003

      Thanks Rory,

      Problem is not so much the first addition of the lists. It’s when the value in combo1 changes that I’m in trouble…

      Then I need somehow to
      1. lookup the related values the array making up combo1 (the hidden columns on the control)
      2. match the values from each column in combo1 with a corresponding value somewhere in the list of each of the other combos
      3. set the .listindex on combos 2-4 to their corresponding new number

      …i guess

    • #1235159

      Rubberduck

      I am wondering whether you could make use of the Pivot Tables function to filter the various lists. This might be a simple way to avoid lots of VBA code and quickly filter your data sets based on user selections.

    • #1235172

      Why can’t you just set the Value of the other controls, using the values in the first array?

    • #1235173

      Hi Rory,

      Not quite sure that I understand what you mean by that… , but I’ll try to explain.

      Combo1 – array of 50 rows x 12 columns
      – user sees only column 1 and selects an item from there. All other cols contain – in database terms – duplicate values.

      Combo2 – currencies (fed from named range “dnCurrencies”, 8 items)
      – is also found in combo1 array in column 2 (so company X has a corresponding currency, Company Y may have the same CCY)

      Combo3 – countries (fed from named range “dnCountries”, 20 items)
      – is also found in combo1 array in column 3 (so company X has a corresponding country, Company Z may have the same country)

      So when user changes in Combo1, I get the .listindex returned and can see corresponding values for Currency and Country in columns 2 and 3 respectively.

      Then somehow I need to lookup these values in the other combo’s lists and once I’ve found their .listindex value then set it.

      So questions are:
      – How do I make the latter in the most efficient way?
      – Should it be done on a change event?
      – Do I need to link the events somehow?

      What would be the “best practise” approach on this? (if there is one – )

    • #1235185

      My question is why do you need the listindex? You know the value is in the list, so just set the value of the control to the selected value in combo1. That will automatically set the listindex.

    Viewing 5 reply threads
    Reply To: Bound combo-boxes

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: