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

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.

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

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



"Doug Waters 03/03/08" wrote in
message ...
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.

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

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

"Doug Waters 03/03/08" wrote:

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.

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

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" wrote in message
...
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

"Doug Waters 03/03/08" wrote:

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.

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

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)" wrote:

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" wrote in message
...
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

"Doug Waters 03/03/08" wrote:

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.

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

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

Rick


"Mike H" wrote in message
...
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)" wrote:

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" wrote in message
...
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

"Doug Waters 03/03/08" wrote:

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.

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

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.

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

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

Rick


"Doug Waters 03/03/08" wrote in
message ...
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.

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

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

"Rick Rothstein (MVP - VB)" wrote:

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

Rick

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

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

"Rick Rothstein (MVP - VB)" wrote:

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



"Doug Waters 03/03/08" wrote in
message ...
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.

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

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 InsertModule. 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 ToolMacroMacros.

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


Gord Dibben MS Excel MVP

On Wed, 6 Aug 2008 08:25:01 -0700, 116
wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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



"Doug Waters 03/03/08" wrote in
message ...
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.

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

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" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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



"Doug Waters 03/03/08" wrote
in
message ...
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.

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

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

Thanks
David

"Gord Dibben" wrote:

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 InsertModule. 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 ToolMacroMacros.

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


Gord Dibben MS Excel MVP

On Wed, 6 Aug 2008 08:25:01 -0700, 116
wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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



"Doug Waters 03/03/08" wrote in
message ...
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.

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

Never even saw that<g


Gord

On Wed, 6 Aug 2008 17:35:40 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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



"Doug Waters 03/03/08" wrote
in
message ...
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.