Ucase / macros

[MrDave]

hi, trying to get ucase to work, would settle for the 1 liner that works..
just to get the command to work, but don't see the answer anywhere that
combines a couple things (if have wording right):
- event macro, mixed in with other items, or
- reference to specific cells, columns
- similar, many different cells or multiple columns, whether:
- ucase, or lcase, thanks in advance

closest script have is for separate sheet entry guesse, but not working.
have: (can I get rid of private sub & option explicit have....)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr As String

With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then
Exit Sub
On Error GoTo ErrHandler:

'If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then

myStr = UCase(myStr)

Application.EnableEvents = False
.Value = myStr
End If
End With

ErrHandler:
Application.EnableEvents = True
End Sub

 

[MrDave]

found this that seems to work, modified so formula's not affected.

question: how to enter multiple ranges, columns & separate cell targets.

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("a1:a20"), 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



(reference: Subject: Re: convert lower to upper case automatically without
using UPPER, gord.. 10/31/2008 9:19 AM PST)