+ Reply to Thread
Results 1 to 5 of 5

ActiveX Combo Box and VBA code not working on multiple Data Validation Dropdown Lists

  1. #1
    Registered User
    Join Date
    08-25-2016
    Location
    Calgary, Canada
    MS-Off Ver
    2010
    Posts
    4

    ActiveX Combo Box and VBA code not working on multiple Data Validation Dropdown Lists

    Hello,

    I'm very new to this and I've been reading and learning from everybody's posts here. To workaround the problem of data validation dropdown list's small font on request form I’m working on, I've come cross to this helpful page http://www.contextures.com/xlDataVal14.html, and I'm quite happy that it works on one of my dropdown lists, but not for all of them. I suspect there is something I need to change in the VBA code but couldn’t find answers on this forum... I'm hoping someone could help me please!

    Some of my data validations are in merged cells and some are in single cells.
    Also, I have a column of data validation which is based on the cell before, and the source formula looks like below.
    =OFFSET(Brands!$B$1,MATCH(A32,Brands!$B:$B,0)-1,1,COUNTIF(Brands!$B:$B,A32),1)

    Any input is greatly appreciated.

    Please Login or Register  to view this content.
    Last edited by ringonohitorigoto; 09-29-2016 at 11:09 AM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,638

    Re: ActiveX Combo Box and VBA code not working on multiple Data Validation Dropdown Lists

    Although the code is useful, it is preferable to get your whole file so we can see how the DV lists are defined, how the combobox is implemented, and so forth. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

    I reviewed the link you provided, and I think this kind of solution is overthinking this whole problem. I would simply use comboboxes everywhere linked to a cell, instead of data validation lists, then you don't need any code.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    08-25-2016
    Location
    Calgary, Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: ActiveX Combo Box and VBA code not working on multiple Data Validation Dropdown Lists

    Hi 6StringJazzer,

    I have uploaded my sample file. Thank you for quickly responding! Hope the file helps and if VBA code is not the way to go, I'm open to any simpler solutions!
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,638

    Re: ActiveX Combo Box and VBA code not working on multiple Data Validation Dropdown Lists

    OK. Here's the problem. You are using your Data Validation lists differently than the linked site instructs. To be fair, that site is not as clear as it could be. That site says to create a list of values in a worksheet, then reference that range as your data validation Source, so the formula will look something like

    =A1:A10

    Instead, you just typed in the values you want:

    NA,LA,EA,ME,CIS,CHN,FE-ID,FE-ML,FE-AU,FE-IN

    What you did works fine for data validation, but not for this solution. The code assumes that this list is a formula starting with "=". It strips off the "=" and puts the rest as the ListFillRange value for the combobox. This must be a range expression. Yours isn't a formula, so it breaks the code.

    it works on one of my dropdown lists, but not for all of them
    I don't know which one it works for, and I can't take the time to click on every cell in your entire sheet to find out where the data validation lists are. The following is the quickest option to get you up and running:

    Create a hidden sheet that has each list you want to use in a data validation dropdown. For example, take the list above and put those values in A1:A10 of the new sheet. Then update your data validation source to be

    ='List Sheet'!$A$1:$A$10

    Repeat this for every data validation list you have, then your existing code should work.

    You could also replace every dropdown list with a combobox. But you already have the code in place, you would still have to create the lists as I just described, and you would have to create all the comboboxes, so the first option is much faster to build.

  5. #5
    Registered User
    Join Date
    08-25-2016
    Location
    Calgary, Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: ActiveX Combo Box and VBA code not working on multiple Data Validation Dropdown Lists

    Hi 6StringJazzer,

    Thank you!! The only cells that worked were cell A32 down which I already had a separate sheet of list for, so it makes total sense. I've tried to do the same for all other dropdown lists, and they worked perfectly!
    The only thing left unsolved is cells B32 down which contain...
    =OFFSET(Brands!$B$1,MATCH(A32,Brands!$B:$B,0)
    as a data validation source (dependent), and I'm wondering if the code applies to this type of dropdown list...?
    Other than this, I've tried using "=INDIRECT(A32)" as a source of data validation list for same cells and it works no problem too.

    I am so close to completing this form thanks to your huge help!
    Hope you or anyone could help me figure out a code that applies to dependent dropdown list as well.

    Thank you.

+ 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. Data Validation Combo Box with Multiple Independent Validation Lists
    By firstofnine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2016, 04:38 PM
  2. Data validation referring to an activex combo box?
    By phmdt in forum Excel General
    Replies: 6
    Last Post: 03-09-2016, 07:32 PM
  3. [SOLVED] Autocomplete dropdown list, NOT data validation or ActiveX
    By Phillips Contracting in forum Excel General
    Replies: 7
    Last Post: 03-24-2014, 02:31 PM
  4. Massive Data Validation with Dropdown lists
    By tonalqualityx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-09-2013, 12:12 PM
  5. Changing code to allow for multiple data validation lists/combo boxes
    By StatsN3rd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2013, 02:12 PM
  6. creating VB code using advanced search criteria with multiple data validation lists?
    By Jonathan Bay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2011, 09:56 AM
  7. filtering multiple data validation dropdown lists
    By gdallas in forum Excel General
    Replies: 4
    Last Post: 05-12-2010, 03:28 AM

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