Cells accept only specific values written

Question

I just started using vba and I was wondering if I can get some guidance here. I am trying to set the range a1:a50 to accept only numbers written like this: i.e. 111.111.111 or 231.432.212. I set this by using %%%.%%%.%%% as I read that % is defined as a number in VBA.

However, using this format %%%.%%%.%%% does not do the job. Can anyone guide me to fix this if it is possible? Must appreciated.

Dim cell As Range
     Application.EnableEvents = False
        For Each cell In Target
         If Not Application.Intersect(cell, Range("a1:a50")) Is Nothing Then
           If Not cell.Value = %%%.%%%.%%% Then
               cell.Interior.Color = RGB(0, 0, 255)

       Else

           cell.Interior.Color = RGB(1000, 1000, 1000)
           End If
        End If
   Next cell
    Application.EnableEvents = True

Phil · Accepted Answer · 2018-01-03T11:45:40.993

You could use RegEx to test the cell value. You could run the following macro to see how it works.

See here for how to set up RegEx in Excel.

Sub Test_Reg()
Dim regEx As New RegExp
Dim strPattern As String: strPattern = "[0-9]{3}.[0-9]{3}.[0-9]{3}"
'Note: if you want to exact match 9 numbers with two dots, 
'      then use strPattern = "^[0-9]{3}.[0-9]{3}.[0-9]{3}$"
strInput1 = "111.111.111"
strInput2 = "1234.1234.1234"
strInput3 = "12.12.12"
strInput4 = "12a.12a.12a"


    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With
    MsgBox (regEx.Test(strInput1))
    MsgBox (regEx.Test(strInput2))
    MsgBox (regEx.Test(strInput3))
    MsgBox (regEx.Test(strInput4))

End Sub

Then your code could be modified like this:

Dim regEx As New RegExp
Dim strPattern As String: strPattern = "[0-9]{3}.[0-9]{3}.[0-9]{3}"
'Note: if you want to exact match 9 numbers with two dots, 
'      then use strPattern = "^[0-9]{3}.[0-9]{3}.[0-9]{3}$"
With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = strPattern
End With
Dim cell As Range
     Application.EnableEvents = False
     For Each cell In Target
         If Not Application.Intersect(cell, Range("a1:a50")) Is Nothing Then
           If (NOT regEx.Test(cell.Value)) Then
               cell.Interior.Color = RGB(0, 0, 255)

       Else

           cell.Interior.Color = RGB(1000, 1000, 1000)
           End If
        End If
   Next cell
   Application.EnableEvents = True

Cells accept only specific values written

1 Answers1