Excel: how to convert text to upper case upon entry?

  • Thread starter Thread starter Doug Waters 03/03/08
  • Start date Start date
D

Doug Waters 03/03/08

When I enter text data into an Excel cell, how can I get it to automatically
be converted to upper case when I hit the Enter or Tab key? Yes, I know about
the UPPER function - the problem is I want the data to be converted to upper
case in the SAME CELL I just entered it into as lower case text.

Thanks for any help that anyone can provide.
 
Right-click the worksheet you want this functionality on, select View Code
from the popup menu that appears and copy/paste the following into the code
window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Not Intersect(Target, Range("A:B")) Is Nothing Then
Target.Value = UCase(Target.Value)
End If
Whoops:
Application.EnableEvents = True
End Sub

Change my Columns A and B reference in my Range("A:B") example to whatever
range of cells you want this functionality for.

Rick
 
To avoid changing formula into values I would suggest you ammend the line in
Rick's code to this

If Not Target.HasFormula Then Target.Value = UCase(Target.Value)

Mike
 
The OP said "When I enter text data into an Excel cell", so I assumed that
if he is *entering* text, there would not be a formula to worry about.

Rick
 
Rick,

I read it slightly differently because the OP didn't exclude the possibility
of a formula in the range. I think you might agree anyway it would be good
practice.

Mike
 
Nope, it couldn't hurt to do it your way.

Rick


Mike H said:
Rick,

I read it slightly differently because the OP didn't exclude the
possibility
of a formula in the range. I think you might agree anyway it would be good
practice.

Mike
Click to expand...
 
Rick & Mike:

I'm a bit of a newbie at this, so I'll have to study your answers, but
thanks very much for your prompt help.
 
If anything is unclear, feel free to post back to this thread and ask for
clarification.

Rick
 
Hi, Rick & Mike -

Since you guys are so expert on this, could you recommend a GOOD website
where I can learn Visual Basic? The sites I've found aren't all that great.
I'd like something that approaches the subject like a college course, but
doesn't get TOO bogged down in minutiae.

Thanks,
Doug
 
I have tried a number of different scripts about converting to UPPER Case,
but with no luck. I am running Excel 2003. Not sure what the problem might
be. Besides me.

David
 
Read Rick's post carefully.

Make sure you have pasted his code into the sheet module.

Edit the range to suit.

If you just want a macro to run after the fact............no event code.

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Copy/paste into a General module.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP
 
I see I left out one word from my description that might have led you
astray. The first sentence I posted should have said...

Right-click the worksheet TAB you want this functionality on....

Rick
 
Thank you for the assist. Workbook was opening with Macros Disabled. The
weblink was a great help.

Thanks
David
 
Never even saw that<g>


Gord

I see I left out one word from my description that might have led you
astray. The first sentence I posted should have said...

Right-click the worksheet TAB you want this functionality on....

Rick
Click to expand...
 
Back
Top