2

To overcome the Protection Limitation of Autofiltered ranges (see my Answer to: VBA Excel - How to lock specific cells but allow filtering and sorting) I have a desire to respond to the user clicking an Autofilter Button (at which point I can unprotect the sheet and allow the filtering). I can't find any event that triggers when the user clicks an autofilter button so instead I have instructed my users to first select an Autofilter row's cell (an event I can recognize) before clicking the cell's Autofilter button.

This is a mostly-reasonable workround but it does require the users to work in a non-standard way. Is there any way to capture that an autofilter button (on a locked cell) has been clicked?

Many thanks

Malcolm Farrelle
  • 177
  • 14
  • you have a button which runs code... why can you not tell that code to indicate something globally to trigger? – Cyril Mar 28 '19 at 13:19
  • Thanks cyril that was a quick response. But it's not my button, rather it's the Excel Autofilter button. You know, the one you get if you select a row then click the "Filter" button in the "Data" menu. – Malcolm Farrelle Mar 28 '19 at 13:34
  • Why doesn't ticking EnableAutofilter when protecting doesnot work for you? – EvR Mar 28 '19 at 15:57
  • I know this is an old thread but [this](https://stackoverflow.com/questions/30982509/excel-vba-detect-ok-event-from-autofilter) could help. – kilroy Oct 30 '19 at 03:16

0 Answers0