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

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Mike H]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Mike H]

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[Doug Waters 03/03/08]

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.

 

[Rick Rothstein \(MVP - VB\)]

If anything is unclear, feel free to post back to this thread and ask for
clarification.

Rick

 

[Doug Waters 03/03/08]

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

 

[116]

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

 

[Gord Dibben]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[116]

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

Thanks
David

 

[Gord Dibben]

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