Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 11th 07, 11:59 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 1
Default A validation rule on Alpha and Numeric characters

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*


  #2   Report Post  
Old June 11th 07, 04:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,886
Default A validation rule on Alpha and Numeric characters

Hi Sar

Use Data ValidationCustom and array entered formula of

{=AND(SUM(CODE(MID(A1,ROW(INDIRECT("1:"&3)),1)))1 94,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.

--
Regards

Roger Govier


"Sar*" wrote in message
...
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*



  #3   Report Post  
Old June 11th 07, 04:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 2,202
Default A validation rule on Alpha and Numeric characters

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

  #4   Report Post  
Old June 11th 07, 04:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 751
Default A validation rule on Alpha and Numeric characters

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

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

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

HTH
Kostis Vezerides

On Jun 11, 1:59 pm, Sar* wrote:
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*



  #5   Report Post  
Old June 11th 07, 06:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 1,231
Default A validation rule on Alpha and Numeric characters

Sar* wrote...
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),"ABCDEFGHIJK LMNOPQRSTUVWXYZ"),
SEARCH(MID(X99,ROW(4:7),1),"0123456789"))=7,LEN(X9 9)=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.



  #6   Report Post  
Old June 11th 07, 06:54 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 1,231
Default A validation rule on Alpha and Numeric characters

"Rick Rothstein \(MVP - VB\)" wrote...
....
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.

  #7   Report Post  
Old June 11th 07, 06:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 1,231
Default A validation rule on Alpha and Numeric characters

Harlan Grove wrote...
....
were numeric using --ISNUMBER(A1,4). . . .

....

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

  #8   Report Post  
Old June 11th 07, 07:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
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

  #9   Report Post  
Old June 11th 07, 10:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 1,231
Default A validation rule on Alpha and Numeric characters

"Rick Rothstein \(MVP - VB\)" wrote...
....
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?

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?

....

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.

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


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.

  #10   Report Post  
Old June 11th 07, 10:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 2,202
Default A validation rule on Alpha and Numeric characters

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a cell w/Alpha numeric characters in mulplication formula MAJ0116 Excel Worksheet Functions 4 February 10th 07 01:07 AM
Formulas that include alpha and numeric characters Doktor1 at Mile High Excel Worksheet Functions 2 December 23rd 06 07:36 PM
Can you ID a cell that has both Alpha AND Numeric characters? Phil Excel Worksheet Functions 5 April 18th 06 09:32 PM
The colums changed from alpha to numeric how do you make it alpha worldmade Excel Discussion (Misc queries) 2 May 26th 05 03:44 PM
Count unique alpha numeric "characters" in a common cell WIM4246 Excel Worksheet Functions 4 December 11th 04 02:27 AM


All times are GMT +1. The time now is 11:35 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017