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.

take off your on error for a start. if anything is going wrong to cause the problem you're hiding the issue. — Zerk
– Zerk, Commented Nov 27, 2017 at 15:15
Write application.EnableEvents=True in the immediate window. Press Enter. Then write Stop after Const adrs As String = "F4:F50" and check whether the event works. — Vityata
– Vityata, Commented Nov 27, 2017 at 15:16

Vityata · Accepted Answer · 2017-11-27 15:27:24Z

1

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.

edited Nov 27, 2017 at 15:27

answered Nov 27, 2017 at 15:19

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Daisy509th Over a year ago

Ok, so from doing this I see that it runs through the code but than then it skips the call line of code. Therefore, the sort module is never called. Also is there a way to see the value its getting for sh.name?

Vityata Over a year ago

@Daisy509th - msgbox (sh.name)

Daisy509th Over a year ago

So, Sh.name is returning the proper values. Therefore, my code " If Not Intersect(Target, Worksheets(Sh.Name).Range(adrs)) Is Nothing Then Call Sort(Sh.Name) End If" So i guessing there is something wrong with my line of code dealing with the logic or argument. Any Ideas? Also thanks for your time and help.

Vityata Over a year ago

@Daisy509th - put a stop sign and press F8 to debug. Probably you Target is not within the F4:F50 range. Or something similar.

Daisy509th Over a year ago

So,question, Can the problem be that "Sh.name" = Tuesday and not "Tuesday"? Meaning the code line looks like this " Worksheets(Tuesday).Range("F4:F50") instead of "Worksheets("Tuesday").Range("F4:F50")" because the .Range = "F4:F50". I am thinking that Tuesday needs to read "Tuesday" for syntax purposes

|

Daisy509th · Accepted Answer · 2017-11-27 19:06:28Z

0

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.

answered Nov 27, 2017 at 19:06

Daisy509th

294 bronze badges

Collectives™ on Stack Overflow

VBA EXCEL Sorting

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related