"Sandy Mann" wrote in message

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

You'll want an Application.EnableEvents = False/True in that code. If you
dont' turn off Events, the _Change code will make a change, which will call
_Change, which will make a change, which will call _Change, and on and on
and on until VBA just quits (earlier versions of Excel would blow up with an
"out of stack space" error). Also, you'll want to test whether Target has a
Formula in it. If it does, you don't want to overwrite the formula with the
value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.HasFormula = False Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Text, vbUpperCase)
Application.EnableEvents = True
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Sandy Mann" wrote in message
...
Al, or should I say AL <g

you could use a sheet event macro to change the case:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value = UCase(Target.Value)

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"gramps" wrote in message
...
I work a 4 week rota which entails 4 different duties, any 5 days from
seven.
At the end of that time I produce a duty statement detailing which duty
numbers were worked on what days. The duty number is in the form of
'AB1'.
How can I ensure that even if I enter the duty number as 'ab1' it will be
automatically be entered in upper case format.
Many thanx
--
Al