Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Why can't you just set the Value of the other controls, using the values in the first array?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 - )
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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