VBA sort code no longer working

J

jaclwb

New member
Joined
Aug 24, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2016
I have a sheet with four columns (Company, Activity, People, Date) and I'm trying to have the document automatically sort by date whenever a new activity & date is entered.

I'd set up a VBA sort code to automatically sort information using this code:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Range("D1").Sort Key1:=Range("D45"), _
      Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub
It worked perfectly when I first set it up and tested it. I saved the document and when I opened it this morning it's no longer automatically sorting when I enter new dates or change existing dates. I don't know what's happening. Please help!
 
Last edited by a moderator:
Is cell D2 blank?
Best attach a workbook with it not working.
 
p45cal said:
Is cell D2 blank?
Best attach a workbook with it not working.
Click to expand...

No, D2 has a date entered. I've attached the workbook where it's not working.
 

Attachments

  • BK Marketing Activity Tracker.xlsm
    15.4 KB · Views: 10
Works here.
1. Is it on SharePoint?
2. If you type:
?application.EnableEvents
in the Immediate pane of the VBE then press Enter what does it return with?
 
p45cal said:
Works here.
1. Is it on SharePoint?
2. If you type:
?application.EnableEvents
in the Immediate pane of the VBE then press Enter what does it return with?
Click to expand...

It's not on SharePoint.

I tried typing in the line as you suggested, but nothing changed. I went back into the worksheet and changed a date and it didn't re-sort. I tried adding a date and it didn't re-sort. It had worked for me yesterday when I first created the document so I'm stumped as to why it's not sorting for me now.
 
jaclwb said:
I tried typing in the line as you suggested, but nothing changed.
Click to expand...
It won't change anything, it should return True or False in the Immediate Pane as soon as you press Enter. I want to know if it says True or False. Note: it starts with a question mark.
 
p45cal said:
It won't change anything, it should return True or False in the Immediate Pane as soon as you press Enter. I want to know if it says True or False. Note: it starts with a question mark.
Click to expand...

It doesn't say True or False. When I type it in and press enter it turns into: Print Application.EnableEvents
 
jaclwb said:
It doesn't say True or False. When I type it in and press enter it turns into: Print Application.EnableEvents
Click to expand...
Then you're not typing it in the Immediate pane (more probably where the rest of the code is), bring the Immediate pane up by Ctrl +g on the keyboard.
 
Last edited:
p45cal said:
Then you're not typing it in the Immediate pane (more probably where the rest of the code is), bring the Immediate pane up by Ctrl +g on the keyboard.
Click to expand...

I get a pop-up saying the macros in this project are disabled.
 
jaclwb said:
I get a pop-up saying the macros in this project are disabled.
Click to expand...

Then that's why!
Close the workbook, re-open it while enabling macros.
 
p45cal said:
Then that's why!
Close the workbook, re-open it while enabling macros.
Click to expand...

Thanks - I figured out how to re-set my macros setting so now I can enable it each time I open the document. Thanks for all your help!
 
You must log in or register to reply here.
Back
Top