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.

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.

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.