Forcing Cell Entries to be Upper Case

  • Thread starter Thread starter Guest
  • Start date Start date
Hi
you could use a custom validation. e.g. for cell A1 goto 'Data - Validation'
and enter the formula
=EXACT(A1,UPPER(A1))
 
Thanks Frank,
but that just gives an error if it's not uppercase. I really need to change
the entry to uppercase if someone enters it in lower. I think it may be
impossible without writing a macro to check after each entry and then
overwriting the entry.
Colin
 
Changing Case On Data Entry

In Excel97 and later versions, you can use the Worksheet_Change event
procedure to automatically change the case of the text when the data is
entered. In the sheet module for the worksheet that contains the cells you
want to have updated, enter the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

This will automatically change the case of the data when the user enters
data in the range A1:A10. Change this range to the range you need to use for
your application.

The code Application.EnableEvents = False prevents the Worksheet_Change
event from calling itself as it changes the value of the target cell.

You can modify this code as described in the previous section to force the
entered values to be in either lower or proper case.
 
I need to do the same thing. Where exactly do I put this code (I have Excel
2003)? I'm not sure how to find the "sheet module." And if I send the
worksheet to someone else will it stay with the worksheet? Pardon my
ignorance.
 
Right click the sheet tab of the sheet you want top use, select view code
and paste in the code.
Make sure you enable macros when you open the workbook


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)
 
Back
Top