Error Message when value appears in a range

[mykal66]

HiI need to be able to validate the format of a UK National Insurance number then have an error 'Message Box' advise the user the format is wrong before clearing the incorrect value and setting the focus back to the cell that needs re-entered.I've found a formula that validates the format and gives TRUE & FALSE values in a helper cell (Column B), then I used an If Statement in another helper cell (Column C) to give Correct & Incorrect values if an entry has been made but the format is wrong.I've tried to get a message box to appear if 'Incorrect' appears in Column C but can only get it to work if I reference an individual cell rather than any cell in a range.I have created an example spreadsheet but i am unable to attach it for some reason, the manage attachment button doesn't do anything!In the example the National Insurance Number is eneter into A1, the format is validated in B1 and C1 checks that both A1 has an entry and B1 is True to provide correct / incorrect valuesCan anyone help please?As always help from you guys is really appreciatedThanksMykal

[Paul_Hossler]

I do not know the correct number format or the range, but put this into the correct worksheet's code module and adjust to suit

I don't personally like 'helper' cells so this just pops up a message box

Option Explicit
Const csCheckRange = "A1:E20"                   '   change
Const csNationalNumber = "###-#####-??[A-M]"    '   change

'Online help for the Like operator
'   Characters in pattern   Matches in string
'       ?                   Any single character.
'       *                   Zero or more characters.
'       #                   Any single digit (0–9).
'       [charlist]          Any single character in charlist.
'       [!charlist]         Any single character not in charlist.


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rCell As Range

    If Intersect(Target, Range(csCheckRange)) Is Nothing Then Exit Sub
    Target.Interior.Color = vbGreen
    For Each rCell In Target.Cells
        If Not rCell.Value Like csNationalNumber Then
            Application.EnableEvents = False
            rCell.Interior.Color = vbRed
            Call MsgBox("Data in " & rCell.Address & " must be in format " & csNationalNumber, vbCritical + vbOKOnly, "Format Check")
            rCell.ClearContents
            rCell.Select
            Application.EnableEvents = True
            Exit Sub
        End If
    Next
End Sub

Attachments here seem to be finicky. The only reliable way I've found to to go Advanced, Add Files, and select Basic Uploader from the little Browse box. Doing Add Files just seems to not do anything

Paul

[mykal66]

Hi PaulThank you for helping me with this - I've adapted it to UK Nat Ins Num format and it works brilliantly and much easier than two helper columns too.Really appreciate your helpThanks againM