You can use Data Validation to force your users to enter text in upper case. Select the cell where you want to force upper case and select Validation from the Data menu. Assuming you selected cell A3, make your validation screens look like the pictures below. I don’t have any input message, but you certainly could. If you use a cell other than A3, be sure to change your cell references in the formula.
This is very cool, thanks.
Hi Dick
I must admit I am not seeing a use for this – maybe I’m not thinking broadly enough, but what advantage does validation have over, say, a worksheet change event that would automatically convert whatever was typed in to uppercase? Fair enough that the validation route alerts the inputter that it has to be in capitals, but I would have thought it would just get annoying whereas the worksheet change would avoid interruption.
Best regards, Richard
The advantage that the data validation method has has over a worksheet change event is that you know it will work. The user can easily disable macros when the workbook is opened. And, I’m afraid, that’s going to be increasingly common if Excel 2007 ever becomes mainstream. Judging by the warnings that are displayed, a novice user will quickly form the impression that macros are a huge security risk and they should be disabled.
That said, I prefer this data validation formula:
=NOT(EXACT(A1,UPPER(A1)))
Accompanied by this Error Alert:
“Turn off your CAPS LOCK you moron!”
-John
A lot easier — and a lot more user friendly, don’t you think? — would be to enter, in the cell where the content must be upcased, the formula =UPPER(data-entry-cell)
Hi John
Just after I posted my message, I decided that turning off macros could be a possible issue, but what I said about being annoying still stands. I think Tushar’s solution is by far the best. I know having validation for uppercase would quickly irritate me, and lead me to either avoid using or to modify said validation.
Best regards, Richard
Richard, Tushar,
I created journal entry templates for our accounting department that have many restrictions: no decimals (validation criteria: allow integer), some cells blank and some that can’t be blank, and no extra columns (no helper columns). The journal entry will be imported to a mainframe, and variations from exact form will crash the process. While I thought the input messages and error warnings were annoying, Accounting loves them and the double-checking data validation eliminates.
Brett
Brett
I’m not saying validation has no uses, and you raise a couple of situations where it is of great use (even essential): what I’m saying is Capitalisation checking through validation would be a pain, and could easily be avoided by using Tushar’s formula. I’m not applying my argument to other situations.
Richard
I am not saying that this has no use (actually it is kinda cool and in limited circumstances it will be handy) but as a general rule I tend to avoid requiring my users to do something that I can handle from my end. If I require data to be upper case then somewhere in my business logic layer I will make the conversion. The end user only sees the user interface I give them and the business logic is handled in hidden sheets (or possible it is handled directly on a report). By keeping the User Interface seperate from the business logic, reporting and display side of things I can minimize the amount of restrictions I have to place on the end user. I have found that this greatly increases user acceptance. I know that I get kinda frustrated when I am entering data and there are a bunch of different rules that I need to follow. My motto is that I can either train 1,000 user how to do it right or I can design a system that they can’t get wrong. I have rarely had a luck training the 1,000 users…
Amen to that, Jim. “You did something wrong and I know exactly what it is but you fix it yourself” prompts are turnoffs.
“I have rarely had a luck training the 1,000 users…”
… or even 1.
I’m with Jim on this one. I would sooner UCase the variable once I take it off the location it’s input to, than nag the user, because they’ll just whine about it. Plus this way I can astound and amaze them by making “John” and “JOHN” the same thing. Hey, they’re easily impressed….!
As for training users? Nah, the best you can hope for is to minimise the amount of bad data they put through…. ;-)
It’s always easier and better for a developer to separate the business logic of any application from the presentation layer. A good programmer should expect the end users to input data wrongly (from time to time) cos they are not infallible and so if the business logic is separated from the presentation layer then there can be a form of friendly filter (which validates the inputs from the end user and corrects errors that can be fixed without involving the end user, it is usually the best way to go, else you irritate them with the various nagging prompts (like a desperate housewife), and some of them could really sound stupid and dumb to the user…. cos the user will expect the program to be able to correct such simple errors as capitalisation… So I’m also with Jim on this, and will not even attempt to train 1,000 users as a way of avoiding the end user’s possibility of inputting lowercase instead of uppercase..
The keynote is that our programs should be more user-friendly, error-tolerant and robust (and at the same time, not compromising the standards or rules/requirements of the domain we are developing the application for).
Regards,
David
Thanks for the help, I took the concept a little furhter once I had the help.
First position must be Uppercase ‘A’, next 6 must be numeric, only 7 positions.
=AND(LEN(B6)=7,UPPER(LEFT(B6,1)=”A”),ISNUMBER(VALUE(MID(B6,2,6))))
Thanks again.