Macro Slowing Work Book

[rv79]

Hi Guys

I desperatley require help with the following, i have a work book that i use as a database for work requests that is kept on a server which other people can log job requests on, basically the vba code that i have created seems to slow the work book right down which is making it practically impossible to log jobs?

Here is the macro EventProc1 creates a time stamp, EventProc2 sorts column J

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False

EventProc1 Target
EventProc2 Target

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub EventProc1(ByVal Target As Range)
If Target.Column = 10 Then
Application.EnableEvents = False
Cells(Target.Row, 11).Value = Date + Time
Application.EnableEvents = True
End If
End Sub
Private Sub EventProc2(ByVal Target As Range)
Dim LR As Long
If Target.Column <> 10 Then Exit Sub
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A1:N" & LR).Sort Key1:=Range("J2"), Order1:=xlDescending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


Any help with this would be greatly appricated?

 

[GTO]

Hi there,

Several observations:

When posting code, use the [code=rich] ...your code pasted here [/code] tags and some sort of indentation. It makes the code a lot easier to read, for both any helper and you.

Option Explicit
    
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    EventProc1 Target
    EventProc2 Target
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
    
Private Sub EventProc1(ByVal Target As Range)
    If Target.Column = 10 Then
        Application.EnableEvents = False
        Cells(Target.Row, 11).Value = Date + Time
        Application.EnableEvents = True
    End If
End Sub
    
Private Sub EventProc2(ByVal Target As Range)
Dim LR As Long
    
    If Target.Column <> 10 Then Exit Sub
    
    LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    Range("A1:N" & LR).Sort Key1:=Range("J2"), _
                            Order1:=xlDescending, _
                            Header:=xlYes, _
                            OrderCustom:=1, _
                            MatchCase:=False, _
                            Orientation:=xlTopToBottom
End Sub

A minor point: You are disabling/re-enabling events twice when only once appears needed.

I suspect more at root of speed issue: Yare searching the the entire sheet. Search (at max) the column we have Change triggering on.