excel data validation list only upper case

Question

I want to do some data validation in my excel workbook. I want to use as many centralized values as possible for the data validation as the same values are used for multiple sheets. Now I have added some validation for Currency codes (USD...). the validation is based on a list in another sheet and its working kinda good. my problem is that I can still enter lower case values and not just exact values. I would prefer a solution which does not use VBA is possible. If its only possible with VBA, then please add some information on how to add it to the workbook and such.

The values i want to allow are:

CHF
EUR
GBP
USD
DKK
SEK
NOK

and the data validation looks like this: =Sheet1!$S$2:$S$8 but also allows the values above as lower case which is not what i want (i see that i can list the values inside the data validation and then it only accepts exact matches, but for the sake of not having much work if the values change, i want to have them centralized).

I think it would be also good enough to convert the values into upper case, but it has to happen in the same cell eg. I enter eur in cell J6 then it should convert the value to upper case and write it also in J6

Answer 1

I found a solution with VBA:
Do a right-click on the tab of your worksheet and chose "Show code...". In the VBA-Editor enter these macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:C100")) Is Nothing Then
         On Error GoTo ErrorHandler
         Application.EnableEvents = False
         Target.Value = UCase(Target)
    ErrorHandler:
        Application.EnableEvents = True
    End If
End Sub

Change the range corresponding to your needs. You have to save your file as *.xlsm. Then every text in the range is converted to uppercase.

Answer 2

If the cell you want to validate is in cell A1, set your Data Validation type to Custom instead of List and paste this formula in:

=AND(SUMPRODUCT(--((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)),--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))=LEN(A1),--(IFERROR(IF(MATCH(A1,Sheet1!$S$2:$S$8,0),1),0)))

Walkthrough

This formula uses two conditions, combined with an AND() - firstly a check of whether the value is all upper case, secondly a check that the value matches your list of permitted options.

 =AND(
    SUMPRODUCT(
       --(
           (CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)
         )
       ,--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91)
     )=LEN(A1)
   ,--(IFERROR(IF(MATCH(A1,Sheet1!$S$2:$S$8,0),1),0)))

The bits that look like CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64 are checking the ASCII code for each individual character. If they're between ASCII codes 64 and 91 we know they are upper case. If the number of upper cases characters matches the length of the value string, we know they are all upper case.

The MATCH() part checks the value appears in your list of currencies.

Note: Shoutout to this post for the upper case check.