UCase Code Solution Needed

[Phil H.]

A worksheet has 13 column ranges (L21:L37, P2137, AC21:AC37, etc
requiring upper case if the user inputs lower case. The following cod
does the job for L21:L37; however, upon a worksheet change, a compil
error occurs on the code for P2137 - Ambiguous name detected
Worksheet_Change. The first line of P21 code (Private Sub...) i
highlighted.

In the worksheet code sheet, the same code (with the range changed) ha
been entered once for each of the 13 ranges, trying to get around th
problem of not being able to make a single sub to the work for all 1
ranges. Could someone suggest a solution for getting the 13 colum
ranges to change lower case to upper case?

Private Sub Worksheet_Change(ByVal Target As Range)
'Forces text to UPPER case for the range L21:L37
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("L21:L37")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Forces text to UPPER case for the range P2137
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("P2137")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Su

 

[Peter Rooney]

Phil,

Why don't you use a series of If/Endif statements, one after the other, for
each range you want to work with - but within ONE worksheet_change macro? You
can only have one change macro for each worksheet.

Hope this helps

Pete

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
'Forces text to UPPER case for the range L21:L37
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("L21:L37,P2137")) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub

 

[Peter Rooney]

Ouch!

Consider my multiple IF/ENDIF solution well and truly smoked!

Cheers

Pete

 

[Martin Fishlock]

Phil try

Private Sub Worksheet_Change(ByVal Target As Range)
'Forces text to UPPER case for the ranges

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Dim v_ranges As Variant
Dim i As Integer
v_ranges = Array("L21:L37", "P2137", "AC21:AC37")

For i = LBound(v_ranges) To UBound(v_ranges)
If (Not Intersect(Target, Range(v_ranges(i))) Is Nothing) Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0
End If
Next i
End Sub

 

[Phil H.]

Thanks, JE - I figured there had to be a way to make this work on on
sub