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

KyleJackMorrison

Board Regular
Joined
Dec 3, 2013
Messages
101
Office Version
  1. 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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.
 

KyleJackMorrison

Board Regular
Joined
Dec 3, 2013
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
@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?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

@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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,794
Messages
5,598,078
Members
414,210
Latest member
Janisogor

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
Top