A worksheet has 13 column ranges (L21:L37, P21:P37, 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 P21:P37 - 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 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 Su

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

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

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,P21:P37")) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub



In article ,
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

Ouch!

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

Cheers

Pete



"JE McGimpsey" wrote:

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,P21:P37")) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub



In article ,
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 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", "P21:P37", "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." 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

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

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