Hi all,

Does anyone know of a way I can force cell contents to appear in upper
case? I'm tidying up a worksheet that is completed by many users and
one of the columns is for a reference code which has three letters and
six numbers (ABC123456). Some users leave it in lower case, and the
resulting sheet looks a mess.

Can I get Excel to always display this column in upper case regardless
of what the user inputs?

Trevor


--
Trevor Coultart
------------------------------------------------------------------------
Trevor Coultart's Profile: http://www.excelforum.com/member.php...o&userid=24945
View this thread: http://www.excelforum.com/showthread...hreadid=384807

Trevor,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
.Value = UCase(.Value)
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

"Trevor Coultart"
<Trevor.Coultart.1rqt2g_1120644331.6361@excelfor um-nospam.com wrote in
message news:Trevor.Coultart.1rqt2g_1120644331.6361@excelf orum-nospam.com...

Hi all,

Does anyone know of a way I can force cell contents to appear in upper
case? I'm tidying up a worksheet that is completed by many users and
one of the columns is for a reference code which has three letters and
six numbers (ABC123456). Some users leave it in lower case, and the
resulting sheet looks a mess.

Can I get Excel to always display this column in upper case regardless
of what the user inputs?

Trevor


--
Trevor Coultart
------------------------------------------------------------------------
Trevor Coultart's Profile:
http://www.excelforum.com/member.php...o&userid=24945
View this thread: http://www.excelforum.com/showthread...hreadid=384807

=UPPER("abc")
-- ABC
=PROPER("abc")
-- Abc
=LOWER("aBc")
--abc

Ola Sandström


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384807

a helper column with =UPPER(H1) abc456 in cell H1 was converted to ABC456 in
my helper cell.....
--
paul
remove nospam for email addy!



"Trevor Coultart" wrote:


Hi all,

Does anyone know of a way I can force cell contents to appear in upper
case? I'm tidying up a worksheet that is completed by many users and
one of the columns is for a reference code which has three letters and
six numbers (ABC123456). Some users leave it in lower case, and the
resulting sheet looks a mess.

Can I get Excel to always display this column in upper case regardless
of what the user inputs?

Trevor


--
Trevor Coultart
------------------------------------------------------------------------
Trevor Coultart's Profile: http://www.excelforum.com/member.php...o&userid=24945
View this thread: http://www.excelforum.com/showthread...hreadid=384807

Several ways:
http://www.officearticles.com/excel/...ft_exce l.htm
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Trevor Coultart"
<Trevor.Coultart.1rqt2g_1120644331.6361@excelfor um-nospam.com wrote in
message news:Trevor.Coultart.1rqt2g_1120644331.6361@excelf orum-nospam.com...

Hi all,

Does anyone know of a way I can force cell contents to appear in upper
case? I'm tidying up a worksheet that is completed by many users and
one of the columns is for a reference code which has three letters and
six numbers (ABC123456). Some users leave it in lower case, and the
resulting sheet looks a mess.

Can I get Excel to always display this column in upper case regardless
of what the user inputs?

Trevor


--
Trevor Coultart
------------------------------------------------------------------------
Trevor Coultart's Profile:
http://www.excelforum.com/member.php...o&userid=24945
View this thread: http://www.excelforum.com/showthread...hreadid=384807

Many thanks for the responses. I've used a combination: "upper" to
convert all the existing entries and Bob's magic formula to ensure that
it stays looking tidy. (Bob - thanks. I have not the faintest idea how
that works but it does.)

Trevor


--
Trevor Coultart
------------------------------------------------------------------------
Trevor Coultart's Profile: http://www.excelforum.com/member.php...o&userid=24945
View this thread: http://www.excelforum.com/showthread...hreadid=384807