Greetings,

I am running Excel 2k on a W2k box with 1gig of ram.

I am attempting to add a name to a Validation list. The list appears
in 5 different cells. I tried to piggy back this code:

If Not Intersect(Target, Me.Range("B11")) Is Nothing Then
With Target
NewCleanerAddForm.Show
End With
End If

into Private Sub Worksheet_Change(ByVal Target As Range) which is
being used to change the entries to about 12 different cells to all
caps. When I added this code i can get one or the other to work/

Here is the complete sub:

Private Sub Worksheet_Change(ByVal Target As Range)
'Force Caps on certain ranges
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("AK15:AM22,B32,X2,Q7,Q10")) Is _
Nothing Then
With Target
.Value = UCase(.Value)
End With
End If

If Not Intersect(Target, Me.Range("B11")) Is Nothing Then
With Target
NewCleanerAddForm.Show
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Anyone have any ideas as to how to do this?

TIA

-Minitman

When I added this code i can get one or the other to work

Since the range for uppercasing is different than the range for showing the
userform (B11) both would never fire with the same entry.



--
Jim Rech
Excel MVP

Try dropping the 'With Target' to show a userform.

Mike F
"Minitman" wrote in message
...
Greetings,

I am running Excel 2k on a W2k box with 1gig of ram.

I am attempting to add a name to a Validation list. The list appears
in 5 different cells. I tried to piggy back this code:

If Not Intersect(Target, Me.Range("B11")) Is Nothing Then
With Target
NewCleanerAddForm.Show
End With
End If

into Private Sub Worksheet_Change(ByVal Target As Range) which is
being used to change the entries to about 12 different cells to all
caps. When I added this code i can get one or the other to work/

Here is the complete sub:

Private Sub Worksheet_Change(ByVal Target As Range)
'Force Caps on certain ranges
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("AK15:AM22,B32,X2,Q7,Q10")) Is _
Nothing Then
With Target
.Value = UCase(.Value)
End With
End If

If Not Intersect(Target, Me.Range("B11")) Is Nothing Then
With Target
NewCleanerAddForm.Show
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Anyone have any ideas as to how to do this?

TIA

-Minitman