Adding multiple constants to Worksheet_Change Event, bringing up MsgBox based on multiple parameters

[Melowshipslinki]

Now that my Worksheet Change event set up, so that when a certain phrase 'x' is entered into any cell in Range("H:H"), a MsgBox appears, saying, "the use of 'x' is forbidden; use 'y' instead". This is so that my users are made aware of the fact that 'x' is invalid, and so they develop a habit of using the valid phrase.

I would like to be able to include multiple constants, forbidden phrases specifically. I have uploaded a blank spreadsheet with my code in Sheet1. For some reason that I'm not aware of yet, I can't create and run one macro without having another empty macro below it (another problem for another thread), but that's why there's an empty macro in my sample :/

I've tried all kinds of syntax in the code,

Const ksForbidden = "x","x1"

Const ksForbidden = "x" + "x1"

etc.

Here's what I have now, it doesn't work now- am I at least on the right track with adding an ElseIf? Or should there be a second With instead? From the examples I looked at, I'm pretty sure there can't be two separate Worksheet Change events in one WorkBook, unless I understood wrong. Any help would be greatly appreciated!!!

Private Sub Worksheet_Change(ByVal Target As Range)

Const ksTestRange = "H:H"
Const ksForbidden = "x"
Const ksForbidden1 = "x1"

Const ksAllowed = "y"


If Application.Intersect(Range(ksTestRange), Target) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

With Target
If .Value Like "*" & ksForbidden & "*" Then

MsgBox "Use of <" & ksForbidden & "> is forbidden; use <" & _
ksAllowed & "> instead.", _
vbApplicationModal + vbExclamation + vbOKOnly, "Warning"
ElseIf .Value Like "*" & ksForbidden1 & "*" Then
MsgBox "Use of <" & ksForbidden1 & "> is forbidden; use <" & _
vbApplicationModal + vbExclamation + vbOKOnly, "Warning"
End If
End If
End With

End Sub

 

[kchiba]

Why not have a list of the forbidden words in the file somewhere and then check the input against that list

 

[SirJB7]

Hi, Melowshipslinki!

Taking my previous file as a base, I build this with a table Forbidden/Suggested in 2nd worksheet.
https://dl.dropboxusercontent.com/u...ers (for Melowshipslinki at chandoo.org).xlsm

This is the code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    ' constants
    Const ksTestRange = "H:H"
    Const ksWSLists = "Hoja2"
    Const ksForbidden = "ForbiddenList"
    Const ksAllowed = "SuggestedList"
    ' declarations
    Dim rngF As Range, rngA As Range
    Dim I As Integer, bOk As Boolean
    ' start
    If Application.Intersect(Range(ksTestRange), Target) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    Set rngF = Worksheets(ksWSLists).Range(ksForbidden)
    Set rngA = Worksheets(ksWSLists).Range(ksAllowed)
    ' process
    With Target
        bOk = True
        For I = 1 To rngF.Cells.Count
            If .Value Like "*" & rngF.Cells(I, 1).Value & "*" Then
            ' replace previous for next one, if instead of partial match you want a whole cell match
            'If .Value = rngF.Cells(I, 1).Value Then
                bOk = False
                Exit For
            End If
        Next I
        If I <= rngF.Cells.Count Then
            MsgBox "Use of <" & rngF.Cells(I, 1).Value & "> is forbidden; use <" & _
                rngA.Cells(I, 1).Value & "> instead.", _
                vbApplicationModal + vbExclamation + vbOKOnly, "Warning"
        End If
    End With
    ' end
    Set rngA = Nothing
    Set rngF = Nothing
End Sub

Didn't tested it, but you should give it a try anyhow. Just advise if any issue.

Regards!

 

[Melowshipslinki]

Works perfectly! Thank you so much!!! Testing out the procedures is an adrenaline rush haha

 

[SirJB7]

Hi, Melowshipslinki!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!