Capturing the Excel AutoFilter Sorting Event

Hi,

I have an excel in which the AutoFilter option has been checked using Data --> Filter à AutoFilter. The sheet is protected using Tools àProtection àProtect Sheet as the contents of the cells have to be kept as uneditable. The AutoFilter Dropdown shows the filter options as (All), (Top 10 …..)(Custom……), other distinct values in the column. It also shows two options for Sorting as Sort Ascending, Sort Descending.

Using a filter among the filter options such as (All), (Top 10...) only hides the unnecessary cells and is allowed to do so when the sheet is protected. However when using a sort option Sort Ascending/Sort Descending, it causes a change in the value of the cells and is not allowed doing so when the sheet is protected giving a message “The cell or chart you are trying to change is protected and therefore read only”.

How do we make the sort option Sort Ascending/Sort Descending work when the sheet is protected? Is it possible to capture the Excel Autofilter selection change event and read the selection value as Sort Ascending/Sort Descending. Then we can write code for this event to unprotect the sheet, manually sort it and then protect it.

Is it possible to remove the two options Sort Ascending/Sort Descending in the AutoFilter dropdown? Then we can add our own custom events and handle the sorting through code.

Changing the text in the drop-down is pretty extreme.

On my Excel, AutoFilter is disabled on a protected sheet. Advanced Filter works, but ShowAll is also disabled. At least on my Excel 2004, OS 10.5 .

This code, put in the code module for the Sheet in question, will automaticaly sort column A every time Advanced Filter is engaged.
This requires that some cell on the hold a formula (like =COUNTA(A:A)) that will cause the Calculate event to fire when the filter is engaged.

It works on a protected sheet, but Showing All after the filter will require unprotecting the sheet.

Please Login or Register  to view this content.