convert lower to upper case automatically without using UPPER

S

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?
 
J

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.
 
B

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.
 
M

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
 
G

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
 
M

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.


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top