• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3683
  • Last Modified:

making list validation case- sensitive

Good morning all ....
Just wonder is there a way to do this. I've got a list set up (mapped to a range name) but how do I set it up to be case-sensitive e.g. if Paris is in the list, I want the dropdown list to ONLY accept Paris, not paris i.e. to be an exact match...
Thanks as always..
0
agwalsh
Asked:
agwalsh
  • 5
  • 5
  • 4
  • +1
5 Solutions
 
Rory ArchibaldCommented:
If you still need the dropdown list, your options are:
1. to use a typed list in the DV source box (not a range)
2. to use a combobox control instead
3. To use code to update the entry after it is made.

If not, you can use a formula to ensure that the text is an exact match.
0
 
www_exacto_dkCommented:
Hi,

If you want to use code you could e.g. loop the values in the named validation range and see if the match is exact. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

' Variables
    Dim cl As Range
    Dim val_rng As Range

' Error handling disabled (to ignore if cells without name is changed)
    On Error Resume Next

    If Target.Name.Name = "lst_choice" Then
    ' Loop named range which is source of validation list (Note: VBA makes a case sensitive comparison)
    ' Note: lst_choie = named range of the cell with the validation list
        Set val_rng = Range(Target.Validation.Formula1)
        For Each cl In val_rng
            If cl.Value = Target.Value Then
            ' Exact match => exit and do no further
                Exit Sub
            End If
        Next cl
   
    ' No exact match found => Error msg
        ' Reset value, events disabled to avoid trigger change event when cleaning invalid value
            Application.EnableEvents = False
            Target.ClearContents
            Target.Activate
            Application.EnableEvents = True
       
    ' Error msg to user
        MsgBox "Invalid input value", vbExclamation, "OBS!"
       
    End If

' Re-enable error handling
    On Error GoTo 0

End Sub

Note: You could also choose to insert the correct value if the match is ok, but only the case is wrong.

Brg

Martin
0
 
agwalshAuthor Commented:
Hm, what formula would I use? Would it be some sort of text formula? And how would I apply code if I went that way. The typed list is not really an option as I want  it to be easy for people to update it..
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Rory ArchibaldCommented:
Something like:

=OR(EXACT(B1,$F$1:$F$2))

where B1 is the validation cell and F1:F2 contains the list of values). Martin has provided sample code already.

HTH
Rory
0
 
www_exacto_dkCommented:
Hi,

Rory, I am not able to use an EXACT with a range consisting of more than 1 cell, seems to me that it can only match cells 1:1, or am I mistaken?
In order to apply the code you do the following:
- Press ALT + F11
- In the VB Project navigator you find the worksheet which you have the validation cell on
- Doubleclick the sheet and paste the code above into the window

Brg
Martin
0
 
Rory ArchibaldCommented:
EXACT should work with a range too.
0
 
www_exacto_dkCommented:
Ah, just tested it here, you are right, Rory, as far as I can see it then needs to be an array-formula, like:

={OR(EXACT(A1:A3;B1))}

Agwalsh: Notice the { } - In case you do not know them already, you get them by pressing CTRL + SHIFT + ENTER simultaneously, when you have written the formula.

Brg
Martin
0
 
agwalshAuthor Commented:
I love the look of the Or/Exact formula but where do I put it? Since the validation cell and list are both referenced in the formula..
and can I use a range name for the list?
Thank you...so much :-)
0
 
Rory ArchibaldCommented:
You change the data validation from List to Custom and then enter that in the source box. You can replace the range address with a range name.
0
 
agwalshAuthor Commented:
@rorya, have tried that and it certainly stops the user entering anything but the case sensitive match but for some reason my dropdown has disappeared? Any reason for that? thanks again.
0
 
Rory ArchibaldCommented:
I did say you could only use the formula if you didn't need the dropdown! You can't have both.
0
 
www_exacto_dkCommented:
Using VBA as inserted above you would achieve exactly what you want.

If you do not want to use VBA you might find some inspiration here:

However, that will require that you use some sort of hidden "support ranges", but that might be acceptable.

Brg
Martin
0
 
Elton PascuaCommented:
Here's an alternative VBA solution.
case-sensitive-validation.xlsm
0
 
agwalshAuthor Commented:
Alas, I thought it would be easier than the solutions I found here - but that's how it goes. Thanks to everyone who helped.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now