Can I force a case?

  • Thread starter Thread starter Selene
  • Start date Start date
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?
 
Hi
this can only be done with VBA (using an event procedure). is this a
feasible way for you?
 
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.
 
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
 
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.
 
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
 
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.
 
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
 
Back
Top