Case sensitive validation in Excel from Named List

Question

I'm using VBA in Excel 2003 to apply validation to apply validation to a given range of cells from a named list. The user can then select from a dropdown list of values.

Edit: Here's how I'm setting the validation, given a named range called 'MyLookupList'

        With validatedRange.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=MyLookupList"
            .ErrorMessage = "Invalid value. Select one from the dropdown list."
            .InCellDropdown = True
        End With

All that works fine, but the problem is that when validation is applied from a named list, it is case-insensitive. I.e. if a dropdown choice is "John Smith", then the user can type in "john smith" or "john SmiTh" into the validated cell and Excel will still treat it as a valid entry.

I know that manually creating a list via Tools-->Validation... will make the lookup validation case sensitive, but for my case this is just not feasible - I have to populate the named lists and assign validation programmatically.

Does anyone know of a way to ensure that Excel validation based on named lists is case-sensitive?

Thanks.

Answer 1

Well you could just build the validation list given the validation range (assuming it's not too large)

Dim sValidationList As String
Dim iRow As Integer

  'build comma-delimited list based on validation range
  With oValidationRange
    For iRow = 1 To .Rows.Count
      sValidationList = sValidationList & .Cells(iRow, 1) & ","
    Next
  End With

  'trim trailing comma   
  sValidationList = Left(sValidationList, Len(sValidationList) - 1)

  'apply validation to data input range
  With oDataRange.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
      Operator:=xlBetween, Formula1:=sValidationList
    .ErrorMessage = "Invalid value. Select one from the dropdown list."
    .InCellDropdown = True

  End With

Answer 2

How about StrComp? StrComp string comparison is case sensitive if you use vbBinaryCompare. For example:

   Set c = Range("MyLookupList").Find(Range("ValidateRange"), _
         LookIn:=xlValues)
    If Not c Is Nothing Then
        If StrComp(c, Range("ValidateRange"), vbBinaryCompare) = 0 Then
            'Match '
            MsgBox "OK"
        Else
            MsgBox "Problem"
        End If
    End If

Answer 3

Have a look at this:

http://www.contextures.com/xlDataVal14.html

I haven't tested it and it's a bit more complicated but I think it will do what you want.