Conflicting Code?

[Frank Kabel]

Hi
could you post the complete event procedure. In your
posted part for example the application events are not
enabled after entering/changing cell values

-----Original Message-----
I have this code in the Worksheet_change sub to change

the text in the range to uppercase. It works great on its
own. When I add this other section of code, it causes my
uppercase code to work a little bit and then not at all.
I don't understand if the code is conflicting or what.
Any ideas would be great. Thanks. Matt

UPPER CASE CODE:
On Error GoTo Error_handler
If Not Intersect(Range

("c13:c15,E13:e15,g13:g15,i13:i15,k13:k15,m13:m15,o1315,H
4:I4,C28:C30,E28:E30,G28:G30,I28:I30,K28:K30,M28:M30,O28:O3
0"), Target) _

Is Nothing Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
End If
End With
End If

OTHER CODE:
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C13:C15")) Is Nothing Then
Exit Sub
End If
With Target
If .Value = "AL" And Not IsEmpty(Range ("D10")) Then
MsgBox ("Please enter ADDHR = number of

hours worked and reason on the overtime explanation at the
bottom.")

ElseIf .Value = "HDAY" And Not IsEmpty(Range ("D10")) Then
MsgBox ("Please enter HOLWK = number of

hours worked and reason on the overtime explanation at the
bottom.")

 

[keepITcool]

Hi Matt..

This would work for me:

Private Sub Worksheet_Change(ByVal Target As Range)

'Esc when user is pasting or clearing a large range..
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, _
Union([H4:I4], _
[C13:C15,E13:E15,G13:G15,I13:I15,K13:K15,M13:M15,O13:O15], _
[C28:C30,E28:E30,G28:G30,I28:I30,K28:K30,M28:M30,O28:O30])) _
Is Nothing Then

With Target
'added a check for numeric entry..
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
..Value = UCase(.Value)
'reenable events RIGHT after I'm done
Application.EnableEvents = True
End If
End With
End If

If Intersect(Target, [C13:C15]) Is Nothing Then
Exit Sub
End If
With Target
If .Value = "AL" And Not IsEmpty(Range("D10")) Then
MsgBox ("Please enter ADDHR = number of hours worked and reason on the
overtime explanation at the bottom.")
ElseIf .Value = "HDAY" And Not IsEmpty(Range("D10")) Then
MsgBox ("Please enter HOLWK = number of hours worked and reason on the
overtime explanation at the bottom.")
End If
End With
End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Matt wrote :