VBA Macro not working after move form 2010 to 2016

[kenderweasel]

Hi,

I've got a problem with a macro on one of my worksheets not working since I moved from Excel 2010 to 2016 this morning. The macro is designed to auto-sort data by column F:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Range("F1").Sort Key1:=Range("F2"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Thanks!

 

[MrKowz]

Is it giving you an error? I just tried the code in Excel 2016, and it works without error.

 

[kenderweasel]

I'm not getting an error - it's just not sorting as it should do.

 

[kenderweasel]

Ok - so I've just re-loaded it and it's now working...
Thanks for your help, and sorry to have wasted your time!

 

[MrKowz]

No time wasted!

What likely happened is your Events got disabled by another macro. If this happens again, try going into the VBA editor's Immediate Window and entering Application.EnableEvents = True.

 

[kenderweasel]

Hi,

Apologies - I thought this had resolved itself, but it's not working. What happens is that the column is auto-sorted on first opening the document, but changes to unsorted once the file has finished its calculations. I've tried inputing Application.EnableEvents=True into the Immediate Window, but it's still not working.

 

[MrKowz]

Is there a workbook_open() event in your VBA? If so, please post the entire code.

 

[Greg Truby]

Try dropping this into that same worksheet's code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.StatusBar = "You have selected cell" _
                          & IIf(Target.Count > 1, "s ", " ") _
                          & Target.Address(0, 0)
End Sub

Is the status bar updating as you select different cells on the worksheet?

Just delete (or comment out) the code and type application.statusbar = false in the immediate window to reset your statusbar once you've done the test.