Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Data validation list entries all on one line in Excel


View products that this article applies to.

Symptoms

When you run a Microsoft Excel macro or other programming code that sets data validation rules for a cell as a list of valid entries, all of the items in the data validation list on the cell appear on one line.

↑ Back to the top


Cause

This behavior occurs when the following conditions are true:
  • The List Separator setting (under Regional Options in Control Panel) is something other than a Comma (,). For example, if your locale setting is Germany, your list separator is a semicolon.
  • The macro for data validation uses that list separator to specify the valid cell entries.
  • The macro code specifies the list explicitly, instead of pointing to a cell range where the valid entries are listed.

↑ Back to the top


Workaround

When you create a macro to specify a specific list of valid entries, always use a comma (,) as your list separator.

↑ Back to the top


More Information

In Excel, Microsoft Visual Basic for Applications always uses the comma as the list separator. This allows you to run a macro on computers that have different locale settings, without having to edit your code. 

When passing a comma delimited array using VBA (XLValidateList Formula1) for data validation a 255 character limitation applies.

When you run a macro that uses a list separator to change a setting, the macro converts the comma to the local regional list separator. For example, this occurs if the macro creates a data validation list, or enters a formula in a cell by using the Range.Formula(number1,number2) command.

↑ Back to the top


References

For more informationabout data validation in Excel, click the following article number to view the article in the Microsoft Knowledge Base:

211485 Description and examples of data validation in Excel

↑ Back to the top


Keywords: kboffice12yes, kboffice12vista, epucon, kbdtacode, kbpending, kbprb, offcon, kbbillprodsweep, kb, kbfreshness2006

↑ Back to the top

Article Info
Article ID : 299490
Revision : 4
Created on : 8/20/2020
Published on : 8/20/2020
Exists online : False
Views : 192