Sorting Excel tables (ListObjects) is not allowed on protected sheets. You'll see the following error message:
I spent weeks looking for a solution with no success. Everything out there is outdated with Excel 2007 code samples. There are no tutorials or guides on how circumvent this limitation.
Here's how I was able to finally overcome..
There is no trappable event when sorting from the Excel's filter drop-down menu of a table. You can, however, trap the events when an ascending, descending or sort dialog commands are invoked from the Ribbon's Home and Data tabs.
Using Excel 2016 Interop (document-level customization), Visual Studio 2015 and C#:
Next, add the events' callback functions.
SortNoAlerts
unprotects the sheet for ascending / descending button clicks. But if the user chooses 'Custom Sort' (Home tab) - or - 'Sort' (Data tab), a dialog will appear, sure it will unprotect the sheet and protect it right back if OK is pressed, but if the user Cancels,ThisWorkbook_SheetCalculate
will never trigger leaving the sheet unprotected. So we add theSortDialogNoAlerts
which unprotects the sheet but also starts a timer that uses p/InvokeFindWindow
to look for the Sort dialog window. When the Window is no longer found, it protects it if not already protected.That will allow sorting tables on protected sheets. Don't be confused, the
AllowSort
option ofworksheet.Protect()
it's only for the cells of the sheet that are not part of a table (ListObject).