A validation rule on Alpha and Numeric characters

[Sar*]

Hey,

I want to create a validation that when i enter a mixed numeric and alpha
reference that i can prevent changing its format.

For example:

Everytime i enter my "number" FEE1234, that the cell keeps the formatting
"@@@####" and prevents me using any other format indicating n error message.

Cheers!
Thanx in advance
Sar*

 

[Roger Govier]

Hi Sar

Use Data> Validation>Custom > and array entered formula of

{=AND(SUM(CODE(MID(A1,ROW(INDIRECT("1:"&3)),1)))>194,ISNUMBER(--(RIGHT(A1,4))))}

To Enter or Edit an Array formula, use Control Shift Enter (CSE) not
just Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel
will enter these for you.

On the Error Alert tab type a message for the use showing the type of
entry you expect.

 

[Rick Rothstein \(MVP - VB\)]

I want to create a validation that when i enter a mixed numeric and alpha

reference that i can prevent changing its format.

For example:

Everytime i enter my "number" FEE1234, that the cell keeps the formatting
"@@@####" and prevents me using any other format indicating n error
message.

Assuming that @@@ means the first 3 characters are either upper or lower
case **letters** from the alphabet only, does this Worksheet Macro do what
you want?

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 = ""
Target.Activate
End If
End Sub

where you would change the number 1 in 'Target.Column <> 1' to the column
number you want to filter (and, of course, change the MessageBox message
also).

Rick

 

[vezerid]

Use the following *array* formula in Data Validation (Custom)

=PRODUCT((CODE(MID(A1,ROW(1:3),1))>64)*(CODE(MID(A1,ROW(1:3),
1))<=91))*ISNUMBER(--RIGHT(A1,4))*(LEN(A1)=7)

Use Shift+Ctrl+Enter instead of just clicking OK.

HTH
Kostis Vezerides

 

[Harlan Grove]

I want to create a validation that when i enter a mixed numeric
and alpha reference that i can prevent changing its format.

....

The most general approach is something like

=AND(COUNT(SEARCH(MID(X99,ROW(1:3),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),
SEARCH(MID(X99,ROW(4:7),1),"0123456789"))=7,LEN(X99)=7)

Two of the other suggestions checked that the 4 through 7 characters
were numeric using --ISNUMBER(A1,4). First, the numeric conversion
requires only one unary minus, and second, this would blissfully
accept such strings as

1E12
-300
28.5
-2.5
33.%
7/12

which is probably not what you want. There are times when simple beats
clever.

 

[Harlan Grove]

...
....

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. Also, Target COULD be a
multiple cell range, which would make it an even worse idea to set all
cells to "".

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.

 

[Harlan Grove]

were numeric using --ISNUMBER(A1,4). . . .

....

Screwed that up. I should have written ISNUMBER(--RIGHT(A1,4)).

 

[Rick Rothstein \(MVP - VB\)]

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?

...

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

 

[Harlan Grove]

...
....

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

Because the PREVIOUS entry is likely to have been valid, and it may be
preferable to preserve an outdated valid entry than to change the
entry to "" (which isn't the same as clearing the cells contents -
that involves setting the FORMULA property to "" - setting the VALUE
property to "" makes the cell nonblank containing a zero-length
string).

I also just noticed that you made a common mistake: you failed to set
Application.EnableEvents to FALSE before and TRUE after modifying
Target, which would otherwise cause the event handler to trigger
itself. You didn't test your code?

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?

....

You have to ENTER multiple cells at once, e.g., array formulas or
multiple cell entries using [Ctrl]+[Enter] or PASTING into a multiple
cell range. Maybe the first is unlikely since users wouldn't be
entering array formulas, the second unlikely because users wouldn't
know about it, but the third is quite possible, and you should learn
to accommodate it.

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, . . .

And some companies have policies of no macros AT ALL, making event
handlers of purely academic interest.

Anyway, if you're going to propose event handlers, you should address
means of discouraging users from disabling macros.

 

[Rick Rothstein \(MVP - VB\)]

I want to create a validation that when i enter a mixed numeric and alpha

reference that i can prevent changing its format.

For example:

Everytime i enter my "number" FEE1234, that the cell keeps the formatting
"@@@####" and prevents me using any other format indicating n error
message.

Another possibility, with special note to Harlan's remark in his reply to my
other post in this sub-thread, namely,

"Event handlers only work when macros are enabled,
and it's simple for users to disable macros"

is to create your own format checker via a macro. Here is a function macro
that should be usable which will expose VBA's Like operator for use in a
worksheet. Right click the tab at the bottom of the sheet, select "View
code", add a Module to the worksheet and put this code in it...

Public Function IsLike(R As Range, Pattern As String) As Boolean
Dim C As Range
IsLike = True
For Each C In R
If Not C.Value Like Pattern Then
IsLike = False
Exit Function
End If
Next
End Function

For your stated need, just put the following into a helper column starting
at the first cell you want to check and then copy down...

=IsLike(A1,"[A-Z][A-Z][A-Z]####")

Of course, replace the A1 with the actual cell reference you want to check.

Rick

 

[Rick Rothstein \(MVP - VB\)]

If, according to the OP, the entry is incorrect and not acceptable,

Because the PREVIOUS entry is likely to have been valid,

Ah, very good thinking.

I also just noticed that you made a common mistake: you failed to set
Application.EnableEvents to FALSE before and TRUE after modifying
Target, which would otherwise cause the event handler to trigger
itself. You didn't test your code?

Actually, I did test the code and didn't see a problem, but I am sure there
are conditions where what you are pointing out will require what you posted;
so thank you for noting that for me... I really do appreciate it.

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?

You have to ENTER multiple cells at once, e.g., array formulas or
multiple cell entries using [Ctrl]+[Enter] or PASTING into a multiple
cell range. Maybe the first is unlikely since users wouldn't be
entering array formulas, the second unlikely because users wouldn't
know about it, but the third is quite possible, and you should learn
to accommodate it.

Thank you again... I will look into this.

And some companies have policies of no macros AT ALL, making event
handlers of purely academic interest.

<g>


Rick

 

[Harlan Grove]

...
....

Another possibility, with special note to Harlan's remark in his
reply to my other post in this sub-thread, namely,

"Event handlers only work when macros are enabled,
and it's simple for users to disable macros"

is to create your own format checker via a macro. Here is a
function macro that should be usable which will expose VBA's Like
operator for use in a worksheet. . . .

....

udfs are also disabled when macros are disabled.

udfs are also slow due to the Excel/VBA interface. If you're going to
incur udf overhead, better to go for something more useful than VBA's
Like operator, e.g., the VBScript RegExp (regular expression) object
for which there are many examples in the Excel newsgroups.