Can I force a case?

S

Selene

I allow my users to type Y, y, N or n as the answer to a yes or no question.
I want them to be able to type Yes or yes or just y and have it change to Y.
Can I do that?
 
F

Frank Kabel

Hi
this can only be done with VBA (using an event procedure). is this a
feasible way for you?
 
D

Dave Peterson

Maybe you could let them type what they want and you could use a helper cell to
make it do what you want:

=upper(left(a1,1))

In fact, you could just refer to that cell that way in your other formulas.

Another option is to use Data|validation.

Provide a list of: Y,N
They can use the dropdown to choose the format you want.
 
L

L. Howard Kittle

Hi Selene,

Take a look at Tools > Auto Correct. You can make y auto correct to Y and
yes correct to Y and y to Y. Do the same with the no's and No etc.

HTH
Regards,
Howard
 
D

Dave Peterson

But the autocorrect list is local to the user.

You could have the workbook_open/auto_open code add these to the autocorrection
list and then clean it up when you close the workbook.
 
T

tsarpoet

You can use Data>Validation function.
Under the function choose allow List & for source choose A1:A2 (
assume u have input Y for cell A1 and N for A2.

Hope it helps
 
D

Debra Dalgleish

If you use a worksheet list as the source it won't force the correct case.
As Dave suggested earlier, you can use a delimited list of Y,N in the
data validation dialog box, and only upper case will be accepted.
 
D

David McRitchie

Hi Selene,
The following is an Event Macro, installation differs from
regular macros. To install: right-click on sheet tab, then
View Code, insert the following code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 3 Then Exit Sub 'apply only to Column C
On Error goto ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

More information on Event procedures in
Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm
and specifically for Worksheet_change
http://www.mvps.org/dmcritchie/excel/event.htm#change

Other replies on use of UPPER Worksheet Function and
for validation do not match the request for an automatic change.

To change pre-existing entries for a selection with a regular macro, see
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
 
Top