Show error message when word in text string isn't in approved list.

KyleJackMorrison

Board Regular
Joined
Dec 3, 2013
Messages
107
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Hello, i would like a code that will show when a user enters a string of text and there isn't a word in that string that doesn't meet the approved list.

Approved list:
'World'
'Planet'
'Globe'
'Earth'

If someone enters in a cell "Hello World' or 'What is Earth' nothing will show,
but if someone says 'Hello John' it to show an error saying, you must use one of the approved words.

TIA
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
It is advisable to put your list of words on a sheet, for example, create a sheet called "list" in column A put the words.
Now put the following code in the events of your sheet where you want to validate.

Now, everything you capture in column B will be validated. Change "B:B" to the column where you want the validation.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Not Intersect(Target, Range("[B][COLOR=#ff0000]B:B[/COLOR][/B]")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Dim word As Variant, i As Long, b As Range, exists As Boolean
        
        exists = False
        word = Split(Target, " ")
        For i = 0 To UBound(word)
            Set b = Sheets("list").Range("A:A").Find(word(i), LookIn:=xlValues, lookat:=xlWhole)
            If Not b Is Nothing Then
                exists = True
                Exit For
            End If
        Next
        If exists = False Then
            MsgBox "You must use one of the approved words"
            Application.EnableEvents = False
            Target.Value = ""
            Application.EnableEvents = True
        End If
    End If
End Sub

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

Awesome works great. Don't suppose you could show me how to tweek it to not display the message if i double click the cell and its an empty cell?
 
Upvote 0
@Dante,

Awesome works great. Don't suppose you could show me how to tweek it to not display the message if i double click the cell and its an empty cell?


Try this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
[B][COLOR=#0000ff]        If Target.Value = "" Then Exit Sub[/COLOR][/B]
        Dim word As Variant, i As Long, b As Range, exists As Boolean
        
        exists = False
        word = Split(Target, " ")
        For i = 0 To UBound(word)
            Set b = Sheets("list").Range("A:A").Find(word(i), LookIn:=xlValues, lookat:=xlWhole)
            If Not b Is Nothing Then
                exists = True
                Exit For
            End If
        Next
        If exists = False Then
            MsgBox "You must use one of the approved words"
            Application.EnableEvents = False
            Target.Value = ""
            Application.EnableEvents = True
        End If
    End If
End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top