0

I have to make a sort of a table before leaving the page which I have in a function (in order that a filtered drop-down list has the correct values, but that is irrelevant for the case).

I got the function run when leaving the page, that works ok

Private Sub Worksheet_Deactivate()    
    Sort_CategoryProject_Group
End Sub

But the problem is that when executing the function, the sort changes to the leaving page, and then the 'WorkSheet_Deactivate' sub executes again entering in an infinite loop.

This is the procedure

Sub Sort_CategoryProject_Group()
'9A14
'Sort after Project Name
    Dim lastRow As Byte

    lastRow = Sheets(pubWsCategory).Cells(Rows.Count, 12).End(xlUp).Row

    Sheets(pubWsCategory).Range("L4:U" & lastRow).Select
    ActiveWorkbook.Worksheets(pubWsCategory).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(pubWsCategory).Sort.SortFields.Add Key:=Range("L4:L" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(pubWsCategory).Sort.SortFields.Add Key:=Range("M4:M" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets(pubWsCategory).Sort
        .SetRange Range("L4:U" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Application.CutCopyMode = False
    Range("B4").Select

End Sub
  • The pubWsCategory is the leaving page variable name.

I run out of ideas and I do not know what else to do. Can anyone help me?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
David
  • 31
  • 1
  • 3
  • 1
    Stop using `.Select` will solve your issue: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) • And you must declare `Dim lastRow As Long` • Instead of `ActiveWorkbook` you probably meant to use `ThisWorkbook`. • You also might want to have a look at the [Application.EnableEvents property](https://learn.microsoft.com/en-us/office/vba/api/excel.application.enableevents) – Pᴇʜ Jan 24 '19 at 07:53
  • Peh, Tank you very much for your help, just removing the .Select line solved the problem. Dim as Long is not needed, as there are less than 255 rows. Also ActiveWorkbook works well. – David Jan 24 '19 at 13:58

0 Answers0