EXCEL: Data Validation With Case Sensetive.

[gaikwad.mm]

Dear Experts,

1) I' am having named range which I' want to use as drop down list i.e. "NamedRange" which contains following records.

inDia
ZooOOz
comPLete
ExCel
inTerneT

2) Here you can see that the words are having both upper and lower mix cases which are also visible in the drop down list.
3) But if the user type "INDIA" instead of "inDia" (as per the drop down list) still the excel is excepting it, as it is going by the alphabets and not with cases.
4) My requirement is the user should enter only the word given in the list as it is with mix cases, even if he type it manually.

5) Another concern is if the user copy any other blank cell on the cell where the data validation list appear, the drop down option is getting deleted.

Please provide the solution via VBA code so I' can protect it by password. Also share the sample excel file which will really help me to apply the solution on my actual data.

Thanks
MG

[GTO]

...Please provide the solution via VBA code so I' can protect it by password. Also share the sample excel file which will really help me to apply the solution on my actual data...

Greetings,

How about you provide a sample file instead, and we try and provide a solution? This way, just one person (you) is building at least an initial example, instead of multiple people (any potential 'answerer') building what they hope looks something like what you got.

Please do not take that as "negative", it is just what makes sense.

Mark

[gaikwad.mm]

Dear GTO,

You said it right, Hence please find attached file with my requirement along with raw data. Your help will be really appreciated.

Thanks
MG

[GTO]

Thank you very much. I apologize, but my thought was that we might be able to use 'Custom' and a formula that draws upon a UDF. Thus far at least, this doesn't happen. Must sleep, will check back.

Mark

[gaikwad.mm]

Any Updates On This?????

[GTO]

Not per se, at least not as to how (if possible) to get DV to use a UDF. I saw (did not try) one response indicating to use a Name to refer to the Function and use the Name in the DV, but I did not try. I barely searched (using Bing) and a couple of responses indicated that if the List was literal (rather than referring to a Range), that the Case then gets enforced.

I used:

Excel UDF with Data Validation
excel enforce case in data validation

as search terms.

Anyways, I am not sure how long a string you can supply List, but for your example wb, I tried this (which seems to work) :

In ThisWorkbook Module:


Option Explicit

Private Sub Workbook_Open()
example
End Sub

In a Standard Module:


Option Explicit

Sub example()
Dim arr_strAcceptableVals() As Variant
Dim strList As String
Dim n As Long

With Sheet1

arr_strAcceptableVals = .Range("NamedRange").Value 'DropDownRange"
For n = 1 To UBound(arr_strAcceptableVals)
strList = strList & arr_strAcceptableVals(n, 1) & ","
Next

strList = Left$(strList, Len(strList) - 1)
Debug.Print ">" & strList & "<"

With .Range("DropDownRange").Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween, strList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = vbNullString
.ErrorTitle = vbNullString
.InputMessage = vbNullString
.ErrorMessage = vbNullString
.ShowInput = True
.ShowError = True
End With

End With

End Sub

Hopefully you will find that the literal string doesn't exceed whatever limit may exist.

Does that help?

Mark

[GTO]

ACK!

"DropDownRange" is what I named the range C3:C7 in your example wb.

[GTO]

Uhm... I didn't read this one before answering. If you do not mind losing the drop-down, you could use Custom and a formula:

=MATCH(TRUE,EXACT(C3,A3:A7),0)

Which I spotted from some lad posting at #3 here: http://www.mrexcel.com/forum/excel-questions/238024-case-data-validation.html

Mark

(Hi Bob)

[gaikwad.mm]

Could you please share the same with sample file ... I' am unable to create it at my end.

[GTO]

Here you go:-)

[gaikwad.mm]

Copy / Paste destroying the logic.