Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A validation rule on Alpha and Numeric characters
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, 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. 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. Thank you again... I will look into this. 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. <g Rick |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A validation rule on Alpha and Numeric characters
"Rick Rothstein \(MVP - VB\)" wrote...
.... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a cell w/Alpha numeric characters in mulplication formula | Excel Worksheet Functions | |||
Formulas that include alpha and numeric characters | Excel Worksheet Functions | |||
Can you ID a cell that has both Alpha AND Numeric characters? | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Count unique alpha numeric "characters" in a common cell | Excel Worksheet Functions |