Macro throwing error when run from customized ribbon

Question

I have two identical macros to sort data. The only difference is which column is sorted. Both macros run correctly when run manually, but SortByDate throws an error when run from the custom button. SortByPPR works correctly. I can't see any differences in settings.

The error is: Cannot run the macro...The macro may not be available in this workbook or all macros may be disabled"

Sub SortByDate()
'
' SortByDate Macro
'

'
   Range("A6:O35").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
   ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("F6:F35") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A6:O35")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub SortByPPR()
'
' SortByPPR Macro
'
Range("A6:O35").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
   ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("b6:b35") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A6:O35")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'
End Sub

Should you not be using `Sub SortbyDate(control As IRibbonControl)`? Side note: it`s a good idea to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Mar 30 '23 at 17:33
If you answered the question , mark it as the answer so it does not remain as unanswered. — Davesexcel, Mar 30 '23 at 19:34

score 0 · Accepted Answer · answered Mar 30 '23 at 17:39

0

Original macro was written in an XLSX file, then subsequently changed to XLSM. The erroneous one was still referring to the xlsx file. Deleted the macro and initiated a new one in an XLSM and it works

answered Mar 30 '23 at 17:39

Robert Kendall

368
2
9
24

Macro throwing error when run from customized ribbon

1 Answers1