Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing from lower to upper case
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing from lower to upper case
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing from lower to upper case
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing from lower to upper case
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing from lower to upper case
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 . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing from lower to upper case
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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing from lower to upper case
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing from lower to upper case
The macro Ron first supplied was a generic solution working from
a selection that the user makes before running the macro. The subsequent example used a specific range B1:B10 If you have a named range in your workbook, you can use it and it goes within the double quotes in the same manner. .Range("B1:B10") .RANGE("B:B") .RANGE("ZipStateRange"). The use of SpecialCells automatically limits the range to the usedrange. Reducing that selection to Text cells eliminates processing blank cells and formulas. Another method would be to use an Event macro, which would only apply to the one worksheet and would be automatic, which is what Gord supplied. FWIW, examples on my pages http://www.mvps.org/dmcritchie/excel/proper.htm#upper http://www.mvps.org/dmcritchie/excel....htm#uppercase Even if you use an Event macro you might want to also install the regular macro to fix up existing entries. Hopefully this is only for things like zip state codes that should be capitalized, rather than people's names. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "Matt" wrote ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing from lower case to upper case | Excel Discussion (Misc queries) | |||
Changing from upper case to lower case | Excel Discussion (Misc queries) | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
Changing upper case characters to upper/lower | Excel Discussion (Misc queries) | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) |