+ Reply to Thread
Results 1 to 19 of 19

cascading combo boxes

  1. #1
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    cascading combo boxes

    I am trying to make cascading combo boxes where: the user selects a country in the first combo box and the second combo box lists the cities within the country selected in the first combo box.

    When I use data validation using indirect function, my model works perfectly but I really want to do it with combo boxes so that the user can go straight to a city by typing.

    Your help will be much appreciated.

    Thanks a bunch

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,325

    Re: cascading combo boxes

    This should get you on the right track

    http://www.contextures.com/xlDataVal02.html
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: cascading combo boxes

    mehmet

    Not sure it can easily be done with INDIRECT so I've used code in the attached.
    Attached Files Attached Files
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    Hi Nourie,

    Thanks a lot for the effort - it looks very good but the country name shows up in the second combo box (where I only want to see the cities)

    Would that be possible to remove it and show only city names?

    Thanks

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: cascading combo boxes

    What if there are no cities?

  6. #6
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    If there are no cities, it would be great if I could see just a blank line (basically nothing).

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: cascading combo boxes

    Change the code to this.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    I am getting a run time error for some values :When I debug, the following line is highlighted in yellow

    ComboBox2.List = rngCities.Value

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: cascading combo boxes

    Try this.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    Hey Nourie,

    It works well now; however, when I try to copy the two worksheets (sheet1 and countries and cities) to another excel file, I get a run time error 381 again which says "could not set the list property. invalid property array index". When I debug, the following line in the code is highlighted in yellow:

    ComboBox1.List = Application.Transpose(arrCountries)

    What do you think is the reason? I tried copying the two sheets to a few files and combo boxes worked fine in all except for the file where I need them.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: cascading combo boxes

    The problem is probably that the wrong sheets are being referenced.

  12. #12
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    I kept the same sheet names from the file you sent me - and there are no other sheets in the new file with same sheet name. Why do you think it is working in other files?

    Could that be because of cell range names? I have range names that are the same as the ones you use.

  13. #13
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    Indeed, I cleared all range names that are the same as the ones you use. I am still getting a run time error once I click on the first combo box. Your help will be highly appreciated

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: cascading combo boxes

    I didn't use any named ranges.

  15. #15
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    Can you tell me what potential steps I should be following if I need to copy these two sheets to a different excel file? copying and pasting the two sheets did not work out as I got the error message I told you before

  16. #16
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    2,015

    Re: cascading combo boxes

    mehmet, I copied those sheets to a new wkb without any troubles and the code provided by Norie worked very well. Try to pay attention...
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  17. #17
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    Thanks for the reply, John. I tried copying those sheets to a few different files and it seems like I am getting the error message in one file only. The code works will in all the others...

    Here is the line that is highlighted when I debug:
    ComboBox1.List = Application.Transpose(arrCountries)

  18. #18
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    2,015

    Re: cascading combo boxes

    who knows...LoL!
    but try to copy (at that file) the text (not the whole sheet) as it is in the original file, then add the comboboxes and then the code. perhaps it works for you

  19. #19
    Registered User
    Join Date
    02-22-2013
    Location
    istanbul
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: cascading combo boxes

    I indeed tried copying destination sheets to the source and it worked.

    I am linking the output of the two combo boxes to 2 separate cells in a worksheet. Lets say I choose US from the first combo box and NY from the second combo box - and cell A1 shows US and A2 shows NY. Is it possible to clear the contents of cell A2 and add a text "please choose a city" once the I change the country in the first combo box?

+ 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