Tag Archives | automation

Excel Data Validation and Case-Sensitivity

I was working with a client recently on some VBA programming and the client was trying to limit data entries in a column of cells to one of four specific values. Now those of you who are familiar with Excel will immediately shout out “set up data validation”, and you would be correct. However, not entirely, as most people would create the set of entries in a range of four cells somewhere and set the Excel data validation to that range of cells.

While using data validation this way would work after a fashion it would not enforce the case of the entries. Those of you staying in Excel would probably not worry. I was working using VBA (Visual Basic for Applications) and the VBA code was case sensitive. Not only that but the client wanted to force the text entered by users to have the first letter of each entered word to be upper case.

So how do you force data validation to enforce the case of entered values. The answer is to enter the values literally in the Source box of the Data Validation dialog, separating each value with a comma, and not set the source to be a range reference. So if I was restricting my entries to the three major cities of England then I would type in the Source box:

London, Birmingham, Manchester

In this way London would be acceptable but london or lonDON would be rejected.

Of course, if you are working with a dynamic list then you have to stick with range references and use some other method (as I would in my VBA code) to enforce the case of entries, but the above is a simple solution for short, static lists.