Allow users of protected sheet to use macro involving custom filte

[Roady]

Hi:

I have created a macro that works for me when unprotected. However, when I
re-protect it before sending to mass users, it will not allow the custom
filter part of the macro to occur and returns an error. I have tried checking
the box in 'Protection'- 'Protect Sheet' that allows auto filter but there
does not seem to be an option for custom filter. Is there a way around this
without un-protecting the sheet? thank you!

 

[Pete_UK]

What you would normally do is to include two lines in your macro - one
immediately before what you want the user to do will unprotect the
sheet (by having the password embedded within the macro) and one
immediately after to protect the worksheet again.

You should note that users who have Excel 2000 and earlier will not be
able to use autofilter when a sheet is protected.

Hope this helps.

Pete

 

[Roady]

Can I do this by including the un-protect/re-protect step in the macro as I
am recording it? sort of book-end the macro with the password protection at
both ends?

 

[Gord Dibben]

Not while recording but you can edit afterward.

Sub Macro1()

ActiveSheet.Unprotect Password:="justme"
do your recorded stuff
ActiveSheet.Protect Password:="justme"

End Sub


Gord Dibben MS Excel MVP