convert lower to upper case automatically without using UPPER

[Sal]

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?

 

[Gary''s Student]

Touch the CAPSLOCK key prior to typing.

 

[Joel]

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.

 

[BT]

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.

 

[Mike H]

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

 

[Gord Dibben]

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

 

[MrDave]

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.


~~~~~~~~~~~~~