microsoft excel - VBA macro Enforcing specific input in a cell with a regular expression

I need to restrict the user when he tries to input some string in a cell, this input to be restricted to the following list: C1, C2, C3, C4, C5, C6, C7, C8, C9, C10 ; words: merge,complete framed,width,border left,border right and integers from 1 to 100 for example:
C6 merge 1, C4 merge 1 . No other values are allowed except these.

I still can`t find out the exact regex which must be used to include all the rules above. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim strPattern As String
Dim regEx As RegExp
Dim vValues As Variant
Dim vValue As Variant
Dim strInput As String
Dim currCell As Range
Dim MyRange As Range

Set MyRange = ThisWorkbook.Worksheets("BY Blocks").Range("G3:G308")
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
    strPattern = "the needed regex"
     'strPattern = "\b(C(?:10|[1-9])),(merge|complete framed|width),(\d+)"

    Set regEx = New RegExp

    vValues = Split(Target, ",")

    With regEx
     'For Each currCell In MyRange
           'If strPattern <> vbNullString Then
       ' strInput = currCell.Value
        'End If
              'Next currCell
        For Each vValue In vValues
            .Global = True
            .IgnoreCase = False
            .Pattern = strPattern

            If .Test(Trim(vValue)) Then
                MsgBox "Match found in " & Target.Value & " : " & Trim(vValue)
            Else
                MsgBox "No match"
            End If
     If (regEx.Execute(strInput)) Then
     '"smth when the pattern is matched"
     End If

     End With
     Set regEx = Nothing
     End If
      End Sub

Actually the string pattern is wrong because it doesn`t find a match in If .Test(Trim(vValue)) condition

Answer

Here is an approach that does not use Regex.

In a standard module enter this UDF()

Public Function IsItGood(aWord As Variant) As Boolean
    Dim s As String
    s = "|"
    tmp = s & aWord & s
    patern = ""

    For i = 1 To 100
        patern = patern & s & i
    Next i
    For i = 1 To 10
        patern = patern & s & "C" & i
    Next i
    patern = patern & s & "merge|complete framed|width|border left|border right" & s

    If InStr(1, patern, tmp) > 0 Then
        IsItGood = True
    Else
        IsItGood = False
    End If

End Function

In the worksheet code area enter: Private Sub Worksheet_Change(ByVal Target As Range) Dim BigS As String If Intersect(Range("G:G"), Target) Is Nothing Then Exit Sub arr = Split(Target, " ") For Each a In arr If IsItGood(a) Then Else MsgBox Target.Address(0, 0) & vbCrLf & a & vbCrLf & "has bad stuff" Application.Undo End If Next a End Sub

The event code takes phrase input from column G. It parses the phrase into words and make sure each word is a member of the pre-defined list.

EDIT#1:

The previous version of the event code allowed too many UnDos. Use this version instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim BigS As String

    If Intersect(Range("G:G"), Target) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    arr = Split(Target, " ")
    For Each a In arr
        If IsItGood(a) Then
        Else
            MsgBox Target.Address(0, 0) & vbCrLf & a & vbCrLf & "has bad stuff"
            Application.EnableEvents = False
                Application.Undo
            Application.EnableEvents = True
            Exit Sub
        End If
    Next a
End Sub

1. it will only validate if a single cell is changed


2. it handles cell clearing better


3. it limits the number of error messages


4. it removes the infinite loop cause by undo