Forcing an entry

G

goldcomac

I swear I've done it before, but I can't remember how.

Can't I force a cell to take a Y or N as opposed to a number (or any
other character, for that matter)? Also, can I guarantee that the Y or
N is capitolized?

Thank you.

Chip
 
J

Jim Thomlinson

Select Data -> Validation -> List and in the source place Y, N. The default
for the error alert is Stop which will only allow Capial Y or N...
 
G

goldcomac

Jim, thanks for this. I got it to work. This kind of dummy-proofs the
cell so that only a capitol Y or N can be used, especially when I use
the Input Message or Error Alert.

Let's assume that the range D1:D100 clearly needed a Y or N. No other
option would make sense to the user. If a user enters a lower case y,
is there a way that once he hits enter it gets changed to a capitol
letter? I know I can use =UPPER(D1:D100), but that only works in a
different column.

Chip
 
G

Gord Dibben

If you are using a DV dropdown box on every cell there would be no need for any
change because the user is seleting from the DV dropdown.

If the range D1:D100 "clearly" makes sense that a Y or N is to be entered you
could use event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Me.Range("D1:D100")) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

This is sheet event code.

Right-click on the sheet tab and copy/paste into that module.

ALT + q to go back to your worksheet

Type in a lower case "y" and will become upper case.


Gord Dibben MS Excel MVP
 
Top