Solved Excel Error Repairs Sorting When Opening

August 28, 2019 at 10:13:43
Specs: Windows
I have a file that keeps giving me and error when I launch it. Upon launch it says,

"We found a problem with some content in 'Sheet.xlsm'. Do you want us to recover as much as we can? If you trust the source of this workbook, click Yes."

Upon clicking yes, I get a repairs list that says this.

"Excel was able to open the file by repairing or removing the unreadable content"
"Removed Records: Sorting from /xl/worksheets/sheet2.xml part"

I do have a sort VBA in sheet 2. I will copy the entire sheet VBA below (There is also a worksheet change applied).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 8
xTimeColumn = 9
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
    End If
End If

End Sub

Private Sub Worksheet_Deactivate()
   
       ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range( _
        "A2:A" & Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:L" & Rows.Count)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    
End Sub

Any ideas on why this is occurring?

message edited by Steven4321


See More: Excel Error Repairs Sorting When Opening

Reply ↓  Report •

✔ Best Answer
September 10, 2019 at 16:16:28
re: "Have you come to any further conclusions or a resolution?"

I thought that I already gave you the resolution. Did you not test the code after I offered this?

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear

Add that as the last instruction before End Sub and I believe that the problem will be solved.

As far as why that instruction is needed, no, I have not found a reason.

message edited by DerbyDad03



#1
August 28, 2019 at 12:11:15
As far as I can tell from my testing, the error is being caused by the Deactivate-Sort code itself. Since I can force the error without the Worksheet Change code even being in the workbook, we can ignore the Change code.

I also found that the error only occurred after the Deactivate code was triggered at least once. If you never Deactivate Sheet 2, the sort code never runs and the error doesn't occur when you open the workbook.

Once I had it narrowed down to that code and the fact that it had to run at least once, I came up with a solution that seems to work. At this time I can't tell you why the error occurs or why the following instruction eliminates the error:

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear

Add that as the last instruction before End Sub and I believe that the problem will be solved. Let me know if that works for you. If it does, I'll do a little more research to see if I can find out why,

Obviously, the Sort is leaving remnants hanging around even after the workbook is saved and Excel doesn't like them when it opens the file. What I don't know (yet) is why this happens when the sort is triggered by the Deactivate event. Lots of macros run sort routines that don't cause this error so it's a bit of a puzzler right now.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
August 29, 2019 at 07:50:57
re: "As far as I can tell from my testing, the error is being caused by the Deactivate-Sort code itself"

I still don't know why it's happening, but I think I narrowed it down a bit. I should have tested this yesterday, but I just determined that the Deactivate event has nothing to do with the error. Based on my testing, the Sort code itself is the issue. I ran it as a General macro, not an Event macro, and it caused the same error.

I also noticed that the SortFields.Clear instruction is automatically included if you record a macro while doing a Sort. Obviously it is needed, but I'm not sure why.


Still digging.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
September 10, 2019 at 13:43:56
I apologize for the delay in response. I've been out of town for the past 2 weeks and have not been on top of this. Have you come to any further conclusions or a resolution?

Reply ↓  Report •

Related Solutions

#4
September 10, 2019 at 16:16:28
✔ Best Answer
re: "Have you come to any further conclusions or a resolution?"

I thought that I already gave you the resolution. Did you not test the code after I offered this?

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear

Add that as the last instruction before End Sub and I believe that the problem will be solved.

As far as why that instruction is needed, no, I have not found a reason.

message edited by DerbyDad03


Reply ↓  Report •

#5
September 18, 2019 at 09:55:26
I actually had the code working prior to leaving out of town. I meant to ask if you had determined a reason why the instruction was needed. But I suppose the reason is not important since the code worked and I am no longer getting the issue.

Thank you again.


Reply ↓  Report •

Ask Question