+ Reply to Thread
Results 1 to 14 of 14

Indirect with ActiveX Combobox

  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

    I have a country list and a city list.

    I have no problem using the Indirect function and Data Validation with a Form Control Combo/Drop Down to reference one list to another.

    I cannot figure out how to do it with an ActiveX Combo.

    Sample is attached
    Attached Files Attached Files
    Last edited by dagindi; 07-07-2011 at 01:06 PM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Indirect with ActiveX Combobox

    dagindi,

    You need to set a Linked Cell in the ActiveX control's properties. So in your example, if you set ComboBox1's linked cell to B13, then the cell B13 would display whatever was selected in the combobox. You could then use formulas to reference B13 like you would a validation list.

    Hope that helps,
    ~tigeravatar

  3. #3
    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

    tigeravatar,

    Many thanks.

    I actually tried that first but then couldnt figure out where to put the formula in relation to combobox2.

    Can you clarify?

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Indirect with ActiveX Combobox

    dagindi,

    Oh I see what you mean now. You want combobox2's list to be dependent on combobox1's selection. Unfortunately, because you're using activeX controls, its not a formula. You'd have to use VBA. In designer mode, right-click combobox1 and select "view code" and then copy/paste the following:
    Please Login or Register  to view this content.


    Attached is a modified version of your sample workbook so you can see how it works.

    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Indirect with ActiveX Combobox

    dagindi,

    Here's the code with a line added to prevent errors if nothing is selected in combobox1:
    Please Login or Register  to view this content.


    ~tigeravatar

  6. #6
    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

    tigeravatar,

    Works great!

    But I cannot figure out how you did this. Can you explain how ComboBox2 is pulling the data?

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Indirect with ActiveX Combobox

    dagindi,

    The way you have your workbook setup is with named ranges. And then combobox1 is, essentially, a drop-down list of the names of those named ranges. So when combobox1 has a selection, combobox2 looks up that named range, and adds all the items in that range to its list.

    Hope that helps,
    ~tigeravatar

  8. #8
    Registered User
    Join Date
    03-03-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Indirect with ActiveX Combobox

    Not sure if you've noticed, but this doesn't work when you choose Hong Kong. Guessing it has to do with the fact that this is two words. Have a similar case in a spreadsheet I'm working on. In native XL you would use an indirect with substitute formula. Have little knowledge of VBA coding and would be very grateful if somebody could assist me.

  9. #9
    Registered User
    Join Date
    08-05-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Indirect with ActiveX Combobox

    Quote Originally Posted by tigeravatar View Post
    dagindi,

    Here's the code with a line added to prevent errors if nothing is selected in combobox1:
    Please Login or Register  to view this content.


    ~tigeravatar
    Dear Tigeravatar or Forum Family,

    This VB code works fantastic for when the named ranges are on the same sheet. Does anyone know how to make it work for when named ranges are on a different sheet?
    Unfortunately I canonly make it work when I use Data Validation list, but I need to use the ActiveX Combo Box as it is far more legible.

    Thanks in advance to everyone.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Indirect with ActiveX Combobox

    George,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  11. #11
    Registered User
    Join Date
    08-05-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Indirect with ActiveX Combobox

    Dear Tigeravatar or Forum Family,

    This VB code from Tigeravatar works fantastic for when the named ranges are on the same sheet. Does anyone know how to make it work for when named ranges are on a different sheet?
    Unfortunately I can only make it work when I use Data Validation list, but I need to use the ActiveX Combo Box.

    Thanks in advance to everyone.

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Indirect with ActiveX Combobox

    George,

    Did you check post 10?

  13. #13
    Registered User
    Join Date
    11-17-2004
    Posts
    3

    Re: Indirect with ActiveX Combobox

    The attached solution references only a single 'Countries' Range. This makes the list easier to manage if you need to add or remove a country/city in the future, without having to update each individual country Range.

    In exchange though, it uses a bit more VBA code. This solution makes use of a Dictionary object; is made available by adding a reference to the Microsoft Scripting Runtime.

    The following builds a countries (outer) and cities (inner) dictionary list, which gets consumed by the combo boxes to dynamically update its lists.

    Please Login or Register  to view this content.
    And code-behind on Sheet6. SetupCombox() is called when the workbook gets opened.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-05-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Indirect with ActiveX Combobox

    Thanks VBC22. I'll give it a try and see how it goes.

    Just curious if you have any experience with "ListFillRange" function?
    I have been trying to use this function with the values of another ComboBox, without success.

    Thanks
    Kind Regards,
    George

+ 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