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?

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?

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?

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?

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?

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?

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?