Protected sheets and Drop Downs

[kkurtz]

I have a spreadsheet with drop downs on it. I have the drop downs
assigned to a macro with VBA code that unprotects the sheet when it
runs and then resets the protection at the end of the code. The
problem is that, apparently, the activity triggered by the drop downs
happens before the code is processed. Until the code is run the sheet
remains protected which means I get the standard excel message about
the sheet being protected and can't change the value in that cell.
Bottom line is that I need to know how to get the macro to unprotect
the sheet first before the drop down attempts to change any cell
values. I don't want to present an unprotected sheet to the users and
yet I want them to have use of the drop downs. Thanks, Ken

 

[Jim Rech]

In Excel 97 and later you can use an autofilter on a protected sheet if you
(1) enable it, and (2) protect the sheet with Userinterfaceonly set to True:

Sheet1.EnableAutoFilter = True
Sheet1.Protect userinterfaceonly:=True

The later setting is not retained with the workbook when saved so it must be
reset when the workbook is re-opened.

In Excel 2002 you can enable autofilter through the user interface (Tools,
Protection, Protect Sheet).