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?