Data validation list entries all on one line in Excel

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.

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.

Workaround

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

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.

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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×