Upper case only entries

[Guest]

Hi - happy new year to one and all from Jane in Portbury, near Bristol,
England.

I have XP with Office 2000, is there any way to ensure entries into a column
appear as upper case only. The entries are Y, N or N/A but I need this to
only be upper case only, and as always you can request this, but it is not
always so.

Thanks if you can help.
xJane

 

[Bob Phillips]

One way

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim nCount As Long
Dim nMax As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 2 Then
With Target
.Value = UCase(.Value)
If .Value <> "Y" And .Value <> "N" And .Value <> "N/A" Then
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Roger Govier]

Hi Jane
As an alternative to Bob's code solution, (but not as robust), you could
use Data Validation.
Mark the group of cells where entries are to take place.
Data>Validation>use dropdown to select List in the white pane marked
Allow enter Y,N,N/A
You can choose whether or not you want this to appear as a dropdown
choice in the cell.
On the Error Alert Tab, type a message to say "You may only enter
uppercase values Y or N or N/A"

You could also repeat this on the .Input Message tab if you want, and
the user's will see it as they hover over the cell.