PDA

View Full Version : [SOLVED] Error Message when value appears in a range



mykal66
10-30-2013, 02:46 AM
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
10-30-2013, 05:53 AM
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
10-30-2013, 07:37 AM
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