Monday, January 23, 2023

Data validation list entries all on one line in Excel - Microsoft Support [MS]

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

No comments:

Post a Comment

Search This Blog

December 6, 2016, update for Office 2016 (KB3127990) - Microsoft Support [MS]

December 6, 2016, update for Office 2016 (KB3127990) Office 2016 More.....