Upper case only entries
 

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

Upper case only entries
 

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)

"JWF" wrote in message
...
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

Upper case only entries
 

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.
DataValidationuse 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.

--
Regards

Roger Govier



JWF wrote:
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