How would I set a certain region of cells so that no
matter what case the user types it always appears or
changes to upper case? Any ideas would be greatly
appreciated. Thanks. Matt

You can use a functer UPPER()

oscar

Matt wrote in message
...
How would I set a certain region of cells so that no
matter what case the user types it always appears or
changes to upper case? Any ideas would be greatly
appreciated. Thanks. Matt

Hi Matt,
This code will change all text starting in cell D1 to
upper case. Adjust D1 to whatever your range is. This
will loop until the first blank cell in Col. D.

Sub trythis()
' Text already in Upper Case is ignored.

Range("D1").Select
Do Until ActiveCell = ""
ActiveCell = UCase(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

End Sub

-----Original Message-----
You can use a functer UPPER()

oscar

Matt wrote in
message
...
How would I set a certain region of cells so that no
matter what case the user types it always appears or
changes to upper case? Any ideas would be greatly
appreciated. Thanks. Matt


.

Be careful with this code example
If there is a formula in the range then it will be a value after
you run this macro.

Use this one
Look at the webpages also below the macro

Here is a Macro for changing text cells in the selection

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

See this webpages

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Eva Shanley" wrote in message ...
Hi Matt,
This code will change all text starting in cell D1 to
upper case. Adjust D1 to whatever your range is. This
will loop until the first blank cell in Col. D.

Sub trythis()
' Text already in Upper Case is ignored.

Range("D1").Select
Do Until ActiveCell = ""
ActiveCell = UCase(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

End Sub

-----Original Message-----
You can use a functer UPPER()

oscar

Matt wrote in
message
...
How would I set a certain region of cells so that no
matter what case the user types it always appears or
changes to upper case? Any ideas would be greatly
appreciated. Thanks. Matt


.

Ron,
Is there a way I can name the range in your example? That
way the user doesn't have to select the range and run the
macro. Thanks. Matt


-----Original Message-----
Be careful with this code example
If there is a formula in the range then it will be a
value after
you run this macro.

Use this one
Look at the webpages also below the macro

Here is a Macro for changing text cells in the selection

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," &
Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

See this webpages

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Eva Shanley"
wrote in message news:0c1a01c3d5f8$8f6ff270
...
Hi Matt,
This code will change all text starting in cell D1 to
upper case. Adjust D1 to whatever your range is. This
will loop until the first blank cell in Col. D.

Sub trythis()
' Text already in Upper Case is ignored.

Range("D1").Select
Do Until ActiveCell = ""
ActiveCell = UCase(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

End Sub

-----Original Message-----
You can use a functer UPPER()

oscar

Matt wrote in
message
...
How would I set a certain region of cells so that no
matter what case the user types it always appears or
changes to upper case? Any ideas would be greatly
appreciated. Thanks. Matt


.



.

Sure , example for b1:b10 on "Sheet1"
Look at David site also if you want to change the case in formulas also
http://www.mvps.org/dmcritchie/excel/proper.htm


Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Sheets("Sheet1").Range("b1:b10") _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Matt" wrote in message ...
Ron,
Is there a way I can name the range in your example? That
way the user doesn't have to select the range and run the
macro. Thanks. Matt


-----Original Message-----
Be careful with this code example
If there is a formula in the range then it will be a
value after
you run this macro.

Use this one
Look at the webpages also below the macro

Here is a Macro for changing text cells in the selection

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," &
Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

See this webpages

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Eva Shanley"
wrote in message news:0c1a01c3d5f8$8f6ff270
...
Hi Matt,
This code will change all text starting in cell D1 to
upper case. Adjust D1 to whatever your range is. This
will loop until the first blank cell in Col. D.

Sub trythis()
' Text already in Upper Case is ignored.

Range("D1").Select
Do Until ActiveCell = ""
ActiveCell = UCase(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

End Sub

-----Original Message-----
You can use a functer UPPER()

oscar

Matt wrote in
message
...
How would I set a certain region of cells so that no
matter what case the user types it always appears or
changes to upper case? Any ideas would be greatly
appreciated. Thanks. Matt


.



.

Matt

If just text is to be entered in these cells this will do the job when you hit
<ENTER or leave the cell......

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Range("a1:a20"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

This is worksheet_event code and must be placed into the worksheet module.

Right-click on sheet tab and "View Code". Paste in there.

Gord Dibben Excel MVP

On Thu, 8 Jan 2004 22:54:06 +0800, "oscar" wrote:

You can use a functer UPPER()

oscar

Matt wrote in message
...
How would I set a certain region of cells so that no
matter what case the user types it always appears or
changes to upper case? Any ideas would be greatly
appreciated. Thanks. Matt