I have a problem I can't figure out. I have a protected worksheet with some macros running on it, and I
have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use
this command on a protected sheet" error when applying an autofilter. Here is what is making this problem a little tricky:
- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings
on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.
- When I protect the
sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both
are on. Still, I keep getting the error.
The failing line of code is:
shData.Range("_filterDataBase").AutoFilter lngField, strArg
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.
My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is
used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are
read-only, and cannot be used to modify the existing filter.
I would be very grateful if somebody could either
clarify that using .AutoFilter on a protected worksheet is something that simply cannot be done, or point out what I am doing
wrong. Also, if I am wrong in any of my above assumptions of "can't do" nature, please correct me.