+ Reply to Thread
Results 1 to 5 of 5

Indirect with ActiveX Combobox (Revisit)

  1. #1
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Indirect with ActiveX Combobox (Revisit)

    I recently posted and closed a thread called Indirect with ActiveX Combobox

    Attached is the same workbook, however this time I added a second sheet called Calc.

    I cannot figure out how to code this so that the Calc sheet is referencing the lists on the List sheet.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,163

    Re: Indirect with ActiveX Combobox (Revisit)

    Hi dagindi,

    It seems you are an experienced VBA person so this answer may be off base, but...

    I think you are running into a SCOPE of variable and/or Private vs Public Module question.
    See
    http://www.cpearson.com/excel/codemods.htm for the module the code should be in.
    and
    http://www.your-save-time-and-improv...ivate-sub.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    Re: Indirect with ActiveX Combobox (Revisit)

    Change one line in your code:

    Please Login or Register  to view this content.
    Although the range name is scoped for the workbook, an unqualified reference to Range refers to the worksheet that the code is in.

    Also, note that you list Hong Kong as a country but have no cities associated with it. Meantime you list Hong Kong as a city in China.

    Also, note that your country list has "United Kingdom" but there is no named range for that. Instead, your named range is "UK". This generates an error if you choose United Kingdom from your first combobox. In fact, you will have this problem any time you have a country name that has spaces in it.

    Which leads me to my next point: this is not a flexible solution. If you need to add cities you have to mess with your named ranges. I've included another example that you might want to consider. It adds a list to show what country each city is in, instead of making named ranges to group them. The extra combobox on Calc uses this method. Now if you want to add cities, you just add the city to the list and type in the corresponding country name next to it, rather than add or update named ranges. This solution also doesn't care that there are spaces in the country names.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Re: Indirect with ActiveX Combobox (Revisit)

    Marvin,

    My experience is limited to what I can find online and the cut paste or manipulate to get it to work. When someone is kind enough to post on Excel Forum, in addition to use what they provided, most of the time if I don't understand the answer (even if the code works) I ask for clarification. Some times I get lucky and a follow up is posted in the thread.

    Thanks for the links. I will read them asap.

  5. #5
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    293

    Talking Re: Indirect with ActiveX Combobox (Revisit)

    StringJazzer,

    Thank you so much for the assist. Your post was easy to understand and follow. I prefer your two list version. While it will be a bit larger, it will definitely be easier to manage!!

    Many Thanks!

    Re: Hong Kong - In a nutshell, depending on how the data is used Hong Kong, China could be totally different then Hong Kong, Hong Kong. You can be a China based company that ships out of Hong Kong or you can be a Hong Kong based company that ships out of Hong Kong. Greatly affects shipping times, handling, etc

    Re: UK - I ran into the exact problem with the space. I'll probably switch over to a format like UnitedKingdom if the list gets out of control.

    Again... Many thanks.. This was a huge help!

    PS - You had a slight error in the code:

    I changed:

    Please Login or Register  to view this content.

    to

    Please Login or Register  to view this content.

+ 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