+ Reply to Thread
Results 1 to 9 of 9

Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

  1. #1
    Registered User
    Join Date
    06-21-2018
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    9

    Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    Hello everyone,

    First of all - thank you all. I've learned so much reading your forum!

    Now my bummer - I'm trying to get a dependent drop down list in ActiveX combobox.
    A.k.a I have a 14 items list in ComboBox.1 and choices that have to vary in ComboBox.2. After quite a search I found this: /excel-programming-vba-macros/782864-indirect-with-activex-combobox.html and seeing the problem is solved was a bright light... but not quite in my case. (I knew it was too easy to be good :D )
    Just pasting the
    Please Login or Register  to view this content.
    ...gets me an error: "Method 'Range' of object'_Worksheet' failed". The Debug sends me on
    Please Login or Register  to view this content.
    row.
    Although this is not everything in this sub:
    Please Login or Register  to view this content.
    ...and I also have 6 other simple subs in the worksheet that work just fine without the above. My working file is attached.
    To aim higher I even need a 3d dependent list in ComboBox.3 which I haven't even created yet. Do you think the above solution could work on the 3d list as well?

    Thank you in advance.
    Kind regards.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    Re: Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    Not entirely clear on what it is you're asking/looking for. I think i may've encountered a similar issue no that long ago though and someone on the forum helped me out so I'm shooting you the link to see if maybe it's the same problem. For my particular situation i was trying to have a drop down list appear only when certain requirements were met and stay hidden until then. Here's the link. May or may not help. Good Luck,

    https://www.excelforum.com/excel-gen...ml#post4886000

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    You're very close. Just qualify the sheet where the lists are found.

    I also added an If statement to test if something in the combobox is selected.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    06-21-2018
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    9

    Re: Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    Hello and thank you for the replies!

    Jim Clayton, this is not exactly what I asked but I'm sure it will help me a little more down the road because this is few steps ahead in my 'project' and I guess I'll fail there as well. :D As the linked guy, I also suck at explaining but the general idea is for me to have 3 each-other dependent drop-down lists as ActiveX items based on already done lists outside VBA with only =INDIRECT.

    AlphaFrog, I really appreciate your help. You IF updated variant looks exactly how I imagined it but unfortunatelly it's still not working for me. This time the error is...

    Run-time Error '1004': Application defined or object-defined error

    ...and it's still debugging me on the same row:
    Please Login or Register  to view this content.
    I still can't google this out... Reattached again.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    "Application defined or object-defined error" means it cant find an objet. There are only two objects in the line that errors; Combobox1 and the named range of cells selected with the combobox.

    We know the combobox exists; We reference it in the previous line of code without error. As best i can tell the named ranges exists too. So I'm not sure why it errors.

    I cannot run your exact workbook because I have an older version of Excel. But I did copy your DB sheet and tested it with a combobox and your code and it worked. For what its worth.


    I doubt this will help, but try this line instead.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-21-2018
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    9

    Re: Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    It doesn't AlphaFrog, but your explanation and thoughts helped me through... I recalled my dropdown list choices are multi-worded with even special characters so I redirected them to vlookup the one-word and pull name ranges from there. So it turned out my linked list of options was not correct. That fixed means the 3 comboboxes are working perfectly. Of course now I have to deal with the problem how to see 'xxx® xxx xxx 999' but the combobox to read the correct represented name range...

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    Have the code replace spaces in the selected item with underscores and then reference the named range...

    Please Login or Register  to view this content.

    This syntax might work too...

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 06-27-2018 at 06:15 AM.

  8. #8
    Registered User
    Join Date
    06-21-2018
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    9

    Re: Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    Oh my God, you are amazing! Thank you very much for the help!

    In my case the whole line went:
    Please Login or Register  to view this content.
    Hah it feels like a miracle-work... :D

    I'm very sorry for the late late reply but for some reason the website did not let me post anything for quite some time and just yesterday I found out why... (thanks to /tips-and-tutorials/1213799-sucuri-block-access-denied-sucuri-website-firewall-workarounds.html).

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    78,944

    Re: Dependent DropDown lists in VBA /=INDIRECT() on ActiveX ComboBox/

    Yes, several (but by no means all) members were affected by the Sucuri block yesterday - the technical team have been notified. Sorry for the inconvenience.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Indirect Function with Combobox activex controls
    By HOENY428 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-25-2017, 02:03 PM
  2. ActiveX ComboBox DropDown
    By EssoExplJoe in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-24-2017, 09:21 AM
  3. ActiveX Combobox dropdown 'reset' when repopulating
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-27-2017, 06:01 AM
  4. Dynamic Dependent ActiveX Dropdown from Column Data
    By mani_bbc05 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2013, 02:17 PM
  5. Indirect with ActiveX Combobox
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-06-2013, 04:25 AM
  6. Indirect function with ActiveX Combobox
    By George Issa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-06-2013, 02:20 AM
  7. Indirect with ActiveX Combobox (Revisit)
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-12-2011, 01:39 PM

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