My Code takes too long to execute

**coreytroy** · 07-29-2008, 11:39 PM

Private Sub Worksheet_Change(ByVal Target As Range)
'Place the word SITE in the Target Cell if it is Blank
If Not Application.Intersect(Target, Range("B5,B9,B13,E5,E9,E13,H5,H9,H13,K5,K9,K13,N5,N9,N13,Q5,Q9,Q13,T5,T9,T13")) Is Nothing Then
If Target.Value = "" Then Target.Value = "Site"
End If
If Range("K2").Value <> "" Then Range("K2").Value = UCase(Range("K2").Value)
End Sub

Is there a faster execution scenario with this?

**shg** · 07-29-2008, 11:52 PM

I'd change the order of evaluation as a matter of practice:

Private Sub Worksheet_Change(ByVal Target As Range)
    Static r As Range
    
    If r Is Nothing Then Set r = Range("B5,B9,B13,E5,E9,E13,H5,H9,H13,K5,K9,K13,N5,N9,N13,Q5,Q9,Q13,T5,T9,T13")

    Application.EnableEvents = False
    With Target
        If IsEmpty(.Value) Then
            If Not Application.Intersect(.Cells, r) Is Nothing Then .Value = "Site"
        End If
    End With
    
    With Range("K2")
        If Not IsEmpty(.Value) Then .Value = UCase(.Text)
    End With

    Application.EnableEvents = True
End Sub

.... but I can't imagine you could tell the difference in the speed of the user interface.

**Paul** · 07-29-2008, 11:53 PM

Hi Coreytroy,

How long is it taking this to execute on your PC? It's nearly instantaneous on my 3-year-old laptop running XP Pro/Office 2K3.

**coreytroy** · 07-30-2008, 12:07 AM

It seems to Stall Excel for about 2-3 seconds, and prevents anything from being done.

I removed the code and i get no stalls.
But i need it in there.

What other problem could it be if not the code directly?