VBA EXCEL Sorting

Question

Macro Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) paste in ThisWorkbook module:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Const adrs As String = "F4:F50"

    Select Case Sh.Name
        Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"
            If Not Intersect(Target, Worksheets(Sh.Name).Range(adrs)) Is Nothing Then
               Call Sort(Sh.Name)
            End If
        Case Else
    End Select
End Sub

Macro Sub Sort(shtNme As String) paste in Module1 module

Option Explicit

Sub Sort(shtNme As String)
    On Error GoTo the_end
    Application.EnableEvents = False

    With ActiveWorkbook.Worksheets(shtNme)
        .Range("N3:O50").ClearContents
        .Range("E3:F50").Copy Destination:=Range("N3")

        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("O4:O50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("N3:O50")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        .Range("A4").Select
    End With

    the_end: Application.EnableEvents = True
End Sub

I can not get this code to run in excel 2016. I also can't step through this code either. If I try to step through code it just brings up an empty Marco Box. The code will compile though.

I am trying to sort drivers by name based on time worked. Other code that I have used in the past seems to be crossing talking between sheets. I have also noticed that the workbook itself is slow loading and unloading.

Any Ideas?

I post my workbook but its to big.

Answer 1

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Const adrs As String = "A4:B50"

Select Case Sh.Name
    Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"
        If Not Intersect(Target, Worksheets(Sh.Name).Range(adrs)) Is Nothing Then

          Call Sort(Sh.Name)

        End If
     Case Else
  End Select
End Sub

So this fixes the problem and by the other user teaching me how to debug this type of code I found the problem.

Answer 2

Private Sub Workbook_SheetChange is an event. You cannot step on it, it gets activated when something is changed in the workbook.

Try writing Application.EnableEvents=True in the Immediate Window and press Enter.

Change your code like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As 
Range)
Const adrs As String = "F4:F50"
Stop
...

Now change something on your sheet, and see how the event fires. It will stop on the Stop line, thus you can debug from there.