Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert lower to upper case automatically without using UPPER
Is there a way to autoformat a range of cells to always display as upper case
even if lower case if entered in the cell? Is there a shortcut to convert text from lower to upper case? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert lower to upper case automatically without using UPPER
Touch the CAPSLOCK key prior to typing.
-- Gary''s Student - gsnu200811 "Sal" wrote: Is there a way to autoformat a range of cells to always display as upper case even if lower case if entered in the cell? Is there a shortcut to convert text from lower to upper case? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert lower to upper case automatically without using UPPER
the best way is to use UPPER. then copy the results paste back to the
original the original location use PasteSpecial with Value selected to perminantly change the original cells. "Sal" wrote: Is there a way to autoformat a range of cells to always display as upper case even if lower case if entered in the cell? Is there a shortcut to convert text from lower to upper case? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert lower to upper case automatically without using UPPER
Hi Sal
I don't know of any easy way to force upper case in your sheet (although the good members of this community will probably come back with a really wizzy bit of VBA) but to convert text in column A to upper case, use a helper column (B) and the function =upper(A1) in cell B1. Copy down, then use Copy, Paste Special, Values to overwrite column A with the correct case. "Sal" wrote: Is there a way to autoformat a range of cells to always display as upper case even if lower case if entered in the cell? Is there a shortcut to convert text from lower to upper case? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert lower to upper case automatically without using UPPER
For an automatic solution, right click your sheet tab, view code and psate
this in Private Sub Worksheet_Change(ByVal Target As Range) Target.Formula = UCase(Target.Formula) End Sub Mike "Sal" wrote: Is there a way to autoformat a range of cells to always display as upper case even if lower case if entered in the cell? Is there a shortcut to convert text from lower to upper case? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert lower to upper case automatically without using UPPER
You cannot autoformat but you could use event code.
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 Right-click on your sheet tab and "View Code". Copy/paste into that module. Edit the range to suit. Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Fri, 31 Oct 2008 05:46:14 -0700, Sal wrote: Is there a way to autoformat a range of cells to always display as upper case even if lower case if entered in the cell? Is there a shortcut to convert text from lower to upper case? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert lower to upper case automatically without using UPPER
Hi, thanks for that answer, looking far-wide with no luck on ucase as user
function, in a range, wondering if there is a way to select multiple ranges, got modification of your example to exclude formula's... (all can do is copy-paste, not real good at discerning meaning / if have correct items, please advise), have: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Cells.Count 1 Then Exit Sub If .HasFormula Then Exit Sub If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then 'If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) End If End With ErrHandler: Application.EnableEvents = True End Sub (this obviously not right as is), but want to add stuff: If Not Intersect(Me.Range("A1"), .Cells) Is Nothing Then If Not Intersect(Me.Range("F10"), .Cells) Is Nothing Then If Not Intersect(Me.Range("Z9"), .Cells) Is Nothing Then If Target.Row < toprowID Then Exit Sub €˜for onlly portions of entries If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then If Not Intersect(Me.Range("M:M"), .Cells) Is Nothing Then thanks in advance. ~~~~~~~~~~~~~ "Gord Dibben" wrote: You cannot autoformat but you could use event code. 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 Right-click on your sheet tab and "View Code". Copy/paste into that module. Edit the range to suit. Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Fri, 31 Oct 2008 05:46:14 -0700, Sal wrote: Is there a way to autoformat a range of cells to always display as upper case even if lower case if entered in the cell? Is there a shortcut to convert text from lower to upper case? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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) | |||
Change from mixed caps and upper lower to all upper lower case | Excel Worksheet Functions | |||
Convert lower case charecters to upper case | Excel Discussion (Misc queries) | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) |