I'm looking into a problem for someone, who works in a mixed OS environment. She has created an Excel spreadsheet in Office 2007 to act as a directory, with AutoFilter turned on for names, email addresses, departments etc. To make sure no one accidentally edits email addresses (for example), she has protected the work sheet.

Accessing this worksheet on a PC running Excel 2007, everything runs as you'd expect. You can filter the sheet by any of the auto-filtered columns, and because the sheet is protected, the data integrity is guaranteed.

However, if you access the sheet on a Mac running Excel 2008, you can't filter the columns. What's strange here is that the AutoFilter dropdown arrows do appear in each of the column headers as you would expect. It's just that nothing happens if you click on them.

If you select one of the column header cells (say, 'First Name') and check the menu: Data->Filter, you can see that AutoFilter is ticked.

As another datapoint, you also seem to be able to apply an Advanced filter to these rows on the protected sheets.

Does anyone know why this might be? It seems to be a compatibility issue between Excel 2007/2008 (I know the codebase isn't the same), but I can't find any references to it in documentation or forums anywhere, and it would be good to know if there's a way around this.


---- Updated

I've done some more investigation, and this appears to be a known feature of Office 2004 for the Mac. It looks like it might be an interpretation issue: protecting the sheet should disable you from making any changes to it. According to this: http://www.officeformac.com/ProductForums/Excel/3936 this behavior was replicated with VBA scripts. Office 2008 has no support for VBA scripts, so I'm currently investigating if I can work around or investigate this with AppleScript.

Anyone with an idea of what else I might do to get around this?


I know this is an old question, but perhaps someone else might try this. Use a macro which turns off protection when a header selection is made allowing autofilter to occur, then turning protection back on when filtering completes. Selection of any other cell in the sheet can be set to not turn protection off.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.