My boss has a large (very large) spreadsheet that contains lower case
characters for serial numbers, mixed lower case and numbers (0-9). She is
looking for a way to type lower case letters into Excel and them have them
automatically changed to upper case without disturbing the numbers. Is there
any formula to do this? Example: Cell A1 contains the serial number "a1b9ced"
she want to type this lower case and when she hits enter it is automaticall
converted to the upper case "A1B2CED". Can this be done?
--
Clark

Right click sheet tabview codeinsert thismodify to suit your rangeSAVE
workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Range("j1:j21")) Is Nothing Then
Target = UCase(Target)
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Clark" wrote in message
...
My boss has a large (very large) spreadsheet that contains lower case
characters for serial numbers, mixed lower case and numbers (0-9). She is
looking for a way to type lower case letters into Excel and them have them
automatically changed to upper case without disturbing the numbers. Is
there
any formula to do this? Example: Cell A1 contains the serial number
"a1b9ced"
she want to type this lower case and when she hits enter it is
automaticall
converted to the upper case "A1B2CED". Can this be done?
--
Clark

With VBA:

Right click on worksheet tab, "View code" and copy/paste code below which
checks for entries in column A. Change as required


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A2:A1000" '<=== change


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Value = UCase(Target.Value)
End If
ws_exit:
Application.EnableEvents = True
End Sub

"Clark" wrote:

My boss has a large (very large) spreadsheet that contains lower case
characters for serial numbers, mixed lower case and numbers (0-9). She is
looking for a way to type lower case letters into Excel and them have them
automatically changed to upper case without disturbing the numbers. Is there
any formula to do this? Example: Cell A1 contains the serial number "a1b9ced"
she want to type this lower case and when she hits enter it is automaticall
converted to the upper case "A1B2CED". Can this be done?
--
Clark

Here is some event code for the worksheet.

Works on columns 1 through 8.............edit to suit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

As you enter text in any cell it will change to CAPS.

Will not change existing text unless you F2ENTER on those but will change all
new text entries.

If you want a macro to change all existing text to UPPER case try this one.

Sub Upper_Case()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 24 Jul 2007 12:10:01 -0700, Clark
wrote:

My boss has a large (very large) spreadsheet that contains lower case
characters for serial numbers, mixed lower case and numbers (0-9). She is
looking for a way to type lower case letters into Excel and them have them
automatically changed to upper case without disturbing the numbers. Is there
any formula to do this? Example: Cell A1 contains the serial number "a1b9ced"
she want to type this lower case and when she hits enter it is automaticall
converted to the upper case "A1B2CED". Can this be done?

Thanks. worked like a charm.

Also, thanks to all answered.
--
Clark


"Gord Dibben" wrote:

Here is some event code for the worksheet.

Works on columns 1 through 8.............edit to suit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

As you enter text in any cell it will change to CAPS.

Will not change existing text unless you F2ENTER on those but will change all
new text entries.

If you want a macro to change all existing text to UPPER case try this one.

Sub Upper_Case()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 24 Jul 2007 12:10:01 -0700, Clark
wrote:

My boss has a large (very large) spreadsheet that contains lower case
characters for serial numbers, mixed lower case and numbers (0-9). She is
looking for a way to type lower case letters into Excel and them have them
automatically changed to upper case without disturbing the numbers. Is there
any formula to do this? Example: Cell A1 contains the serial number "a1b9ced"
she want to type this lower case and when she hits enter it is automaticall
converted to the upper case "A1B2CED". Can this be done?