Commas in VBE Data Validation List
 

Am using VBA for data validation lists. One of my lists has
commas within each item, example:
Sales, Marketing and Administration
Parts, Planning
Excel however interprets each comma as 'start the next
item on the next line', so I get in my list:
Sales
Marketing and Administration
Parts
Planning
How do I fix this please? I was thinking it was by using a code (like
CHR(13) is used for carriage return), but can't find anything. So far I have
(all on one line
in the code version):

xlBetween,Formula1:="Sales, Marketing and Administration, Parts, Planning"

Any assistance is appreciated!

Commas in VBE Data Validation List
 

If you store the list in a named range on a worksheet, you can refer to
that range as the list source. When a cell in the range contains a
comma, the entire cell will be treated as one item in the list.


Paige wrote:
Am using VBA for data validation lists. One of my lists has
commas within each item, example:
Sales, Marketing and Administration
Parts, Planning
Excel however interprets each comma as 'start the next
item on the next line', so I get in my list:
Sales
Marketing and Administration
Parts
Planning
How do I fix this please? I was thinking it was by using a code (like
CHR(13) is used for carriage return), but can't find anything. So far I have
(all on one line
in the code version):

xlBetween,Formula1:="Sales, Marketing and Administration, Parts, Planning"

Any assistance is appreciated!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Commas in VBE Data Validation List
 

Works great - thanks, Debra!!!!

"Debra Dalgleish" wrote:

If you store the list in a named range on a worksheet, you can refer to
that range as the list source. When a cell in the range contains a
comma, the entire cell will be treated as one item in the list.


Paige wrote:
Am using VBA for data validation lists. One of my lists has
commas within each item, example:
Sales, Marketing and Administration
Parts, Planning
Excel however interprets each comma as 'start the next
item on the next line', so I get in my list:
Sales
Marketing and Administration
Parts
Planning
How do I fix this please? I was thinking it was by using a code (like
CHR(13) is used for carriage return), but can't find anything. So far I have
(all on one line
in the code version):

xlBetween,Formula1:="Sales, Marketing and Administration, Parts, Planning"

Any assistance is appreciated!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html