Sorting protected table by macro, run time 1004 error

[Limone]

I have a button that's supposed to alphabetically sort a table by the first column so the end user won't have problems finding employees even when the worksheet is protected.

Unfortunately the code that I have and that worked fine when I first tried it won't stop returning a run time 1004 error. The ".Apply" line gets highlighted and it happens whether the sheet is protected or not.

Any idea how to fix it?

Sub PulsanteRiordinaA_Click()
Dim lo As Excel.ListObject
Application.ScreenUpdating = False
Activesheet.Unprotect
Set lo = ActiveWorkbook.Worksheets("Dati Anagrafici").ListObjects("TabellaAnagrafe")
With lo
.Sort.SortFields.Clear
    .Sort.SortFields.Add _
        Key:=Range("TabellaAnagrafe"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
Application.ScreenUpdating = True
Activesheet.Protect
End Sub

 

[DanteAmor]

You must unprotect the sheet to work, because maybe it's not the active sheet.

Sub PulsanteRiordinaA_Click()
    Dim lo As Excel.ListObject
    Application.ScreenUpdating = False


    [COLOR=#0000ff]ActiveWorkbook.Worksheets("Dati Anagrafici").Unprotect[/COLOR]


    Set lo = ActiveWorkbook.Worksheets("Dati Anagrafici").ListObjects("TabellaAnagrafe")
    With lo
        .Sort.SortFields.Clear
        .Sort.SortFields.Add _
            Key:=Range("TabellaAnagrafe"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    Application.ScreenUpdating = True


    [COLOR=#0000ff]ActiveWorkbook.Worksheets("Dati Anagrafici").Protect[/COLOR]


End Sub

 

[Limone]

I tried that route just to be sure but it makes no difference unfortunately.

 

[DanteAmor]

You have to put the column number to Sort, look at the updated code:

Sub PulsanteRiordinaA_Click()
    Application.ScreenUpdating = False
    ActiveWorkbook.Worksheets("Dati Anagrafici").Unprotect
    With ActiveWorkbook.Worksheets("Dati Anagrafici").ListObjects("TabellaAnagrafe").Sort
        .SortFields.Clear
        .SortFields.Add Key:=[COLOR=#0000ff]Range("TabellaAnagrafe").Columns(1)[/COLOR], _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Dati Anagrafici").Protect
    Application.ScreenUpdating = True
End Sub

 

[Limone]

It works! Thanks a lot Dante

 

[DanteAmor]

I'm glad to help you