Code to invoke a Macro when the user leaves the active sheet

I have a macro to sort a named range that works well but I need to have it run every time when a user leaves the active sheet. That way if they change anything in the named range, when they leave that sheet, it will re-sort that range. The code below is what I have to sort the range.

If anyone can help with my question or can simplify my code below, I would be so grateful.
RR

    Sheets("Project Role").Select
    Application.Goto Reference:="R3C2"
    Range(Selection, Selection.End(xlDown)).Select
    Range("B3:B42").Select
    ActiveWorkbook.Worksheets("Project Role").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Project Role").Sort.SortFields.Add Key:=Range("B3" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Project Role").Sort
        .SetRange Range("B4:B42")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select

Your code is not complete; it is just a fragment. What Sub is it in? What module is it in?

when a user leaves the active sheet

When the user leaves any active sheet? Or is there one sheet in particular you are interested in?

If it's one sheet in particular use this code in the VBA module for that sheet:

Private Sub Worksheet_Deactivate()

   ' call the Sub that does the sort

End Sub

Also your code looks like it is from the macro recorder, so has some bloat. I would put it in the code module for sheet Project Role and rewrite it like this:

   With Me.Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("B3"), _
                      SortOn:=xlSortOnValues, _
                      Order:=xlAscending, _
                      DataOption:=xlSortTextAsNumbers
      .SetRange Range("B4:B42")
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
   
   Range("A1").Select

All the code you are showing here should be in the VBA module for the worksheet Project Role. The new sub I provided would be in that module and revised to be:

Private Sub Worksheet_Deactivate()

   Macro3

End Sub

Your sort code is hard-coded to sort rows 4-42 so if someone inserts a new row it won't work correctly anymore. If you attach your file here (see yellow banner at the top of the page) I can revise your code to use the name Proj_Role. I can't test code without the file and data.

See how the data on Sector is a Table? That is what I would do with Project Role, instead of making it a named range. But I didn't change it.

I did change the code to refer to Proj_Role. I moved the Macro3 into the sheet module and renamed it. Sub Macro3 still exists but now calls the new sub, which will allow you to invoke it from the user interface. I removed Module2, it was redundant. I added the Worksheet_Deactivate Sub.

I didn't touch Module1, not sure what you are trying to do there.

On Project Roles I changed (Bottom) to zz(Bottom) so it will always sort to the bottom of the list.

I tested everything and it works.

Glad to help! If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.