Excel Data Validation

Question

Consider having a list - Ferrari, Mclaren, Red Bull on A2, A5, A8 cells, if i make a name for the list - "F1", and put it in data validation it pops up an error -

"The list source must be a delimited list, or reference to single row or column".

Is there any workaround?

lori_m · Accepted Answer · 2012-03-31 14:39:14Z

2

AFAIK the only way is to convert your range of cells to a delimited string...

activecell.Validation.Add xlValidateList,,,Join(array([a2],[a5],[a8]),",")

The list separators are dependent on your locale settings, comma or semicolon (File > Options > Advanced > Use system separators)

answered Mar 31, 2012 at 14:39

lori_m

5,5751 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

madyykk Over a year ago

Can you please explain more, i am not familiar with tha VBA code.

lori_m Over a year ago

To run the code, choose a cell, press Alt+F11 then enter the command in the immediate window. You can also just do this manually if you are not familiar with VBA, the idea is just to put the list in the validation dialog box as you highlighted in the question.

Li-aung Yip · Accepted Answer · 2012-03-31 14:14:02Z

0

The delimiters must be semicolons, not commas.

Ferrari;McLaren;Red Bull

answered Mar 31, 2012 at 14:14

Li-aung Yip

12.5k5 gold badges36 silver badges51 bronze badges

Collectives™ on Stack Overflow

Excel Data Validation

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related