Cell to only allow certain text strings.

[KyleJackMorrison]

Hello,

I need help with a code that will only allow certain text strings in the cell. So like data validation however not so restricted.
For example restrict the cell to have these:
Holiday
Course
Leave

But! I would like users to be allowed to enter other information before or after. For example:
"Christmas - Holiday" = Allowed
"Holiday - Bahamas" = Allowed
"First Aid Course" = Allowed
"Pineapples" = Not allowed
"Sick Days Off" = Not allowed

Many Thanks!

 

[DanteAmor]

Put the following code in the events of your sheet

Change data in red by your information

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("[COLOR=#ff0000]B:B[/COLOR]")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        If Target.Row < [COLOR=#ff0000]2 [/COLOR]Then Exit Sub   'row 1 with headers
                
        vals = Array("Holiday", "Course", "Leave")
        For i = 0 To UBound(vals)
            If InStr(1, Target.Value, vals(i)) > 0 Then
                exists = True
                Exit For
            End If
        Next
        If exists = False Then
            MsgBox "Text not allowed"
            Target.Value = ""
            Target.Select
        End If
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[KyleJackMorrison]

Awesome works like a charm.

Anyway I can get it so it's not case sensitive?

 

[Eric W]

You can do it with Data Validation. Let's say you have your words in A1:A3, and the cell you want to check is B1. Then select B1, click Data Validation > Custom > and enter:

=OR(ISNUMBER(SEARCH(A1:A3,B1)))

Case insensitive. If you want more words, or even a variable amount of words based on the list, we can adapt the formula to accommodate.

 

[DanteAmor]

Awesome works like a charm.

Anyway I can get it so it's not case sensitive?

Change this:

[COLOR=#333333]If InStr(1, Target.Value, vals(i)) > 0 Then[/COLOR]

By:

If InStr(1, LCase(Target.Value), LCase(vals(i))) > 0 Then