View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default A validation rule on Alpha and Numeric characters

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Or Target.Value = "" Then Exit Sub
If Not Target.Value Like "[A-Za-z][A-Za-z][A-Za-z]####" Then
MsgBox "Wrong Format!"
Target.Value = ""


Very bad! At least use Application.Undo rather than setting the target
cell's value to "", which can't be undone.


If, according to the OP, the entry is incorrect and not acceptable, why
would it matter that it is permanently erased or undone?

Also, Target COULD be a multiple cell range, which would
make it an even worse idea to set all cells to "".


I just tried the macro against a larger selection and multiple individual
selections and the only cell that was erased was the active cell. I'm using
Excel 2003 if that makes a difference. Does this code work differently in
Excel 2003 than other (earlier?) versions?


Target.Activate
End If
End Sub

...

Event handlers only work when macros are enabled, and it's simple for
users to disable macros. Data validation ALWAYS works.


That is a valid point and I can't argue with it at all. I would note,
though, that company policy might make disabling macros on company
spreadsheets a thing not to do, so the OP would have to decide if a macro
solution is usable for the intended purposes or not. By the way, the reason
I proposed a macro was due to, perhaps, a too strong reading of the words
"prevents me using any other format" and not paying enough attention to the
words "indicating an error message" in the OP's original post.

Rick