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


"Phil H." wrote:


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

In the worksheet code sheet, the same code (with the range changed) has
been entered once for each of the 13 ranges, trying to get around the
problem of not being able to make a single sub to the work for all 13
ranges. Could someone suggest a solution for getting the 13 column
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 P21:P37
If Target.Cells.Count 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("P21:P37")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub


--
Phil H.
------------------------------------------------------------------------
Phil H.'s Profile: http://www.hightechtalks.com/m229
View this thread: http://www.hightechtalks.com/t2301241